Need Help with T-SQL
-
Hi,
I have a Log Table which has the following columns:
ID, TktNbr, Previous Status, Current Status, Date Created
1 AB12 0 1 date1
2 AB12 1 2 date2
3 AB12 2 2 date3
4 AB13 2 2 date4
Now there are multiple status changes possible for a given ticket number and each activity is logged in this Log Table.
Now I need to find out the Time in each status for each ticket.
Tkt Status Time in
AB12 1 date2-date1
AB12 2 getdate()-min(date2,date3)
Can anybody help me with this query or atleast guide me in how to achieve this.
Cheers,
Arvind -
It's a little late and I have to go home so I can't try this but I would solve that with something lke:
Select TK, Stat Max(Dte) - Min(Dte) as [Time in]
(select TktNbr TK, [Previous Status] Stat, [Date Created] Dte
From BaseTable
Union all
select TktNbr TK, [Current Status] Stat, [Date Created] Dte
From BaseTable ) Q
Where Stat <> 0
Group by TK, Stat
Order By Tk, Stat
HTH
* Noel -
That one is a good one M8!! But doesnt exactly work as i require it to in my case.
Since there is a possibility of multiple status changes in the sense that status
Cur Status Prev Status
0 9
9 1
1 1
1 1
1 5
5 1
1 2
2 5
something like this is very much possible. Lemme know in case i can modify this query in such a way as to work this out.
Cheers!
Arvind -
select a.TktNbr, a.[Current Status] as [Status],
DATEADD(second,SUM(DATEDIFF(second,a.[Date Created],ISNULL(n.[Date Created],GETDATE()))),'') as [Time In]
from (select ID, TktNbr, [Previous Status], [Current Status], [Date Created]
,(select min(ID) as [ID] from #BaseTable b2 where b2.TktNbr = b.TktNbr and b2.ID > b.ID group by b2.TktNbr) NextID
from #BaseTable b) a
left outer join #BaseTable n on n.ID = a.NextID
group by a.TktNbr, a.[Current Status]
order by a.TktNbr, a.[Current Status]
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply