CODEWITHSUNDEEP
sqlsql-server
kajal
kajal

Reputation: 1

I want to split a row into multiple row based on month and year of the date in SQL Server

For example- I have below rows in my table:

id  StartDate  EndDate
101 1/03/2017  15/03/2017
102 27/03/2017 10/04/2017
103 25/12/2017 5/02/2018

I want the following output: 

id  month year 
101 03    2017 
102 03    2017
102 04    2017
103 12    2017     
103 01    2018
103 02   2018

I have tried my best to find a solution but couldn't get through it. Any kind of help is always appreciated.

Thanks in advance.

Upvotes: 0

Views: 75

Answers (2)

Gaurav Rajput
Gaurav Rajput

Reputation: 647

If i correctly understood your problem then below query will work for you, this is giving the exact output required by you :

DECLARE @SAMPLE_DATA TABLE(id INT,  StartDate DATETIME,  EndDate DATETIME)
INSERT INTO @SAMPLE_DATA VALUES
(101, '03/1/2017', '03/15/2017'),
(102, '03/27/2017', '04/10/2017'),
(103, '12/25/2017', '02/5/2018')

;WITH SAMPLE_DATA
AS
(
    SELECT ID,StartDate FROM @SAMPLE_DATA
    UNION ALL
    SELECT S1.id,DATEADD(D,1,S.STARTDATE) FROM SAMPLE_DATA S JOIN     @SAMPLE_DATA S1 ON S.id=S1.id WHERE 
    DATEADD(D,1,S.STARTDATE)<=S1.EndDate
)
SELECT DISTINCT ID,MONTH(StartDate)[MONTH],YEAR(StartDate)[YEAR] FROM     SAMPLE_DATA ORDER BY ID,YEAR,MONTH

Output of query :

-------------------
ID  MONTH   YEAR
-------------------
101 3   2017
102 3   2017
102 4   2017
103 12  2017
103 1   2018
103 2   2018
-------------------

Upvotes: 1

TriV
TriV

Reputation: 5148

You could try it with CTE

DECLARE @SampleDate AS TABLE (
Id int, StartDate date, EndDate date
)

INSERT INTO @SampleDate
(
   Id,
   StartDate,
   EndDate
)
VALUES (101, '2017-03-01', '2017-03-15') , 
(102, '2017-03-27', '2017-04-10'), 
(103, '2017-12-25', '2018-02-05')

DECLARE @MinDate date = '2017-01-01'
DECLARE @MaxDate date = '2020-01-01'

;WITH temp AS
(
   SELECT @MinDate AS StartMonth, EOMOnth(@MinDate) AS EndMonth 

   UNION ALL

   SELECT Dateadd(month, 1, t.StartMonth), Dateadd(month, 1, t.EndMonth) AS CurrentDate
   FROM temp t
   WHERE t.EndMonth < @MaxDate
)
SELECT DISTINCT sd.Id, DATEPART(Month,t.StartMonth) AS Month, 
       DATEPART(Year,t.StartMonth) AS Year 
FROM temp t 
INNER JOIN @SampleDate sd ON t.StartMonth BETWEEN sd.StartDate AND sd.EndDate 
                    OR t.EndMonth BETWEEN sd.StartDate AND sd.EndDate
OPTION (MAXRECURSION 0)

Demo link: RexTester

Upvotes: 0

Related Questions