How to Count Multiple Status

[Abhi93]

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

[Balamurugan_B]

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

[Abhi93]

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

[Jimm]

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;

[Abhi93]

You are Champion Jimm !!!

It indeed gives me answer what i was looking for.

Thanks,

Regards,

AP

[aerobhuvan]

Hi,

I have to use single select statement to get count of the field as two column depending upon the Where condition. Can u please help me out.

I'm new to teradata. So i 'm feeling tough with its syntax

Thanks & Regards,

Bhuvan