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;
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