July 21, 2015 at 1:08 pm
Hi All,
I have an issue accumulating a value based on certain conditions:
I need to sum up the days column as you can see table below.
The sum is based on the StartNewFlag.
When the flag is 1, I need to start a new sum from zero, else I need to accumulate to the previous rows.
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
Days int
)
INSERT INTO #foo VALUES
(1, '7/1/2015', '7/5/2015', 1, 4),
(1, '7/8/2015', '7/9/2015', 0, 1),
(1, '7/11/2015', '7/15/2015', 1, 4),
(1, '7/18/2015', '7/21/2015', 0, 3),
(1, '7/22/2015', '7/26/2015', 0, 4)
SELECT *
FROM #foo
DROP TABLE #foo
My Results must look like one of the following tables (either one would work, however the second one is preferred)
Result 1:
ItemID StartDate EndDate StartNewFlag Days TotalDays
1 2015-07-01 00:00:00.000 2015-07-05 00:00:00.000 1 4 5
1 2015-07-08 00:00:00.000 2015-07-09 00:00:00.000 0 1 5
1 2015-07-11 00:00:00.000 2015-07-15 00:00:00.000 1 4 11
1 2015-07-18 00:00:00.000 2015-07-21 00:00:00.000 0 3 11
1 2015-07-22 00:00:00.000 2015-07-26 00:00:00.000 0 4 11
OR
Result 2:
ItemID EndDate TotalDays
1 2015-07-09 00:00:00.000 5
1 2015-07-26 00:00:00.000 11
July 21, 2015 at 1:50 pm
This might help you. Be sure to test it and understand it. Ask any questions that you might have.
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY EndDate)
- ROW_NUMBER() OVER(PARTITION BY ItemID, StartNewFlag ORDER BY EndDate)
+ StartNewFlag AS Grouper
FROM #foo
)
SELECT ItemID,
MAX(EndDate) AS EndDate,
SUM( Days) AS TotalDays
FROM CTE
GROUP BY ItemID, Grouper;
EDIT: Correction pointed out by Jacob
July 21, 2015 at 1:56 pm
Luis posted his while I was typing, but here's my solution as well:
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
[Days] int
)
INSERT INTO #foo VALUES
(1, '7/1/2015', '7/5/2015', 1, 4),
(1, '7/8/2015', '7/9/2015', 0, 1),
(1, '7/11/2015', '7/15/2015', 1, 4),
(1, '7/18/2015', '7/21/2015', 0, 3),
(1, '7/22/2015', '7/26/2015', 0, 4)
SELECT *
FROM #foo;
WITH CTE AS (
SELECT
AbsRN=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY StartDate ASC),
RelRN=ROW_NUMBER() OVER (PARTITION BY ItemID, StartNewFlag ORDER BY StartDate ASC),
ItemID,
StartDate,
EndDate,
StartNewFlag,
[Days]
FROM #foo)
SELECT
ItemID,
EndDate=MAX(EndDate),
TotalDays=SUM([Days])
FROM CTE
GROUP BY ItemID,CASE WHEN StartNewFlag=1 THEN RelRN ELSE AbsRN-RelRN END
ORDER BY MAX(EndDate) ASC;
DROP TABLE #foo
Luis' will also work quite nicely, but the second value in the GROUP BY should be Grouper, methinks.
Cheers!
July 21, 2015 at 2:01 pm
You're right Jacob. That's what I get for not testing my code. 😀
July 21, 2015 at 3:51 pm
Heh, no worries. These things happen 🙂
As it turns out, I think I may have the misfortune of being the bearer of more bad news. It seems that solution relies on the expression
ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY EndDate)
- ROW_NUMBER() OVER(PARTITION BY ItemID, StartNewFlag ORDER BY EndDate)
always being one less for rows with StartNewFlag=1 than the value of the same expression for the rows in the same group that have StartNewFlag=0.
That won't be true if a preceding group has had a different number of rows with StartNewFlag=1 than with StartNewFlag=0 (so, either a single-row group, or a group with more than 2 rows).
Something like these two sample sets:
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
[Days] int
)
--A preceding group has more than two rows
INSERT INTO #foo VALUES
(1, '7/1/2015', '7/5/2015', 1, 4),
(1, '7/8/2015', '7/9/2015', 0, 1),
(1, '7/11/2015', '7/15/2015', 1, 4),
(1, '7/18/2015', '7/21/2015', 0, 3),
(1, '7/22/2015', '7/26/2015', 0, 4),
(1, '7/27/2015', '7/28/2015', 1, 1),
(1, '7/28/2015', '7/29/2015', 0, 1)
SELECT *
FROM #foo;
WITH CTE AS (
SELECT
AbsRN=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY StartDate ASC),
RelRN=ROW_NUMBER() OVER (PARTITION BY ItemID, StartNewFlag ORDER BY StartDate ASC),
ItemID,
StartDate,
EndDate,
StartNewFlag,
[Days]
FROM #foo)
SELECT
ItemID,
EndDate=MAX(EndDate),
TotalDays=SUM([Days])
FROM CTE
GROUP BY ItemID,CASE WHEN StartNewFlag=1 THEN RelRN ELSE AbsRN-RelRN END
ORDER BY MAX(EndDate) ASC;
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY EndDate)
- ROW_NUMBER() OVER(PARTITION BY ItemID, StartNewFlag ORDER BY EndDate)
+ StartNewFlag AS Grouper
FROM #foo
)
SELECT ItemID,
MAX(EndDate) AS EndDate,
SUM( Days) AS TotalDays
FROM CTE
GROUP BY ItemID, Grouper
TRUNCATE TABLE #foo
--A preceding group has just one row
INSERT INTO #foo VALUES
(1, '7/1/2015', '7/5/2015', 1, 4),
(1, '7/11/2015', '7/15/2015', 1, 4),
(1, '7/27/2015', '7/28/2015', 1, 1)
SELECT *
FROM #foo;
WITH CTE AS (
SELECT
AbsRN=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY StartDate ASC),
RelRN=ROW_NUMBER() OVER (PARTITION BY ItemID, StartNewFlag ORDER BY StartDate ASC),
ItemID,
StartDate,
EndDate,
StartNewFlag,
[Days]
FROM #foo)
SELECT
ItemID,
EndDate=MAX(EndDate),
TotalDays=SUM([Days])
FROM CTE
GROUP BY ItemID,CASE WHEN StartNewFlag=1 THEN RelRN ELSE AbsRN-RelRN END
ORDER BY MAX(EndDate) ASC;
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY EndDate)
- ROW_NUMBER() OVER(PARTITION BY ItemID, StartNewFlag ORDER BY EndDate)
+ StartNewFlag AS Grouper
FROM #foo
)
SELECT ItemID,
MAX(EndDate) AS EndDate,
SUM( Days) AS TotalDays
FROM CTE
GROUP BY ItemID, Grouper
DROP TABLE #foo
I promise I'm not just trying to be critical! 🙂
Cheers!
July 22, 2015 at 9:35 am
Thank you all for your support. I implemented Jacob's solution and it is doing exactly what I need it to do.
Jacob, I think your grouping approach is quite clever.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply