August 29, 2006 at 12:32 pm
Hello,
I have a table that I need to read before and after rows for each row processed and include the before_type and after_type as new columns to each row. Here is an example:
date user type amt
8/9/06 1234 break 10.0
8/9/06 1234 break 12.0
8/9/06 1234 task1 7.2
8/9/06 1234 task1 42.5
8/9/06 1234 task1 32.5
8/9/06 1234 break 10.0
8/9/06 1234 task2 14.5
8/9/06 1234 task3 55.5
8/9/06 1234 break 26.5
8/9/06 1234 task4 22.0
I need to transpose the above table in this manner:
date user type amt before-type after-type
8/9/06 1234 break 22.0 null task1
8/9/06 1234 task1 82.2 break break
8/9/06 1234 break 10.0 task1 task2
8/9/06 1234 task2 14.5 break task3
8/9/06 1234 task3 55.5 task2 break
8/9/06 1234 break 26.5 task3 task4
8/9/06 1234 task4 22.0 break null
Any helps or suggestions would be greatly appreciated.
THanks,
GLO
August 29, 2006 at 12:57 pm
I need a clarification on this. How do u know break is the first one and task1 follows break? is there time assiciated with date that tells you that comes first. If there is no column that specifies the order of the data then before and after is does not make sense.
Thanks
Sreejith
August 29, 2006 at 1:15 pm
Yes, there is a timestamp column that dictates the order of all these tasks and breaks in between tasks. The 1st table I included was sorted by user# and timestamp showing the sequence of tasks the user carried out for a given date. Thank you very much and sorry about the confusion.
GLO
August 30, 2006 at 10:54 am
I think this would be best done either in the middle tier or by using a cursor.
If you have less than 1000 rows something like the following ghastly looking query may be worth a try:
-- Test table
declare @t table
(
tDate datetime not null
,tUser int not null
,Type varchar(10) collate database_default not null
,amt money not null
)
-- Test Data
insert @t
select '20060809 09:00', 1234, 'break', 10.0 union all
select '20060809 10:00', 1234, 'break', 12.0 union all
select '20060809 11:00', 1234, 'task1', 7.2 union all
select '20060809 12:00', 1234, 'task1', 42.5 union all
select '20060809 13:00', 1234, 'task1', 32.5 union all
select '20060809 14:00', 1234, 'break', 10.0 union all
select '20060809 15:00', 1234, 'task2', 14.5 union all
select '20060809 16:00', 1234, 'task3', 55.5 union all
select '20060809 17:00', 1234, 'break', 26.6 union all
select '20060809 18:00', 1234, 'task4', 22.0
-- Query
select dateadd(day, datediff(day, 0, D2.DateFrom), 0) as tDate
,D2.tUser
,D2.Type
,(select sum(T4.Amt)
from @t T4
where T4.tUser = D2.tUser
and T4.Type = D2.Type
and T4.tDate between D2.DateFrom and D2.DateTo) as Amt
,(select top 1 T5.Type
from @t T5
where T5.tUser = D2.tUser
and dateadd(day, datediff(day, 0, T5.tDate), 0) = dateadd(day, datediff(day, 0, D2.DateFrom), 0)
and T5.tDate < D2.DateFrom
order by T5.tUser, T5.tDate desc) as before_type
,(select top 1 T6.Type
from @t T6
where T6.tUser = D2.tUser
and dateadd(day, datediff(day, 0, T6.tDate), 0) = dateadd(day, datediff(day, 0, D2.DateFrom), 0)
and T6.tDate > D2.DateTo
order by T6.tUser, T6.tDate) as after_type
from
(
select D.tUser
,D.Type
,min(D.DateFrom) as DateFrom
,D.DateTo
from
(
select T1.tUser
,T1.Type
,T1.tDate as DateFrom
,max(T2.tDate) as DateTo
from @t T1
join @t T2 on T1.TUser = T2.TUser
and T1.Type = T2.Type
and T1.tDate <= T2.tDate
where not exists (select *
from @t T3
where T3.Type <> T1.Type
and T1.TUser = T3.TUser
and T3.tDate between T1.tDate and T2.tDate)
group by T1.tUser, T1.Type, T1.tDate ) D
group by D.tUser, D.Type, D.DateTo) D2
order by D2.tUser, D2.DateFrom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply