Aggregate over value change
-
Hi,
I am trying to figure out how to calculate sequences in my Slots table.
My table looks like this:
SlotNumber,
StartTime (in minutes),
Duration (in minutes),
Status
I need to calculate sequences of the same status in the table.
for example, the date is:
Slot Number Start Time Duration Status
1 0 30 0
2 30 20 0
3 50 20 1
4 70 20 0
my sequences should be:
first slot last slot total duration status
1 2 50 0
3 3 20 1
4 4 20 0
Is there a way to use PARTITION BY based on a value change which will restart every time the value change?
-
Try this
CREATE TABLE MyTable(SlotNumber INT, StartTime INT, Duration INT, Status INT);
INSERT INTO MyTable(SlotNumber, StartTime, Duration, Status)
SELECT 1, 0,30,0 UNION ALL
SELECT 2,30,20,0 UNION ALL
SELECT 3,50,20,1 UNION ALL
SELECT 4,70,20,0;
WITH CTE AS (
SELECT SlotNumber, StartTime, Duration, Status,
ROW_NUMBER() OVER(ORDER BY StartTime) AS rn1,
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY StartTime) AS rn2
FROM MyTable)
SELECT MIN(SlotNumber) AS FirstSlot,
MAX(SlotNumber) AS LastSlot,
SUM(Duration) AS TotalDuration,
Status
FROM CTE
GROUP BY rn1-rn2,Status
ORDER BY MIN(SlotNumber);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
This is awesome, just what i needed.
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply