Author |
Topic |
Novalobster
Starting Member
2 Posts |
Posted - 2013-07-06 : 13:54:03
|
Hi there,I encountered a tricky problem. The original data, say, table_o, is shown below:Ids Status DateID1 4 02-May-13ID1 3 10-May-13ID1 2 16-May-13ID1 1 20-May-13ID2 3 08-May-13ID2 2 10-May-13ID2 1 19-May-13The final resulting table, e.g., table_f, is:Ids 4->3 3->2 2->1 ID1 8 6 4ID2 NULL 2 9The values in the final table are the days used by each ID transferring from status i to status i-1. E.g., ID uses 8 days (10-May-13 - 2-May-13) to go to status 3 from status 4. It is hard for me to come up with a table like the final table, although I know that the difference between two adjacent rows can be computed by using self-join and timediff(). Thanks for your input! |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-07-06 : 19:47:31
|
Maybe you can use this:select o1.ids ,sum(case when o1.status=3 then datediff(day,o2.date,o1.date) else 0 end) as d3 ,sum(case when o1.status=2 then datediff(day,o2.date,o1.date) else 0 end) as d2 ,sum(case when o1.status=1 then datediff(day,o2.date,o1.date) else 0 end) as d1 from table_o as o1 left outer join table_o as o2 on o2.ids=o1.ids and o2.status=o1.status+1 where o1.status between 1 and 3 group by o1.ids order by o1.ids ps.: there might be syntax errors, as I don't have access to database at the moment. |
 |
|
Novalobster
Starting Member
2 Posts |
Posted - 2013-07-06 : 20:16:45
|
Thank you. Probably I will change d3 to "4 to 3", d2 to "3 to 2", and so on. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-07 : 10:21:52
|
[code]declare @t table(Ids varchar(5),[Status] int,[Date] date)insert @tvalues ('ID1', 4, '02-May-13'),('ID1', 3, '10-May-13'),('ID1', 2, '16-May-13'),('ID1', 1, '20-May-13'),('ID2', 3, '08-May-13'),('ID2', 2, '10-May-13'),('ID2', 1, '19-May-13')SELECT IDs,DATEDIFF(dd,MAX(CASE WHEN Status=4 THEN date END),MAX(CASE WHEN Status=3 THEN date END)) AS [4-3],DATEDIFF(dd,MAX(CASE WHEN Status=3 THEN date END),MAX(CASE WHEN Status=2 THEN date END)) AS [3-2],DATEDIFF(dd,MAX(CASE WHEN Status=2 THEN date END),MAX(CASE WHEN Status=1 THEN date END)) AS [2-1]FROM @tCROSS JOIN (VALUES (0),(-1),(-2),(-3))t1(n)GROUP BY IDs--,[Status]+noutput-----------------------------------IDs 4-3 3-2 2-1-----------------------------------ID1 8 6 4ID2 NULL 2 9[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|