Find min value and max value from two columns. Generate date from min to max

Question

I have table in 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

I need to make some T-SQL query that will return:

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

Amount of entries in result set equals amount of months between min value from both 'Open_date' and 'Close_date' columns of table from DB and max value from same columns. The problem is how to find min and max value from two date columns, then generate dates from min to max and then use this temporary table(or what it would be) to count number of open and closed entries for every date from temporary table.

Answer 1

Another version

;with T(monthstart, monthend, _tempmax) as (
    select 
        min(Open_date), dateadd(day, -1, dateadd(month, 1, min(Open_date))), dateadd(month, 1, max(Open_date)) as _tempmax from THETABLE
    union all
    select 
        dateadd(month, 1, monthstart), dateadd(day, -1, dateadd(month, 2, monthstart)), _tempmax
    from T
    where dateadd(month, 1, monthstart) <= _tempmax
)
select
    month(monthstart) as [month],
    year(monthstart) as [year],
    sum(case when Open_date between monthstart and monthend then 1 else 0 end),
    sum(case when Close_date between monthstart and monthend then 1 else 0 end)
from T
left join 
    THETABLE on (Open_date between monthstart and monthend) or (Close_date between monthstart and monthend)
group by
    month(monthstart),
    year(monthstart)

Answer 2

For SQL Server 2005+, you can try a

somewhat simpler approach. Try this:

WITH CTE AS
(
    SELECT  MIN(CONVERT(VARCHAR(6),Dates,112)) YearMonth, 
            MAX(CONVERT(VARCHAR(6),Dates,112)) MaxDate
    FROM (  SELECT Open_Date Dates FROM YourTable
            UNION 
            SELECT Close_Date FROM YourTable) A
    UNION ALL
    SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,1,YearMonth+'01'),112), MaxDate
    FROM CTE
    WHERE CONVERT(VARCHAR(6),DATEADD(MONTH,1,YearMonth+'01'),112)<=MaxDate
)
SELECT  RIGHT(A.YearMonth,2) [Month], LEFT(A.YearMonth,4) [Year], 
        COUNT(B.Id) [Open], COUNT(C.Id) [Close]
FROM CTE A
LEFT JOIN YourTable B
ON A.YearMonth = CONVERT(VARCHAR(6),B.Open_Date,112) 
LEFT JOIN YourTable C
ON A.YearMonth = CONVERT(VARCHAR(6),C.Close_Date,112) 
GROUP BY RIGHT(A.YearMonth,2), LEFT(A.YearMonth,4)
ORDER BY LEFT(A.YearMonth,4), RIGHT(A.YearMonth,2)

Answer 3

I worked up an example earlier and ended up not posting it, but came back and decided to post it anyway :)

-- 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;

The dates for the sample data have been changed to reflect yyyymmdd. I also used a local Number table:

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 ;

I almost didn't post this, as Norla got it done earlier before I finished this one, but I noticed Norla's solution populates the Close column if a start date was closed (and for the month of that start date) while this version populates the close month column on the month of the close_date, which I believe is what you were asking for.

Answer 4

I changed the dates a bit, since I'm using a different date system, but this should work. Accept if it does, please!

DECLARE @TABLE1 TABLE
(
    ID INT
    , Open_date DATETIME
    , Close_date DATETIME
)

INSERT INTO @TABLE1 (ID, Open_date, Close_date)
      SELECT 1, '01-01-2010', '03-01-2010'
UNION SELECT 2, '01-21-2011', '02-12-2011'
UNION SELECT 3, '03-01-2010', NULL
UNION SELECT 4, '01-10-2010', NULL


DECLARE @MIN_DATE DATETIME
DECLARE @MAX_DATE DATETIME

SELECT @MIN_DATE = MIN(d)
    , @MAX_DATE = MAX(d)
FROM
(
    SELECT Open_date AS d
    FROM @TABLE1

        UNION

    SELECT Close_date AS d
    FROM @TABLE1
)a

--SELECT @MIN_DATE, @MAX_DATE



DECLARE @DATES TABLE
(
    ID INT IDENTITY(1,1)
    , [date] DATETIME
)

DECLARE @DATE DATETIME
SET @DATE = @MIN_DATE

WHILE (@DATE <= @MAX_DATE)
BEGIN

    INSERT INTO @DATES ([date])
    VALUES (@DATE)

    SET @DATE = @DATE + 1

END

--SELECT *
--FROM @DATES

SELECT MONTH(a.[date]) AS [Month]
    , YEAR(a.[date]) AS [Year]
    , SUM(CASE WHEN b.Open_date IS NOT NULL THEN 1 ELSE 0 END) AS [Open]
    , SUM(CASE WHEN b.Close_date IS NOT NULL THEN 1 ELSE 0 END) AS [Close]
FROM @DATES a
LEFT JOIN @TABLE1 b
    ON a.[date] = b.Open_date
GROUP BY YEAR(a.[date]), MONTH(a.[date])
ORDER BY YEAR(a.[date]), MONTH(a.[date])