Ryan Barker Ryan Barker - 3 months ago 18
SQL Question

Oracle SQL Trending MTD Data

I am trying to solve a trending problem at work very similar to the below example. I think I have a method but don't know how to do it in SQL.

The input data is:

1-Apr-16 1 Y
1-Apr-16 2 N
1-May-16 1 N
1-May-16 2 N
1-Jun-16 1 N
1-Jun-16 2 N
1-Jul-16 1 Y
1-Jul-16 2 N
1-Aug-16 1 N
1-Aug-16 2 Y

The desired output is:

1-Apr-16 1 Y New
1-May-16 1 N No Rain
1-Jun-16 1 N No Rain
1-Jul-16 1 Y Carryover
1-Aug-16 1 N No Rain
1-Apr-16 2 N No Rain
1-May-16 2 N No Rain
1-Jun-16 2 N No Rain
1-Jul-16 2 N No Rain
1-Aug-16 2 Y New

I'm trying to produce the output from the input by trending on MTD without depending on it. This way, when new months are added to the input, the output changes without editing the query.

The logic for TRENDS will occur on each unique LOC_ID. Trends will have three values: "New" in the first month RAINED is "Y", "Carryover" in any following months where RAINED is "Y", and "No Rain" in any months where RAINED is "N".

I'd like to automate this problem by introducing an intermediate step with a listagg. For example, for LOC_ID = "1":

1-Apr-16 1 Y (null) / 0 / (I don't care)
1-May-16 1 N Y
1-Jun-16 1 N Y;N
1-Jul-16 1 Y Y;N;N
1-Aug-16 1 N Y;N;N;Y

This way, to produce "TRENDS" in the output, I can say:

case when RAINED = 'Y' then
case when not regexp_like(PREV_RAINED, 'Y', 'i') then
'No Rain'
end as TRENDS

My problem is that I'm not sure how to produce PREV_RAINED for each unique LOC_ID. I have a feeling it needs to combine LAG() statements and partition by LOC_ID order by MTD, but the number of lags I need to do depends on each month.

Is there an easy way to produce PREV_RAINED or a simpler way to solve my overall problem while preserving automation each month?

Thanks for reading all of this! :)


In the below SQL there are two parts.

(i) Calculating the ROWNUMBER value for rained attribute at loc_id,rained level.
(ii) Get the count at partition level loc_id,rained.

By computing the above two we can write the CASE WHEN logic to calculate the trends based on your requirement.

       CASE WHEN rained = 'N' THEN 'No Rain'
            WHEN rained = 'Y' AND rn = 1 THEN 'New'
            ELSE 'Carry Over'    
        END AS Trends       
            SELECT mtd,
                   ROW_NUMBER() OVER ( PARTITION BY loc_id,rained ORDER BY mtd ) AS rn,
                   COUNT(*) OVER ( PARTITION BY loc_id,rained ) AS count_locid_rained               
              FROM INPUT
              ORDER BY loc_id,mtd,rained,rn
         ) X;