CODEWITHSUNDEEP
sqlsql-serversql-server-2008
dpluscc
dpluscc

Reputation: 600

Consecutive / cumulative dates in SQL Server

SQL Server 2008: I'm having a tough time translating my pseudo SQL to actual execution. In short, I have a data set that has:

id, startdate, enddate

so, for example:

1,1/1/2010,2/1/2010
1,3/1/2010,3/15/2010
2,4/1/2010,6/1/2010
2,5/1/2010,5/15/2010
2,7/1/2010,7/15/2010

Where each ID could be listed multiple times and the dates could overlap.

I need to:

a) Get the consecutive days for each ID. So, for example:

1 = 1/1/2010 thru 2/1/2010 + 3/1/2010 through 3/15/2010 = 43
2 = 4/1/2010 thru 6/1/2010 + 7/1/2010 through 7/15/2010 (note: the 5/1/10 thru 5/15 was omitted because it overlapped)

My initial thought was to write a sql query that would do this:

Date, ID, Active

I'm pulling data for an entire year, so I would have a date/ID entry for each ID in the table. The "active" would be a 1 or 0 depending on whether that ID was "on" for that particular day.

Then I could group that up and get my "distinct" cumulative for the year.

Problem is, that will create a table with over 300,000,000 million records, and I just can't imagine there isn't a better way to do this.

Any advice would be greatly appreciated.

Upvotes: 0

Views: 689

Answers (2)

Cade Roux
Cade Roux

Reputation: 89741

https://data.stackexchange.com/stackoverflow/q/109335/

DECLARE @tbl AS TABLE (id INT, startdate DATETIME, enddate DATETIME);
INSERT INTO @tbl VALUES
(1,'1/1/2010','2/1/2010')
,(1,'3/1/2010','3/15/2010')
,(2,'4/1/2010','6/1/2010')
,(2,'5/1/2010','5/15/2010')
,(2,'7/1/2010','7/15/2010');

WITH alldates AS (
    -- Adjust start date and number of days
    SELECT TOP 100000 DATEADD(d, ROW_NUMBER() OVER(ORDER BY ac1.object_id) - 1, '1/1/2010') AS dt
    FROM master.sys.all_columns ac1
    CROSS JOIN master.sys.all_columns ac2
)
SELECT id, COUNT(DISTINCT alldates.dt)
FROM alldates
INNER JOIN @tbl AS period
ON alldates.dt BETWEEN period.startdate AND period.enddate
GROUP BY id;

Upvotes: 1

Code Magician
Code Magician

Reputation: 24032

If I understand your question correctly, this will give you your results 

/*setup data*/
CREATE TABLE #dates 
(
        id INT,
        startdate DATETIME,
        enddate DATETIME
)

INSERT INTO #dates 
SELECT 1,'1/1/2010','2/1/2010'
UNION ALL 
SELECT 1,'3/1/2010','3/15/2010'
UNION ALL 
SELECT 2,'4/1/2010','6/1/2010'
UNION ALL 
SELECT 2,'5/1/2010','5/15/2010'
UNION ALL 
SELECT 2,'7/1/2010','7/15/2010'


/* this is our "tally-table" maybe make this static*/
CREATE TABLE #numbers 
(
    NUM INT PRIMARY KEY CLUSTERED 
)

;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 500 )
    INSERT INTO #numbers 
SELECT n FROM Nbrs
    OPTION ( MAXRECURSION 500 )

/*first we get our full range*/
;WITH fullrange
AS
(
    SELECT D.id, DATEADD(dd,N.num-1,D.startdate) AS dte
    FROM #dates D
        INNER JOIN #numbers N 
            ON N.num <= DATEDIFF(dd,D.startdate, D.enddate)
            /*By joining to the numbers/tally table we can extrapolate the full range of dates like you alluded to in your considered approach*/
)
/*then we aggregate*/
SELECT id, COUNT(DISTINCT dte) AS active --Now we can just count distinct dates for each id
FROM fullrange 
GROUP BY id

Upvotes: 1

Related Questions