Another SQL problem

Jak

Still having trouble getting to grips with all the commands (and the exactness) of SQL.

Anyways,

Problem 1:

If i have a column of numbers 1 to 100 say, and i want to query sets of various non sequential numbers;

e.g: 1,2,8,14,50,69

or: 12,99

How do I do it? Like when asking for date parameter I want it to ask me to enter all the numbers to be queried as well.


The Second Problem:

Unfortunately I don't reckon this one is even possible, but if you even think it might let me know (at least then it's worth still looking for an answer).


Basically I have a number value column which i want to convert to a time value which can then be added to a time.


In other words I have a column of say 90, 120, 180. Which is a number value of the minutes.

I want to convert them to time (01:30 = 90 ...) and have them added to the time field. Any ideas?


Cheers for any help,

JAK.

koloth

for the second problem, firstly divide the mins number by 60 and round down. So 90 divided by 60 is 1.5, round to 1 that gives you the hour. If you can't round to 1 you could probably parse it to take what's left of the period.

Then use MOD 60 (ironically I've never looked at SQL but I'm sure there's an equivalant) on the minutes. So, same example, 90 MOD 60 gives you 30, this is the minutes remainder.

After that you get 1.30

Same goes for 120, 155, onwards. Is this what you're looking for? (I'm no mathematician). I did the same thing in an Excel spreadsheet, so if you want that I'll send it on.

Jak

Unfortunately the Databases Im working on are too big for Excel. But Thanks.

What I am after is the SQL Language (as it has to be done in Access)which would convert number values to time values.

Once i can sort this then Im gonna put together the macro to do all the various bits of analysis on command.


JAK.

kayos

Ok for your Second problem if the field your adding is always in minutes you could do the following
DATEADD(minute,<ValueToAdd>,<ValuetoAddto> )

I'll post the solution to the first prob in a while but in the mean time try messing around with CharIndex and passing in all the numbers in a comma seperated format.

Kayos

koloth

no no, I didn't mean do it in Excel. I meant I have examples in an Excel spreadsheet. Had thought you wanted to do this in SQL Server, but you can still use the MOD function and simple parse functions such as LEFT as CSTR in actual Access SQL.

kayos

Solution to Problem one:
DECLARE @vDocCodes VARCHAR(255),
@iToken INT,
@iCode INT
SET @vDocCodes = '1,2,3,'
WHILE CHARINDEX (',', @vDocCodes) >= 1
BEGIN
SELECT @iToken = CHARINDEX(',',@vDocCodes)
SELECT @iCode = CONVERT(INT,SUBSTRING(@vDocCodes,1,@iToken - 1))
/*
** Do what you want with the number
*/
SELECT @vDocCodes = SUBSTRING(@vDocCodes, @iToken + 1, 255)
END

[This message has been edited by kayos (edited 26-06-2000).]

Jak

Right.

First of all - Cheers for all this help lads.

Koloth, I'd be interested in taking a look at that example so - Mail it to ja2k@ireland.com whenever you can.

Kayos-

For the second problem. The column is a number data type. What I want is to put a command in that will first convert the number 90 into minutes (or whatever format) that can then be added (using date add) to the original time - creating a new field which lists the converted times.

e.g 12.30 + 01.30 = 14:00


As for the second part you posted. Je5us - I thought that would be the less complicated part of the problem.

I'll be perfectly honest and say it all looks rather complex. So rather than try to understand it, I just copied the expression into the SQL view and it said

Characters found after SQL statement

I'm doing something wrong I'm sure.

Problem is I only recently had to start using MSaccess for my research - so SQL is about 2 weeks old to me. Turns out it's a lot more difficult and precise than I thought.


Anyhows, I'll keep trying a few things in the meantime.

Cheers again,

JAK.


ps- This is what my SQL view looks like atm. This is basically what I'm using to break up the date and time. I want to add is the Machine number query (prob 1) and have an extra field in the data (prob 2) giving the modified time.


SELECT TBLTRANSACTION.HORO, TBLTRANSACTION.DUREE_PAYEE, TBLTRANSACTION.TRDATE, CDate(DatePart("d",[trdate]) & "/" & DatePart("m",[trdate]) & "/" & DatePart("yyyy",[trdate])) AS [The date], DatePart("h",[trdate]) AS [The time]
FROM TBLTRANSACTION
WHERE (((CDate(DatePart("d",[trdate]) & "/" & DatePart("m",[trdate]) & "/" & DatePart("yyyy",[trdate]))) Between CDate([enter start date]) And CDate([enter end date])) AND ((DatePart("h",[trdate]))>=[enter start time] And (DatePart("h",[trdate]))<[enter end time]));


[This message has been edited by Jak (edited 26-06-2000).]

kayos

Well all that CharIndex stuff works fine in SQL7 and SQL6.5 if it didn't I'd be *(&^%$!.
But what works in SQL Server doen't always work in Access so sorry about that.

Kayos

Jak

Ok...

My last post to do with this i swear ... but to use the dateadd command ... how?

The column i want to add is called D_P .. the column it's being added to is TRD ... and I want it the result to create a new colum called CO.

So would this be DateAdd(Minute,<D_P><TRD> ) or what exactly?

Or is this an SQL 6.5/7 command which won't work in MSAccess?

Cheers,

JAK.

kayos

DATEADD(minute,VALUETOADD,VALUETOADDTO)
e.g. SELECT DATEADD(MINUTE,10,'01/01/2000 13:00')
would give
'01/01/2000 13:10'
DATEADD is a T-SQL command I just took a look at Access Help and its there as well but its looks like its a VBA thing only .

Kayos