Date to record zero balance for an account version
-
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
-
Sounds like you may want an insert/update trigger that sets or nulls out the date depending on the Amount value.
-
-- 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 -
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 - 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 -
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 -
Many thanks for your help! it is much appreciated. I will remember to add the code next time!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply