Pls help ..query - 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)

Pls help ..query

Author

Topic

rajukurian
Starting Member

8 Posts

Posted - 2007-01-03 : 15:10:58

Hi all

I got a table like

orderno date1 date2 statusbefore statusafter
------- ----- ----- ------------- ----------
1 08-10-2006 09-10-2006 a b
1 09-10-2006 10-10-2006 b c
1 11-10-2006 12-10-2006 c d
2 08-10-2006 09-10-2006 a b
2 09-10-2006 10-10-2006 b c
2 11-10-2006 12-10-2006 c d
3 08-10-2006 09-10-2006 a b
3 09-10-2006 10-10-2006 b c
3 11-10-2006 12-10-2006 c d

Now i want the o/p like this

orderno timetaken (a-b) timetaken (b-c) timetaken (c-d)

only one record for each order no..

pls .
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 15:41:57

[code]-- prepare sample data
declare @t table (orderno int, date1 datetime, date2 datetime, statusbefore varchar, statusafter varchar)

insert @t
select 1, '08-10-2006', '09-10-2006', 'a', 'b' union all
select 1, '09-10-2006', '10-10-2006', 'b', 'c' union all
select 1, '11-10-2006', '12-10-2006', 'c', 'd' union all
select 2, '08-10-2006', '09-10-2006', 'a', 'b' union all
select 2, '09-10-2006', '10-10-2006', 'b', 'c' union all
select 2, '11-10-2006', '12-10-2006', 'c', 'd' union all
select 3, '08-10-2006', '09-10-2006', 'a', 'b' union all
select 3, '09-10-2006', '10-10-2006', 'b', 'c' union all
select 3, '11-10-2006', '12-10-2006', 'c', 'd'

-- show the final result
select orderno,
max(case when statusbefore = 'a' and statusafter = 'b' then datediff(day, date1, date2) end) 'a-b',
max(case when statusbefore = 'b' and statusafter = 'c' then datediff(day, date1, date2) end) 'b-c',
max(case when statusbefore = 'c' and statusafter = 'd' then datediff(day, date1, date2) end) 'c-d'
from @t
group by orderno
order by orderno[/code]

Peter Larsson
Helsingborg, Sweden

rajukurian
Starting Member

8 Posts

Posted - 2007-01-03 : 15:46:37

thanks peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources