0

I have this table:

PRJ_ID | START      | FINISH
xxxx   | 2015-01-01 | 2016-01-01

I have two separates date start_range and end_range.

I would like to produce the following output using a stored procedure in SQL Server 2008 for example with this parameters start_range=2015-02-01 and end_range=2016-08-01:

PRJ_ID |   DATE     | STATUS
xxxx   | 2015-02-01 | ACTIVE
xxxx   | 2015-03-01 | ACTIVE
xxxx   | 2015-04-01 | ACTIVE
xxxx   | 2015-05-01 | ACTIVE
.
.
.
xxxx   | 2016-08-01 | NOT ACTIVE

Where the DATE field should be the first day of the month for each month from the start_range to the end_range

2

Having this parameters start_range=2015-02-01 and end_range=2016-08-01, we can generate a list of dates, recursively (see ;WITH cteDates). This part could be replaced with a calendar table , if you have it.

DECLARE @dStart_Range AS DATE = '2015-02-01',
        @dEnd_Range AS DATE = '2016-08-01'

DECLARE @tPRJ AS TABLE
    (   
        PRJ_ID CHAR(4) NOT NULL,
        START DATE NOT NULL,
        FINISH DATE NOT NULL,
        PRIMARY KEY CLUSTERED (PRJ_ID)
    );
INSERT INTO @tPRJ(PRJ_ID,START,FINISH)
VALUES('xxxx','2015-01-01','2016-01-01');


-- generate the list of dates
-- could be replaced by a calendar table if exists
;WITH cteDates
AS(
    SELECT @dStart_Range AS aDates
    UNION ALL
    SELECT DATEADD(MONTH,1,aDates)
    FROM cteDates
    WHERE   
        aDates < @dEnd_Range
)
--SELECT * FROM cteDates

SELECT
    P.PRJ_ID AS [PRJ_ID],
    D.aDates AS [DATE],
    CASE WHEN P.START <= D.aDates AND P.FINISH>= D.aDates THEN 'ACTIVE'
         ELSE 'NOT ACTIVE' END AS [STATUS]
FROM    
    cteDates AS D
    CROSS JOIN  @tPRJ AS P 

And the result is:

PRJ_ID DATE       STATUS
xxxx   2015-02-01 ACTIVE
xxxx   2015-03-01 ACTIVE
xxxx   2015-04-01 ACTIVE
xxxx   2015-05-01 ACTIVE
xxxx   2015-06-01 ACTIVE
xxxx   2015-07-01 ACTIVE
xxxx   2015-08-01 ACTIVE
xxxx   2015-09-01 ACTIVE
xxxx   2015-10-01 ACTIVE
xxxx   2015-11-01 ACTIVE
xxxx   2015-12-01 ACTIVE
xxxx   2016-01-01 ACTIVE
xxxx   2016-02-01 NOT ACTIVE
xxxx   2016-03-01 NOT ACTIVE
xxxx   2016-04-01 NOT ACTIVE
xxxx   2016-05-01 NOT ACTIVE
xxxx   2016-06-01 NOT ACTIVE
xxxx   2016-07-01 NOT ACTIVE
xxxx   2016-08-01 NOT ACTIVE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.