|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2009 9:12:52 PM
Posts: 5,
Visits: 26
|
|
I need to count frequency of each status whenever it changes its status for below sample:
no date Status
111 8/15/2009 2
111 8/16/2009 2
111 8/17/2009 3
111 8/18/2009 3
111 8/19/2009 4
111 8/20/2009 2
111 8/21/2009 4
111 8/22/2009 4
I want something like this:
No #Status2 #Status3 #Status4
111 2 1 2
Thanks,
Regards,
AP
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
You can try below query, I have assumed 5 status in this example:
Select
(
B.No,
sum(Status1) as Status1,
sum(Status2) as Status2,
sum(Status3) as Status3,
sum(Status4) as Status4,
sum(Status5) as Status5
from
(
Select
A.No,
Case when Status = 1 Then A.STS_CNT
Else 0 End as Status1,
Case when Status = 2 Then A.STS_CNT
Else 0 End as Status2,
Case when Status = 3 Then A.STS_CNT
Else 0 End as Status3,
Case when Status = 4 Then A.STS_CNT
Else 0 End as Status4,
Case when Status = 5 Then A.STS_CNT
Else 0 End as Status5
from
(
Select No, Status, Count(*) as STS_CNT from table1
group by 1,2
) A
) B
group by 1
Regards,
Balamurugan
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2009 9:12:52 PM
Posts: 5,
Visits: 26
|
|
Hi Bala,
Thanks for your response.
Your solution gives number of count each status have been occurred. However i require for example for Status 2 the count should be two as change happens only twice ie Status 2 to Status 3 and then Status 4 to status 2.
Regards,
AP
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
I used slightly different data to test - I added an extra Status 3 record in the middle (to valiodate what happens when you have more than two records in a sequence with the same status!).
So my test data was:
111 2009-08-15 2
111 2009-08-16 2
111 2009-08-17 3
111 2009-08-18 3
111 2009-08-19 3
111 2009-08-20 4
111 2009-08-21 2
111 2009-08-22 4
111 2009-08-23 4
The following query gives the answer you need - with or without my extra row!):
Select
"No"
, Sum(Case When PrevStatus = 1 Then 1 Else 0 End) As Status1
, Sum(Case When PrevStatus = 2 Then 1 Else 0 End) As Status2
, Sum(Case When PrevStatus = 3 Then 1 Else 0 End) As Status3
, Sum(Case When PrevStatus = 4 Then 1 Else 0 End) As Status4
, Sum(Case When PrevStatus = 5 Then 1 Else 0 End) As Status5
, Sum(Case When PrevStatus = 6 Then 1 Else 0 End) As Status6
From
(Select "No"
, "Date"
, Status
, Min("Status") Over (Partition By "No"
Order By "Date"
Rows Between 1 Preceding and 1 Preceding) As PrevStatus
From Tbl1
Qualify PrevStatus <> Status
Union
Select
"No", "Date", Status, Status
From Tbl1
Qualify Row_Number()
Over (Partition By "No"
Order By "Date" Desc) = 1) As D1
Group By 1
Order By 1;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/28/2009 9:12:52 PM
Posts: 5,
Visits: 26
|
|
You are Champion Jimm !!!
It indeed gives me answer what i was looking for.
Thanks,
Regards,
AP
|
|
|
|