Solved

SQL Server running total

Posted on 2011-09-24
8
355 Views
Last Modified: 2012-05-12
Hello experts,
I have a talbe called cases.  Sample of this table looks like this:
caseID            startDate                                     endDate
143            2011-07-01 12:20:12.317      2011-07-06 09:29:11.750
222            2011-07-01 12:57:17.490      2011-07-03 10:58:04.523
323            2011-07-01 14:31:50.290      2011-07-02 14:35:10.063
491            2011-07-01 15:30:16.060      2011-07-07 15:10:09.483
505            2011-07-01 16:29:14.030      2011-07-01 17:05:08.231

I'd like to know the easiest way for me to calculate a running total by day from this table.  I'm thinking I would first need to take the min(startDate) and max(endDate) to figure out my range of days, but aside from that I'm not sure what should be done next.

If someone can provide a udf to do this that would be great.  If not, the tsql code would be ok too.  For the sample data above, this is what I'd like the running total to look like:

 date                  running_total
07/01/2011                  5
07/02/2011                  4
07/03/2011                  3
07/04/2011                  2
07/05/2011                  2
07/06/2011                  2
07/07/2011                  1
07/08/2011                  0

Please let me know if I haven't explained this well.  I can provide more details if needed.  
Thanks in advance for your help.
0
Comment
Question by:w00k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593736
w00k,

If i am understanding your display correctly, that does not appear to be a running total at all. It appears to be a simple COUNT() of the number of rows for a given day regardless of timestamp, is that correct? Or is it how many rows are still active over time, so on 7/1 all 5, then 7/2 only 4, and so on based on an explosion of rows based on range denoted by startDate and endDate?

Kevin
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593748
What is the biggest gap in start and end dates possible? Just trying to determine the boundaries we may have for certain solutions that have limitations.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36593756
Try something like this:

DECLARE @mindate datetime, @maxdate datetime

SET @mindate = (SELECT MIN(startDate) FROM cases)
SET @mindate = DATEADD(day, DATEDIFF(day, 0, @mindate), 0)
SET @maxdate = (SELECT MAX(endDate) FROM cases)
SET @maxdate = DATEADD(day, DATEDIFF(day, 0, @maxdate), 0)

SELECT DATEADD(day, DATEDIFF(day, 0, startDate), 0) AS startDate, 
    DATEADD(day, DATEDIFF(day, 0, andDate), 0) AS endDate
INTO #cases
FROM cases

CREATE TABLE #dates (dt datetime)

WHILE @mindate <= @maxdate BEGIN
    INSERT INTO #dates (dt) VALUES (@mindate)
    SET @mindate = DATEADD(day, 1, @mindate)
END

SELECT d.dt, COUNT(c.startDate) AS Cnt
FROM #dates d LEFT JOIN
    #cases c ON d.dt >= c.startDate AND d.dt <= c.endDate
GROUP BY d.dt
ORDER BY d.dt

DROP TABLE #cases
DROP TABLE #dates

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36593775
Another version, assuming you already have a "calendar table" of all the dates for which you want output in the query:


SELECT DATEADD(day, DATEDIFF(day, 0, startDate), 0) AS startDate, 
    DATEADD(day, DATEDIFF(day, 0, andDate), 0) AS endDate
INTO #cases
FROM cases

SELECT r.dt, COUNT(c.startDate) AS Cnt
FROM tblCalendar r LEFT JOIN
    #cases c ON r.dt >= c.startDate AND r.dt <= c.endDate
GROUP BY r.dt
ORDER BY r.dt

DROP TABLE #cases

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593859
It appears Patrick was thinking the same thing. I was asking about range to see if recursion or use of built in numbers tables like master..spt_values may come in handy if you did not have your own calendar table already. Here is what the recursion would look like. It has its limits thought if your ranges can be large. For the simple case in the question it works like this:
;WITH cte(caseID, dt) AS
(
/* Get all the start dates; recursive anchor. */
SELECT caseID, CONVERT(DATETIME, DATEDIFF(day, 0, startDate))
FROM your_table

UNION ALL 

/* Recursive query. */
SELECT c.caseID, DATEADD(day, 1, p.dt)
FROM your_table c
JOIN cte p ON p.caseID = c.caseID 
WHERE c.endDate >= DATEADD(day, 1, p.dt)
)
SELECT dt as [date], COUNT(caseID) AS [running_total]
FROM cte
GROUP BY dt
;

Open in new window

0
 

Author Comment

by:w00k
ID: 36593921
Thanks for the quick reply guys.  I'll give both of these a try as soon as I get home...give me about an hour.
Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593956
Cool. By the way, I think Patrick's already handles this properly, but the CTE approach I showed above does not add in the extra day 7/8 with 0.

You can either:
;WITH cte(caseID, dt) AS
(
/* Get all the start dates; recursive anchor. */
SELECT caseID, CONVERT(DATETIME, DATEDIFF(day, 0, startDate))
FROM your_table

UNION ALL 

/* Recursive query. */
SELECT c.caseID, DATEADD(day, 1, p.dt)
FROM your_table c
JOIN cte p ON p.caseID = c.caseID 
WHERE c.endDate >= DATEADD(day, 1, p.dt)
)
SELECT dt as [date], COUNT(caseID) AS [running_total]
FROM cte
GROUP BY dt
/* Add in final zero row. */
UNION SELECT DATEADD(day, 1, MAX(dt)), 0 FROM cte
;

Open in new window


Or alter the CTE:
;WITH cte(caseID, dt, dt_end) AS
(
/* Get all the start dates; recursive anchor. */
SELECT caseID, CONVERT(DATETIME, DATEDIFF(day, 0, startDate)), endDate
FROM your_table

UNION ALL 

/* Recursive query. */
SELECT c.caseID, DATEADD(day, 1, p.dt), c.endDate
FROM your_table c
JOIN cte p ON p.caseID = c.caseID 
WHERE c.endDate >= p.dt
)
SELECT dt as [date]
     , COUNT(CASE WHEN dt_end >= dt THEN caseID END) AS [running_total]
FROM cte
GROUP BY dt
;

Open in new window

0
 

Author Closing Comment

by:w00k
ID: 36594079
Thanks you for your help Matthewspatrick.  Worked perfectly.  I already had a calendar table so I was able to use your solution without any problems.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question