multiple variables from select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

multiple variables from select

Author

Topic

besomuk
Starting Member

3 Posts

Posted - 2009-11-06 : 06:11:37

Ok, i;m trying to do this for some time...

I have table which looks like this:

id | date | time
----------------------
1 | 20091109 | 081000
2 | 20091109 | 081500
1 | 20091109 | 161600
2 | 20091109 | 161200
----------------------
etc...

This table shows date and time when ID performed action. Both are nvarchar.
Now...i must take date and time in separate variables to do some calculations, like time difference between two events on one date, to see if there are 2 or more same dates in table, set time to 08 hours if time is 074500 etc...

For beginning, how can i take bunch of times from table, put it in variable, do something with them and display them? I've been trying with sp_executesql but no success.

BTw, do you think it's better to do this from outside program, and not from SQL server itself?

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 06:33:12

its better to save date time as datetime not as nvarchar
is there any special reason you save it as nvarchar?

also to do calculation on all the variables in the table you don't take them out

if you want to show variables with your table structure do something like

SELECT top1 date, time FROM table

or
DECLARE @date char(8)
DECLARE @time char(8)

SELECT top 1 @date = date, @time = time
FROM table

and here we have @date and @time as variables

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

besomuk
Starting Member

3 Posts

Posted - 2009-11-06 : 06:40:38

it's not my table, i only need to get some data from it.
i dont know why it's nvarchar....

but, what do i do with one record i now have? :)
i need one value for each row...

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 07:06:13

what do plan to do with these values later?
if you just need to update it then just update the table if its something very complex and you really need to go through every line and do something based on its variables then use cursor

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

besomuk
Starting Member

3 Posts

Posted - 2009-11-06 : 07:11:44

I need to find id, corresponding date and time.
Then i need to find second time record on that date and calculate difference.

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 08:12:48

oh the horrors
can you just kill the person responsible for the table and reorganize it? :) that is the best solution you have for that kind of task
so you need an id and time difference between 2 entries, I'd try something like:

CREATE TABLE #data(id int, d datetime, date varchar(8))
/*converting varchar to datetime*/
INSERT #data(id, d, date)
SELECT id, CAST(date + ' ' + LEFT(time,2)+':'+SUBSTRING(time,3,2) AS datetime), date

SELECT id, DATEDIFF(minute,MIN(d), MAX(d)), date
FROM #data
GROUP by id, date

you will have a list of differences for each date based on id

This is the best idea i have

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources