一千萬個為什麽

搜索

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

我在SQL Server DB中有表:

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

我需要做一些將返回的T-SQL查詢:

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

結果集中的條目數等於來自DB的表的“Open_date”和“Close_date”列的最小值與來自相同列的最大值之間的月數。問題是如何從兩個日期列中查找最小值和最大值,然後生成從最小值到最大值的日期,然後使用此臨時表(或它將是什麽)來計算臨時表中每個日期的打開和關閉條目的數量。

最佳答案

我之前做了一個例子並最終沒有發布它,但回來後決定發布它反正:)

-- 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 (
    SELECT DATEADD(MONTH, n-1, @Min),
            DATEADD(MONTH, n, @Min)
    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;

樣本數據的日期已更改,以反映yyyymmdd。我還使用了本地Number表:

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 ;

我幾乎沒有發布這個,因為Norla在我完成這個之前完成了它,但我註意到Norla的解決方案填充了關閉列,如果開始日期已經關閉(以及該開始日期的月份),而此版本填充了close_date月份的關閉月份列,我相信這是你要求的。

轉載註明原文: 從兩列中查找最小值和最大值。生成從最小到最大的日期