query help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

query help

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-23 : 22:14:17

I do have temp table:
id date
---------------------
1 1/12/2007 10:00:00
1 1/12/2007 11:11:00
2 1/12/2007 10:00:01
2 1/12/2007 11:12:00
3 1/12/2007 10:12:00
3 1/12/2007 11:25:00
4 1/12/2007 10:25:00

I have a final table like this
Id In out
--------------------------------------------
1 1/12/2007 10:00:00 1/12/2007 11:11:00
2 1/12/2007 10:00:01 1/12/2007 11:12:00
3 1/12/2007 10:12:00 1/12/2007 11:25:00
4 1/12/2007 10:25:00 null

In the temp table each id has two records or one record. the first value should go under "In" column and second value should go under "out" column for that particular Id. If the second value is not there then a null value should be inserted.

Do i have to use a cursor to do this or a better way to this???

Any help is appreciated. Thanks...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-23 : 22:31:21

[code]select
ID,
IN = min(date),
OUT =
case
when min(date) = max(date)
then null
else max(date)
end
from
TempTable
group by
ID
order by
ID[/code]

CODO ERGO SUM

cognos79
Posting Yak Master

241 Posts

Posted - 2007-01-23 : 23:22:44

Thanks a lot Michael...It worked...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources