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
(
SELECT 1122,to_date('01/01/2020','dd/mm/yyyy'),2,'DONE' UNION ALL
SELECT 1122,to_date('01/02/2020','dd/mm/yyyy'),1,'DONE' UNION ALL
SELECT 1122,to_date('01/03/2020','dd/mm/yyyy'),7,'DONE' UNION ALL
SELECT 1122,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
(
SELECT 1122,'1/1/2020',2,'DONE' UNION ALL
SELECT 1122,'1/2/2020',7,'DONE' UNION ALL -- This two returns zero
SELECT 1122,'1/3/2020',1,'DONE' UNION ALL -- As 1 and 7 have sum of 8; it should check once I mean the sum 
SELECT 1122,'1/4/2020',7,'DONE' UNION ALL -- Unfortunately this returns zero as well
SELECT 4466,'1/1/2020',2,'DONE' UNION ALL
SELECT 4466,'1/2/2020',7,'DONE' UNION ALL
SELECT 4466,'1/3/2020',1,'DONE' UNION ALL
SELECT 4466,'1/4/2020',8,'DONE'
),

CTE AS
(
    SELECT ID,
    STOREDATE,
    VALUE,
    CASE 
        WHEN VALUE = 8 THEN 0
        WHEN VALUE + LAG(VALUE) OVER(ORDER BY ID, STOREDATE) = 8 
            AND ID = LAG(ID) OVER(ORDER BY ID, STOREDATE) THEN 0
        WHEN VALUE + LEAD(VALUE) OVER(ORDER BY ID, STOREDATE) = 8 
            AND ID = LEAD(ID) OVER(ORDER BY ID, STOREDATE) THEN 0
        ELSE VALUE
    END VALUE2,
    INFO
    FROM your_table
)

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

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

    ELSE B.VALUE2
END VALUE3
FROM CTE B