Query Result Not Returned As Expected

I am struggling with a query that tried few days back with a suggestion of one of SO user and got it work with the following: WITH your_table(ID,STOREDATE,VALUE,INFO) AS ( SELECT 1

Solution 1:

Your first big mistake is that 'stordate' is not a date, but a character string representation of a date. Thus, all comparisons will be comparisons of a string, not a date. As a string, which comes first, '01/02/2020' or '02/01/2019'.

WITH your_table(ID,STOREDATE,VALUE,INFO)
AS
(
SELECT1122,to_date('01/01/2020','dd/mm/yyyy'),2,'DONE'UNIONALLSELECT1122,to_date('01/02/2020','dd/mm/yyyy'),1,'DONE'UNIONALLSELECT1122,to_date('01/03/2020','dd/mm/yyyy'),7,'DONE'UNIONALLSELECT1122,to_date('01/04/2020','dd/mm/yyyy'),8,'DONE'
),

Solution 2:

Can you please check this bellow solution-

DEMO HERE

DEMO2 HERE

WITH your_table(ID,STOREDATE,VALUE,INFO)
AS
(
SELECT1122,'1/1/2020',2,'DONE'UNIONALLSELECT1122,'1/2/2020',7,'DONE'UNIONALL-- This two returns zeroSELECT1122,'1/3/2020',1,'DONE'UNIONALL-- As 1 and 7 have sum of 8; it should check once I mean the sum SELECT1122,'1/4/2020',7,'DONE'UNIONALL-- Unfortunately this returns zero as wellSELECT4466,'1/1/2020',2,'DONE'UNIONALLSELECT4466,'1/2/2020',7,'DONE'UNIONALLSELECT4466,'1/3/2020',1,'DONE'UNIONALLSELECT4466,'1/4/2020',8,'DONE'
),

CTE AS
(
    SELECT ID,
    STOREDATE,
    VALUE,
    CASEWHENVALUE=8THEN0WHENVALUE+LAG(VALUE) OVER(ORDERBY ID, STOREDATE) =8AND ID =LAG(ID) OVER(ORDERBY ID, STOREDATE) THEN0WHENVALUE+LEAD(VALUE) OVER(ORDERBY ID, STOREDATE) =8AND ID =LEAD(ID) OVER(ORDERBY ID, STOREDATE) THEN0ELSEVALUEEND VALUE2,
    INFO
    FROM your_table
)

SELECT*,
CASEWHEN 
        (
            SELECTCOUNT(*) FROM CTE A 
            WHERE A.VALUE2 =0AND A.STOREDATE < B.STOREDATE
            AND A.ID = B.ID
        ) >=1AND B.VALUE =8THEN B.VALUE

    WHEN
        (
            SELECTSUM(A.VALUE) 
            FROM CTE A 
            WHERE A.VALUE2 =0AND A.STOREDATE < B.STOREDATE
            AND A.ID = B.ID
        )>=8THEN B.VALUE

    ELSE B.VALUE2
END VALUE3
FROM CTE B