SQL grouping and running total of open items for a date range

I have a 开发者_高级运维table of items that, for sake of simplicity, contains the ItemID, the StartDate, and the EndDate for a list of items.

ItemID     StartDate     EndDate
1          1/1/2011      1/15/2011
2          1/2/2011      1/14/2011
3          1/5/2011      1/17/2011
...

My goal is to be able to join this table to a table with a sequential list of dates, and say both how many items are open on a particular date, and also how many items are cumulatively open.

Date      ItemsOpened     CumulativeItemsOpen
1/1/2011  1               1
1/2/2011  1               2
...

I can see how this would be done with a WHILE loop, but that has performance implications. I'm wondering how this could be done with a set-based approach?

SELECT COUNT(CASE WHEN d.CheckDate = i.StartDate THEN 1 ELSE NULL END)
         AS ItemsOpened
     , COUNT(i.StartDate)
         AS ItemsOpenedCumulative
FROM Dates AS d
  LEFT JOIN Items AS i
    ON d.CheckDate BETWEEN i.StartDate AND i.EndDate
GROUP BY d.CheckDate

This may give you what you want

SELECT DATE, 
    SUM(ItemOpened) AS ItemsOpened, 
    COUNT(StartDate) AS ItemsOpenedCumulative
FROM
    (
    SELECT d.Date, i.startdate, i.enddate,
        CASE WHEN i.StartDate = d.Date THEN 1 ELSE 0 END AS ItemOpened
    FROM Dates d
    LEFT OUTER JOIN Items i ON d.Date BETWEEN i.StartDate AND i.EndDate
    ) AS x
GROUP BY DATE
ORDER BY DATE

This assumes that your date values are DATE data type. Or, the dates are DATETIME with no time values.

You may find this useful. The recusive part can be replaced with a table. To demonstrate it works I had to populate some sort of date table. As you can see, the actual sql is short and simple.

DECLARE @i table (itemid INT, startdate DATE, enddate DATE)

INSERT @i VALUES (1,'1/1/2011', '1/15/2011') 
INSERT @i VALUES (2,'1/2/2011', '1/14/2011')
INSERT @i VALUES (3,'1/5/2011', '1/17/2011') 

DECLARE @from DATE
DECLARE @to DATE
SET @from = '1/1/2011'
SET @to = '1/18/2011'

-- the recusive sql is strictly to make a datelist between @from and @to
;WITH cte(Date) 
AS ( 
SELECT @from DATE 
UNION ALL 
SELECT DATEADD(day, 1, DATE) 
FROM cte ch     
WHERE DATE < @to 
) 
SELECT cte.Date, sum(case when cte.Date=i.startdate then 1 else 0 end) ItemsOpened, count(i.itemid) ItemsOpenedCumulative 
FROM cte 
left join @i i on cte.Date between i.startdate and i.enddate
GROUP BY cte.Date
OPTION( MAXRECURSION 0)

If you are on SQL Server 2005+, you could use a recursive CTE to obtain running totals, with the additional help of the ranking function ROW_NUMBER(), like this:

WITH grouped AS (
  SELECT
    d.Date,
    ItemsOpened = COUNT(i.ItemID),
    rn = ROW_NUMBER() OVER (ORDER BY d.Date)
  FROM Dates d
    LEFT JOIN Items i ON d.Date BETWEEN i.StartDate AND i.EndDate
  GROUP BY d.Date
  WHERE d.Date BETWEEN @FilterStartDate AND @FilterEndDate
),
cumulative AS (
  SELECT
    Date,
    ItemsOpened,
    ItemsOpenedCumulative = ItemsOpened
  FROM grouped
  WHERE rn = 1
  UNION ALL
  SELECT
    g.Date,
    g.ItemsOpened,
    ItemsOpenedCumulative = g.ItemsOpenedCumulative + c.ItemsOpened
  FROM grouped g
    INNER JOIN cumulative c ON g.Date = DATEADD(day, 1, c.Date)
)
SELECT *
FROM cumulative