CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Nawaf
Nawaf

Reputation: 540

Segregate the row based on the date time column per each month

I have the following table in sql server database environment.

the format of start date MM/DD/YYYY.

enter image description here

I need the result to be like the following table.

based on start date column the record should segregated to each month in the period between start date and end date

enter image description here

Upvotes: 1

Views: 256

Answers (2)

Nawaf
Nawaf

Reputation: 540

Thank you Gordon Linoff Using CTE I have got the following result enter image description here

My code

WITH cte 
     AS (SELECT 1                          AS id, 
                Cast('2010-01-20' AS DATE) AS trg, 
                Cast('2010-01-20' AS DATE) AS strt_dte, 
                Cast('2010-03-15' AS DATE) AS end_dte 
         UNION ALL 
         SELECT id, 
                Dateadd(day, 1, Eomonth (trg)), 
                strt_dte, 
                end_dte 
         FROM   cte 
         WHERE  Eomonth(trg) < end_dte) 
SELECT id, 
       trg, 
       strt_dte, 
       end_dte, 
       Lead (trg, 1, end_dte) 
         OVER ( 
           partition BY id 
           ORDER BY trg) AS lead_result 
FROM   cte

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can use a recursive CTE:

with cte as (
      select id, startdate as dte, enddate
      from t
      union all
      select id, 
             dateadd(day, 1, eomonth(dte)),
             enddate
      from t
      where eomonth(dte) < enddate
     )
select id, dte,
       lead(dte, 1, enddate) over (partition by id order by dte)
from cte;

Upvotes: 3

Related Questions