從兩列中查找最小值和最大值。生成從最小到最大的日期

ID    Open_date     Close_date
1     01-01-2010    01-03-2010
2     21-01-2011    12-02-2011
3     01-03-2010    NULL
4     10-01-2010    NULL


Month    Year    Open    Close
01       2010    2       0
02       2010    0       0
03       2010    0       1
04       2010    0       0
05       2010    0       0
06       2010    0       0
07       2010    0       0
08       2010    0       0
09       2010    0       0
10       2010    0       0
11       2010    0       0
12       2010    0       0
01       2011    1       0
02       2011    0       1


最佳答案

-- Sample Data
CREATE TABLE #SampleVals ( ID int, Open_Date Date, Close_Date Date);
INSERT INTO #SampleVals(ID, Open_Date, Close_Date)
VALUES(1,'20100101','20100301'),
(2,'20110121','20110212'),
(3,'20100301', NULL),
(4,'20100110',NULL);

-- Get Start/End for full date range
DECLARE @Min Date, @Max Date;
SELECT @Min = DateAdd(dd,-1 * Day(MIN(Open_Date)) + 1, MIN(Open_Date)),
@Max = MAX(Close_Date)
FROM #SampleVals;

-- Query for values across entire range
WITH DateRange (StartDate,NextDate) AS (
FROM dbo.Number N
WHERE N.n <= DATEDIFF(MONTH,@Min,@Max) + 1
)
SELECT MONTH(DR.StartDate),
YEAR(DR.StartDate),
SUM(CASE WHEN S.Open_Date >= DR.StartDate Then 1 Else 0 END) AS [Open],
SUM(CASE WHEN S.Close_Date < DR.NextDate Then 1 Else 0 END) AS [Closed]
FROM DateRange DR
LEFT JOIN #SampleVals S ON S.Open_Date < DR.NextDate
AND (S.Close_Date >= DR.StartDate OR S.Close_Date IS NULL)
GROUP BY DR.StartDate
ORDER BY DR.StartDate;

-- Cleanup sample data
DROP TABLE #SampleVals;


CREATE TABLE dbo.Number(n INT NOT NULL IDENTITY) ;
GO
SET NOCOUNT ON ;
INSERT dbo.Number DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 5000
INSERT dbo.Number DEFAULT VALUES ;