Ok... here we go.
First, we're going to need an incremental "row source" to help us avoid any RBAR so that we can do all of this using high performance iTVFs (Inline Table Valued Functions). The following function is based on "Tally Table" structures in a modified Itzik Ben-Gan style. If you don't have one of these babies in your databases, it's time you did. As usual, it's heavily documented in the code.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
Now we need something to generate week boundary dates with. Same thing goes here. If you don't have one already, you really do need one. Here's the code.
CREATE FUNCTION dbo.Weeks
/**************************************************************************************************
Purpose:
Given a @pStart date and an End date, generate "ISO" week periods that start on Monday of the week
that contains the @pStart date and ends on the week that contains the @pEND date.
Returns:
WeekNumber = Starts at 1 and increments for each returned week.
WeekStart = The date of the Monday for the week (Will be uses as temporally "closed").
WeekNext = The date of the Monday for the next week (will be used as temporally "open").
Usage:
SELECT WeekNumber, WeekStart, WeekNext
FROM dbo.Weeks(@pStartDate, @pEndDate)
;
Revision History:
Rev 00 - 09 Dec 2015 - Jeff Moden
- Redacted/simplified production model for posting.
**************************************************************************************************/
--===== These are the parameters of this iTVF
(
@pStartDate DATETIME
,@pEndDate DATETIME
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Expand the dates to weeks
WITH
cteParameters AS
(
SELECT StartWeek = DATEADD(dd,DATEDIFF(dd,0,@pStartDate)/7*7,0)
,EndWeek = DATEADD(dd,DATEDIFF(dd,0,@pEndDate )/7*7,0)
)
SELECT WeekNumber = t.N+1
,WeekStart = DATEADD(dd,t.N*7 ,StartWeek)
,WeekNext = DATEADD(dd,t.N*7+7,StartWeek)
FROM cteParameters p
CROSS APPLY dbo.fnTally(0,DATEDIFF(wk,p.StartWeek,p.EndWeek)) t
;
Up next, we need some test data. I normally test with a million rows and this will be no exception. Don't worry... it only takes just several seconds thank to the "pseudo cursor" formed by the constrained CROSS JOIN.
--=================================================================================================
-- Create and populate a test table.
-- It consists of random start dates for 2014/2015 and an end date for up to 366 days later.
-- Nothing in this section is a part of the solution. We're just building test data here.
--=================================================================================================
--===== If the test table exists, drop it to make reruns easier in SSMS
IF OBJECT_ID('tempdb..#Tab1','U') IS NOT NULL
DROP TABLE #Tab1
;
--===== Create and populate the test table on the fly.
WITH
cteGenCreatedDates AS
(
SELECT TOP 1000000
Created = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2014','2016')+CAST('2014' AS DATETIME)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT RowNum = ISNULL(ROW_NUMBER() OVER (ORDER BY Created),0) --ISNULL creates NOT NULL column
,Created = ISNULL(Created,0)
,Closed = ISNULL(RAND(CHECKSUM(NEWID()))*366+Created,0)
INTO #Tab1
FROM cteGenCreatedDates
;
--===== Create the most useful index
CREATE CLUSTERED INDEX IXC_Created ON #Tab1 (Created)
;
Ok... we have our two permanent functions in place and the test data has been built. The functions make this exercise almost child's play. Here's the code to solve your original problem...
SELECT wks.WeekStart
,WeekEnd = DATEADD(dd,-1,wks.WeekNext)
,OpenCount = COUNT(*)
FROM dbo.Weeks('2014-12-09', '2015-01-15') wks
LEFT JOIN #Tab1 dat
ON dat.Created < wks.WeekNext
AND dat.Closed >= wks.WeekStart
GROUP BY wks.WeekStart,wks.WeekNext
;
... and here are the results...
WeekStart WeekEnd OpenCount
----------------------- ----------------------- -----------
2014-12-29 00:00:00.000 2015-01-04 00:00:00.000 260847
2014-12-08 00:00:00.000 2014-12-14 00:00:00.000 259606
2014-12-15 00:00:00.000 2014-12-21 00:00:00.000 260282
2015-01-05 00:00:00.000 2015-01-11 00:00:00.000 260638
2015-01-12 00:00:00.000 2015-01-18 00:00:00.000 260513
2014-12-22 00:00:00.000 2014-12-28 00:00:00.000 260696
(6 row(s) affected)
If you have "Closed" dates that are NULL, you can add in an ISNULL(WeekNext,'9999') to replace "WeekNext" in the WeekEnd formula.
For more information on how the relationships in the ON clause work to solve this problem, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/105968/
I don't have 2012 on any of my boxes yet (stuck in the 2005/2008 worlds for now) to do a nice running total with but, from there, you can start to do some analytics...
WITH ctePreAggregate AS
(
SELECT wks.WeekStart
,WeekEnd = DATEADD(dd,-1,wks.WeekNext)
,OpenCount = COUNT(*)
FROM dbo.Weeks('2014-12-09', '2015-01-15') wks --Parameterize these dates
LEFT JOIN #Tab1 dat
ON dat.Created < wks.WeekNext
AND dat.Closed >= wks.WeekStart
GROUP BY wks.WeekStart,wks.WeekNext
)
SELECT WeekStart = CAST(WeekStart AS DATE)
,WeekEnd = CAST(WeekEnd AS DATE)
,OpenCount
,PercentOfTotal = CAST(OpenCount*100.0/SUM(OpenCount) OVER (PARTITION BY (SELECT NULL)) AS DECIMAL(4,1))
,Total = SUM(OpenCount) OVER (PARTITION BY (SELECT NULL))
FROM ctePreAggregate
ORDER BY WeekStart
;
... to do things like getting what percentage of the total temporal span each week represents for counts.
WeekStart WeekEnd OpenCount PercentOfTotal Total
---------- ---------- ----------- -------------- -----------
2014-12-08 2014-12-14 259606 16.6 1562582
2014-12-15 2014-12-21 260282 16.7 1562582
2014-12-22 2014-12-28 260696 16.7 1562582
2014-12-29 2015-01-04 260847 16.7 1562582
2015-01-05 2015-01-11 260638 16.7 1562582
2015-01-12 2015-01-18 260513 16.7 1562582
(6 row(s) affected)
) would likely beat even that.
