CASE statement and partition by

Question

Post reply

CASE statement and partition by

JeremyU

SSChasing Mays

Points: 637

December 30, 2021 at 11:19 pm

#3968562

Hello everyone,

I have a question if someone may know how to adjust the code correctly.

I need just to edit the last outer SELECT statement.

I need to change the partition by DMDUNIT, I need outer CASE statement to look at 2 columns:

1) Start Date - if we have 3 distinct weeks worth of data. If yes look at 2) Issues - If we have 3 issues there then return in this column '3INARow' - "Yes".

Examples: no issues in "Issues" - NO for all 3 in "3In A Row' if we have 3 issue in "Issues" and 3 weeks of data in "StartDate" - yes for all in '3In A Row" if we have 2 issues and 1 non issue - no for all in "3 In A Row' -it should look at Start Date and see if we have 3 separate weeks and later see issues (if we have 3 issues)

Issue1

Forecast and Actuals are separate CTEs that i didn't paste the code in here.

  SELECT
        COALESCE(f.LOC, a.LOC) AS LOC,
        COALESCE(f.DMDUNIT, a.DMDUNIT) AS DMDUNIT,
        COALESCE(f.STARTDATE, a.DMDPostDate) AS "Start Date",
        SUM(F.TOTFCST) AS "Forecast",
        SUM(a.HistoryQuantity) AS "Actuals",
        SUM(ABS(a.HistoryQuantity) - f.TOTFCST) AS "Abs Error",
        (1 - (SUM(A.[HistoryQuantity]) - SUM(F.[TOTFCST])) / SUM(A.[HistoryQuantity])) as 'FA%',
        SUM(a.HistoryQuantity) / SUM(f.TOTFCST) AS "Bias"
        ,CASE WHEN SUM(F.[TOTFCST]) > SUM(A.[HistoryQuantity]) THEN 'Overforecasted' 
        WHEN SUM(F.[TOTFCST]) < SUM(A.[HistoryQuantity]) THEN 'Underforecasted'
        WHEN SUM(A.[HistoryQuantity]) IS NULL AND SUM(F.[TOTFCST]) > 0 THEN 'Overforecasted'
        WHEN SUM(F.[TOTFCST]) IS NULL AND SUM(A.[HistoryQuantity]) > 0 THEN 'Underforecasted' 
        WHEN SUM(F.[TOTFCST]) = 0.000 AND SUM(A.[HistoryQuantity]) IS NULL THEN 'No issue'
        
        END AS 'Issue'
    FROM Forecast f FULL OUTER JOIN Actuals a
        ON f.LOC = a.LOC AND f.DMDUNIT = a.DMDUNIT AND f.STARTDATE = a.DMDPostDate
        WHERE f.DMDUNIT='086002053633'
    GROUP BY
        COALESCE(f.LOC, a.LOC),
        COALESCE(f.DMDUNIT, a.DMDUNIT),
        COALESCE(f.STARTDATE, a.DMDPostDate)

)
select *,
    case when
        min(Issue) over (
            partition by DMDUNIT
            order by "Start Date" ) =
        max(Issue) over (
            partition by DMDUNIT
            order by "Start Date" ) and
        count(Issue) over (
            partition by DMDUNIT
            order by "Start Date" ) = 3

        then 'No' else 'Yes' end as "3InARow"
from Merged;

January 1, 2022 at 9:19 am

This was removed by the editor as SPAM
January 12, 2022 at 9:04 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

You'd evaluate "3 in a row" rule with something like

ThreeInARow = CASE WHEN columName = LAG([columnName],1) AND columName = LAG([columnName],2) THEN 1 ELSE 0 END

JeremyU SSChasing Mays Points: 637 More actions · Answer 2

@pietlinden

I am not sure I understand.

I basically need

select *,

case when 'Issue' IN ('Overforecasted', 'Underforecasted') AND 'Start Date' DISTINCT 3 dates THEN 'Issue exists for 3 weeks in a row'

FROM Merged;

Do you know how it can be properly written?

JeremyU SSChasing Mays Points: 637 More actions · Answer 3

I basically need to write a SQL code for such a statement.

I know this is not a proper SQL statement, but I am trying to understand how it can be adjusted so that it fits the criteria.

When "Issue" IN ('Overforecasted', 'Underforecasted' AND there are 3 different dates in column "Start Date" (can be more than 3 weeks but should be at least 3 different dates) return "Issue exists for 3 weeks" in the column "3InARow"

select *,
    case when 'Issue' IN ('Overforecasted', 'Underforecasted') AND 'Start Date' DISTINCT 3 dates THEN 'Issue exists for 3 weeks in a row'
    FROM Merged;

Issue1

JeremyU SSChasing Mays Points: 637 More actions · Answer 4

When "Issue" IN ('Overforecasted', 'Underforecasted' AND there are 3 different dates in column "Start Date" (can be more than 3 weeks but should be at least 3 different dates) return "Issue exists for 3 weeks" in the column "3InARow"

Per one DMDUNIT check if it has 3 issues in column "Issues" and later check if it has 3 different start dates. If it has 3 issues ('Overforecasted', "Underforecasted") and 3 different dates for the same DMDUNIT I need to return it in a new column (end as "3InARow")

Issue1

select *,
    case when 'Issue' IN ('Overforecasted', 'Underforecasted') AND 'Start Date' DISTINCT 3 dates THEN 'Issue exists for 3 weeks in a row'
    FROM Merged;

pietlinden SSC Guru Points: 63428 More actions · Answer 5

Each location with at least 3 different start dates.

SELECT loc
FROM (VALUES ('SAMSCLUB.COM','2021-12-05',42110)
,('SAMSCLUB.COM','2021-12-12',11731)
,('SAMSCLUB.COM','2021-12-19',9886)) d(loc,StartDate,Forecast)
GROUP BY loc
HAVING COUNT(DISTINCT(d.StartDate)) >= 3;

loc column will contain all values for which the predicate is true. Then you can just join to that or use it as a filter...

SELECT [field list]
FROM 

WHERE loc IN (SELECT loc
FROM (VALUES ('SAMSCLUB.COM','2021-12-05',42110)
,('SAMSCLUB.COM','2021-12-12',11731)
,('SAMSCLUB.COM','2021-12-19',9886)) d(loc,StartDate,Forecast)
GROUP BY loc
HAVING COUNT(DISTINCT(d.StartDate)) >= 3);

JeremyU SSChasing Mays Points: 637 More actions · Answer 6

@pietlinden thank you. I am really sorry for taking your time. It is pretty complex for me to figure out how to use it in my query. In my first post i have posted my code, could you let me know how i need to arrange it with your code?

I am sure it must work but as an overall combination it sounds pretty difficult to understand for me.

pietlinden SSC Guru Points: 63428 More actions · Answer 7

Please help us to help you. Read Jeff Moden's article "Forum Etiquette: How to post data/code on a forum to get the best help", and follow the instructions outlined in the article. Then it will be a whole lot easier for everyone.

And who doesn't like tested solutions?