Date to record zero balance for an account version

  • WardyWonderland

    Say Hey Kid

    Points: 699

    Hi All,

    I'm trying to create the last column on the table below which tracks the latest date for a version of an account where the amount is zero, so the date should remain the same as the first time it went to zero until a new version is greater than zero. It the amount is not zero, NULL should be returned. Tried many things around LEAD, LAG, LAST_VALUE but just cannot seem to get the desired result. Can anyone give me any assistance? Would be very much appreciated.

    Thanks,

    GT

    Data

  • Rob Buecker

    SSC-Addicted

    Points: 494

    Sounds like you may want an insert/update trigger that sets or nulls out the date depending on the Amount value.

  • autoexcrement

    SSCertifiable

    Points: 5885

    -- Drop target scratch table if it already exists:
    IF OBJECT_ID('tempdb..#OPCantBeBothered') IS NOT NULL
    DROP TABLE #OPCantBeBothered
    ;

    -- Create scratch table:
    CREATE TABLE #OPCantBeBothered
    (
    AccountNo INT,
    VersionNo INT,
    Amount INT,
    StatusDate DATE
    )
    ;

    INSERT INTO #OPCantBeBothered
    (
    AccountNo,
    VersionNo,
    Amount,
    StatusDate
    )
    SELECT 1, 1, 750, '20190401' UNION ALL
    SELECT 1, 2, 750, '20190402' UNION ALL
    SELECT 1, 3, 0, '20190403' UNION ALL
    SELECT 1, 4, 0, '20190404' UNION ALL
    SELECT 1, 5, 0, '20190405' UNION ALL
    SELECT 1, 6, 0, '20190406' UNION ALL
    SELECT 1, 7, 0, '20190407' UNION ALL
    SELECT 1, 8, 0, '20190408' UNION ALL
    SELECT 1, 9, 750, '20190409' UNION ALL
    SELECT 1, 10, 750, '20190410' UNION ALL
    SELECT 1, 11, 750, '20190411' UNION ALL
    SELECT 1, 12, 750, '20190412' UNION ALL
    SELECT 1, 13, 0, '20190413' UNION ALL
    SELECT 1, 14, 0, '20190414' UNION ALL
    SELECT 1, 15, 0, '20190415' UNION ALL
    SELECT 1, 16, 0, '20190416' UNION ALL
    SELECT 1, 17, 0, '20190417' UNION ALL
    SELECT 1, 18, 750, '20190418' UNION ALL
    SELECT 1, 19, 0, '20190419' UNION ALL
    SELECT 1, 20, 0, '20190420' UNION ALL
    SELECT 1, 21, 0, '20190421'
    ;

    WITH

    FLAGGED AS
    (
    -- Flag the first new/changed record in each window with a 1:
    SELECT
    *,
    CASE
    -- if this value equals the previous value in the window, or if there is no previous value, then flag it with 0:
    WHEN Amount = LAG(Amount, 1, Amount) OVER(PARTITION BY AccountNo ORDER BY VersionNo) THEN 0
    -- if this value does not equal the previous value in the window, then flag it with 1:
    ELSE 1
    END AS NewFlag
    FROM #OPCantBeBothered
    ),

    ISLANDS AS
    (
    -- Identify islands of contiguous data by summing up the NewFlag values within each window.
    -- Each time the beginning of a new island is reached, the counter will increment by 1.
    -- It will increment by 0 for unchanged rows.
    -- So we wind up with identifiable islands because each island will share the same IslandNumber.
    SELECT
    *,
    SUM(NewFlag) OVER(PARTITION BY AccountNo ORDER BY VersionNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS IslandNumber
    FROM FLAGGED
    )

    SELECT
    AccountNo,
    VersionNo,
    Amount,
    StatusDate,
    CASE WHEN Amount = 0 THEN MIN(StatusDate) OVER(PARTITION BY AccountNo, IslandNumber ORDER BY VersionNo) END AS NewDateCalc
    FROM ISLANDS
    ;


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • autoexcrement

    SSCertifiable

    Points: 5885

    GAWD the new formatting on this site is AWFUL. Can someone please change it back? It's practically unreadable. 🙁


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • drew.allen

    SSC Guru

    Points: 76737

    autoexcrement wrote:

    -- Drop target scratch table if it already exists:
    IF OBJECT_ID('tempdb..#OPCantBeBothered') IS NOT NULL
    DROP TABLE #OPCantBeBothered
    ;

    -- Create scratch table:
    CREATE TABLE #OPCantBeBothered
    (
    AccountNo INT,
    VersionNo INT,
    Amount INT,
    StatusDate DATE
    )
    ;

    INSERT INTO #OPCantBeBothered
    (
    AccountNo,
    VersionNo,
    Amount,
    StatusDate
    )
    SELECT 1, 1, 750, '20190401' UNION ALL
    SELECT 1, 2, 750, '20190402' UNION ALL
    SELECT 1, 3, 0, '20190403' UNION ALL
    SELECT 1, 4, 0, '20190404' UNION ALL
    SELECT 1, 5, 0, '20190405' UNION ALL
    SELECT 1, 6, 0, '20190406' UNION ALL
    SELECT 1, 7, 0, '20190407' UNION ALL
    SELECT 1, 8, 0, '20190408' UNION ALL
    SELECT 1, 9, 750, '20190409' UNION ALL
    SELECT 1, 10, 750, '20190410' UNION ALL
    SELECT 1, 11, 750, '20190411' UNION ALL
    SELECT 1, 12, 750, '20190412' UNION ALL
    SELECT 1, 13, 0, '20190413' UNION ALL
    SELECT 1, 14, 0, '20190414' UNION ALL
    SELECT 1, 15, 0, '20190415' UNION ALL
    SELECT 1, 16, 0, '20190416' UNION ALL
    SELECT 1, 17, 0, '20190417' UNION ALL
    SELECT 1, 18, 750, '20190418' UNION ALL
    SELECT 1, 19, 0, '20190419' UNION ALL
    SELECT 1, 20, 0, '20190420' UNION ALL
    SELECT 1, 21, 0, '20190421'
    ;

    WITH

    FLAGGED AS
    (
    -- Flag the first new/changed record in each window with a 1:
    SELECT
    *,
    CASE
    -- if this value equals the previous value in the window, or if there is no previous value, then flag it with 0:
    WHEN Amount = LAG(Amount, 1, Amount) OVER(PARTITION BY AccountNo ORDER BY VersionNo) THEN 0
    -- if this value does not equal the previous value in the window, then flag it with 1:
    ELSE 1
    END AS NewFlag
    FROM #OPCantBeBothered
    ),

    ISLANDS AS
    (
    -- Identify islands of contiguous data by summing up the NewFlag values within each window.
    -- Each time the beginning of a new island is reached, the counter will increment by 1.
    -- It will increment by 0 for unchanged rows.
    -- So we wind up with identifiable islands because each island will share the same IslandNumber.
    SELECT
    *,
    SUM(NewFlag) OVER(PARTITION BY AccountNo ORDER BY VersionNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS IslandNumber
    FROM FLAGGED
    )

    SELECT
    AccountNo,
    VersionNo,
    Amount,
    StatusDate,
    CASE WHEN Amount = 0 THEN MIN(StatusDate) OVER(PARTITION BY AccountNo, IslandNumber ORDER BY VersionNo) END AS NewDateCalc
    FROM ISLANDS
    ;

    I think you're being thrown by inadequate data.  From what I understand, it doesn't matter whether the amounts have changed, only whether it's zero or not.

    The following gives the same results, but requires fewer reads, scans, and sorts.

    WITH NewDates AS
    (
    SELECT *, CASE WHEN Amount = 0 AND LAG(Amount, 1, 1) OVER(PARTITION BY AccountNo ORDER BY VersionNo) <> 0 THEN StatusDate END AS NewDate
    FROM #OPCantBeBothered
    )
    SELECT AccountNo, VersionNo, Amount, StatusDate, CASE WHEN Amount = 0 THEN MAX(NewDate) OVER(PARTITION BY AccountNo ORDER BY VersionNo ROWS UNBOUNDED PRECEDING) END AS NewDateCalc
    FROM Flagged f
    ;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • autoexcrement

    SSCertifiable

    Points: 5885

    Props to Drew. Was hoping I had kicked ass on this one, but not quite. Damn.


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • WardyWonderland

    Say Hey Kid

    Points: 699

    Many thanks for your help! it is much appreciated. I will remember to add the code next time!

     

Viewing 7 posts - 1 through 7 (of 7 total)

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