Teradata SQL - Data ranking

Database
Enthusiast

Teradata SQL - Data ranking

I have the fields code, date_x,fact_x and I would like to see the data in fourth col4 as mentioned below.

codedate_xfact_xCol4
1101-sep-160 
1102-sep-160 
1103-sep-160 
1104-sep-160 
1105-sep-160 
1106-sep-1611.231
1107-sep-1645.661
1108-sep-160 
1109-sep-1612.672
1110-sep-1613.222
1111-sep-160 
1112-sep-160 
1113-sep-160 
1114-sep-1619.113
1115-sep-1617.223
1116-sep-1626.333
1117-sep-1612.663
1118-sep-160 
1119-sep-160 
1120-sep-1666.334
1121-sep-1623.004
1122-sep-160 

 

Refer the attached screenshot for data. I tried different ways but it is getting complex. Please help ! 

SQL_Scenario.PNG

 


Accepted Solutions
Junior Contributor

Re: Teradata SQL - Data ranking

Seems like you want to assign a group number for consecutive day with non-zero values:

SELECT code, date_x, fact_x, 
   CASE WHEN fact_x > 0 
        THEN Sum(flag) -- calculate group number
             Over (PARTITION BY code 
                   ORDER BY date_x 
                   ROWS Unbounded Preceding)
   END
FROM 
 (
   SELECT code, date_x, fact_x, 
      CASE WHEN Coalesce(Max(fact_x) -- previous row = 0
                         Over (PARTITION BY code 
                               ORDER BY date_x 
                               ROWS BETWEEN 1 Preceding AND 1 Preceding), 0) = 0
            AND fact_x > 0           -- current row > 0
      THEN 1                         --> start of a new group
      ELSE 0
      END AS flag
   FROM tab
 ) AS dt

 

1 ACCEPTED SOLUTION
6 REPLIES
Junior Supporter

Re: Teradata SQL - Data ranking


I wanted to delete my post but was not able to do so. Admin can you please delete this post.

Junior Contributor

Re: Teradata SQL - Data ranking

Seems like you want to assign a group number for consecutive day with non-zero values:

SELECT code, date_x, fact_x, 
   CASE WHEN fact_x > 0 
        THEN Sum(flag) -- calculate group number
             Over (PARTITION BY code 
                   ORDER BY date_x 
                   ROWS Unbounded Preceding)
   END
FROM 
 (
   SELECT code, date_x, fact_x, 
      CASE WHEN Coalesce(Max(fact_x) -- previous row = 0
                         Over (PARTITION BY code 
                               ORDER BY date_x 
                               ROWS BETWEEN 1 Preceding AND 1 Preceding), 0) = 0
            AND fact_x > 0           -- current row > 0
      THEN 1                         --> start of a new group
      ELSE 0
      END AS flag
   FROM tab
 ) AS dt

 

Enthusiast

Re: Teradata SQL - Data ranking

 

It is giving cummulative sum for each non zero set of facts. Would like to get rank(for first group 1 and the second group 2 , etc...) for each non zero set of fact values to find the number of days for each group like mentioned below.

 

codedatefact_xCol4
1101-sep-160 0
1102-sep-160 0
1103-sep-160 0
1104-sep-160 0
1105-sep-160 0
1106-sep-1611.231
1107-sep-1645.661
1108-sep-160 0
1109-sep-1612.672
1110-sep-1613.222
1111-sep-160 0
1112-sep-160 0
1113-sep-160 0
1114-sep-1619.113
1115-sep-1617.223
1116-sep-1626.333
1117-sep-1612.663
1118-sep-160 0
1119-sep-160 0
1120-sep-1666.334
1121-sep-1623.004
1122-sep-160 0
Junior Contributor

Re: Teradata SQL - Data ranking

This is exactly what my query returns (when you add "ELSE 0" in the outer CASE.
Enthusiast

Re: Teradata SQL - Data ranking

But I dont need cummulative sum for the facts. I just required numbering  starting 1 (as given in sample data) for each set of facts. 

 

with  (Partition  by cd, date_x  ) and (order by date_x )  needs to get the below results 

for the 1st set of facts >>>>  populate 1

next set of zeros in fact >>>>>  populate 0 

for the 2nd set of facts>>>>> populate

next set of zeros in fact >>>>>  populate 0

for the 3rd set of facts>>>>> populate 3

next set of zeros in fact >>>>>  populate 0

for the 4th set of facts>>>>> populate 4

next set of zeros in fact >>>>>  populate 0

............................... 

Enthusiast

Re: Teradata SQL - Data ranking

 Sorry I was wrongly using it. 

 

I got the results already with your query. 

 

You are superb !  Thanks a lot !