August 2, 2018 at 10:34 am
I found this in anther forum and thought it would be a simple query, but I'm drawing a complete blank on it (maybe thinking of weekend beers, not sure). Now it's piqued my curiosity. Basically trying to have running totals where the dates are within 1 day of each other. If 2 days, then start over at 0.
DECLARE @TableDate TABLE (TrxDate date )
INSERT INTO @TableDate
(
TrxDate
)
VALUES( '2018-07-13'),( '2018-07-14'),( '2018-07-15'),( '2018-07-17'),( '2018-07-18'),( '2018-07-19'),( '2018-07-20')
SELECT TrxDate, IIF( ISNULL( DATEDIFF( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ),TrxDate ),0) > 1
, 0
,ISNULL( DATEDIFF( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ),TrxDate ),0)) AS Nw, NULL AS Conut
FROM @TableDate
| TrxDate | Nw | Count |
|---|---|---|
| 2018-07-13 | 0 | NULL |
| 2018-07-14 | 1 | NULL |
| 2018-07-15 | 1 | NULL |
| 2018-07-17 | 0 | NULL |
| 2018-07-18 | 1 | NULL |
| 2018-07-19 | 1 | NULL |
| 2018-07-20 | 1 | NULL |
I need update Count
| TrxDate | Nw | Conut |
|---|---|---|
| 2018-07-13 | 0 | 0 |
| 2018-07-14 | 1 | 1 |
| 2018-07-15 | 1 | 2 |
| 2018-07-17 | 0 | 0 |
| 2018-07-18 | 1 | 1 |
| 2018-07-19 | 1 | 2 |
| 2018-07-20 | 1 | 3 |
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2018 at 11:09 am
Check the following article:
http://www.sqlservercentral.com/articles/T-SQL/71550/
August 2, 2018 at 12:01 pm
Mike01 - Thursday, August 2, 2018 10:34 AMI found this in anther forum and thought it would be a simple query, but I'm drawing a complete blank on it (maybe thinking of weekend beers, not sure). Now it's piqued my curiosity. Basically trying to have running totals where the dates are within 1 day of each other. If 2 days, then start over at 0.
DECLARE @TableDate TABLE (TrxDate date )
INSERT INTO @TableDate
(
TrxDate
)
VALUES( '2018-07-13'),( '2018-07-14'),( '2018-07-15'),( '2018-07-17'),( '2018-07-18'),( '2018-07-19'),( '2018-07-20')SELECT TrxDate, IIF( ISNULL( DATEDIFF( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ),TrxDate ),0) > 1
, 0
,ISNULL( DATEDIFF( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ),TrxDate ),0)) AS Nw, NULL AS Conut
FROM @TableDate
TrxDate Nw Count 2018-07-13 0 NULL 2018-07-14 1 NULL 2018-07-15 1 NULL 2018-07-17 0 NULL 2018-07-18 1 NULL 2018-07-19 1 NULL 2018-07-20 1 NULL I need update Count
TrxDate Nw Conut 2018-07-13 0 0 2018-07-14 1 1 2018-07-15 1 2 2018-07-17 0 0 2018-07-18 1 1 2018-07-19 1 2 2018-07-20 1 3
This is a classic "Islands" problemDECLARE @TableDate TABLE (TrxDate date PRIMARY KEY CLUSTERED, Value int NOT NULL);
INSERT INTO @TableDate ( TrxDate, Value )
VALUES ('2018-07-13', 50),('2018-07-14', 30),('2018-07-15', 10)
,('2018-07-17', 40),('2018-07-18', 60),('2018-07-19', 80),('2018-07-20', 20)
,('2018-07-23', 70),('2018-07-24', 90),('2018-07-25', 70);
WITH cteData AS (
-- Find the contiguous dates, and Group them together
SELECT TrxDate, DATEADD(day, -1 * DENSE_RANK() OVER(ORDER BY TrxDate), TrxDate) AS Grouper
FROM @TableDate
)
, cteIslands AS (
-- Find the Start/End date of each Island
SELECT Grouper
, MIN(TrxDate) AS IslandStart
, MAX(TrxDate) AS IslandEnd
FROM cteData
GROUP BY Grouper
)
-- Get the running total for each Island
SELECT data.TrxDate, data.Value
, RunTot = SUM(data.Value) OVER (PARTITION BY isl.Grouper
ORDER BY data.TrxDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @TableDate AS data
INNER JOIN cteIslands AS isl ON data.TrxDate between isl.IslandStart and isl.IslandEnd
ORDER BY data.TrxDate;
August 2, 2018 at 12:09 pm
Just for fun, lets add some calculations to the groupsDECLARE @TableDate TABLE (TrxDate date PRIMARY KEY CLUSTERED, Value int NOT NULL);
INSERT INTO @TableDate ( TrxDate, Value )
VALUES ('2018-07-13', 50),('2018-07-14', 30),('2018-07-15', 10)
,('2018-07-17', 40),('2018-07-18', 60),('2018-07-19', 80),('2018-07-20', 20)
,('2018-07-23', 70),('2018-07-24', 90),('2018-07-25', 70);
WITH cteData AS (
-- Find the contiguous dates
SELECT TrxDate, DATEADD(day, -1 * DENSE_RANK() OVER(ORDER BY TrxDate), TrxDate) AS Grouper
FROM @TableDate
)
, cteIslands AS (
-- Find the Start/End date of each Island
SELECT Grouper
, MIN(TrxDate) AS IslandStart
, MAX(TrxDate) AS IslandEnd
FROM cteData
GROUP BY Grouper
)
-- Get the running total for each Island
SELECT data.TrxDate, data.Value
, RunTotal = SUM(data.Value) OVER (PARTITION BY isl.Grouper
ORDER BY data.TrxDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
, GroupTotal = SUM(data.Value) OVER (PARTITION BY isl.Grouper)
, PercentOfGroup = 100.0 * data.Value / SUM(data.Value) OVER (PARTITION BY isl.Grouper)
, RunPercentOfGroup = 100.0 * SUM(data.Value) OVER (PARTITION BY isl.Grouper
ORDER BY data.TrxDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
/ SUM(data.Value) OVER (PARTITION BY isl.Grouper)
FROM @TableDate AS data
INNER JOIN cteIslands AS isl ON data.TrxDate between isl.IslandStart and isl.IslandEnd
ORDER BY data.TrxDate;
August 2, 2018 at 12:37 pm
Damn you Dense_Rank. Should've known Jeff wrote something about this. Thanks for all your help
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply