How to display a record multiple times including d

问题:

This is the question I inspired of: How to get all the monthly intervals between two dates?

The QA asks the question there for Oracle, I liked the question and wanted to do it in SQL Server 2008.

Let me rephrase the question. Assume we have data like:

Event   StartDate    EndDate
A       2018-02-07   2018-04-22

Then the expected output will be:

Event   Date
A       7 to 28
A       1 to 31
A       1 to 22

Basicly, the output should show the day intervals between the EndDate and the StartDate. I handled this problem with the query below, works very well, but I wonder if there is any better ways to deal with this issue. Any advice would be appreciated!.

create table #temptable
([Event] varchar(5), dateinterval varchar(30))

declare @startDate date = '2018-02-07'
declare @endDate date = '2018-04-22'
declare @yeardiff int = (select DATEDIFF(month,@startDate,@endDate))

declare @counter int = 0
declare @tempDate date, @lastDateOfMonth date
declare @dateInterval varchar(100)

while @counter <= @yeardiff
begin
   set @tempDate = @startDate
   set @lastDateOfMonth = DATEADD(month, ((YEAR(@startDate) - 1900) * 12) + MONTH(@startDate), -1)

   if @counter = @yeardiff
   begin
      set @dateInterval = cast(DAY(@startDate) as varchar(2)) + ' to ' + cast(DAY(@endDate) as varchar(2))
   end
   else
   begin
      set @dateInterval = cast(DAY(@startDate) as varchar(2)) + ' to ' + cast(DAY(@lastDateOfMonth) as varchar(2))
   end

   insert into #temptable 
   values ('B',@dateInterval)

   set @startDate = DATEADD(m, DATEDIFF(m, -1, @startDate), 0)
   set @counter = @counter + 1
end

select * from #temptable

回答1:

This is easier in SQL Server because you can use a recursive CTE. (Actually, you have these in Oracle 12C as well, so the same approach works.)

with CTE as (
      select event, startdate, enddate,
             dateadd(day, 1 - day(startdate), startdate) as month_start
      from t
      union all
      select event, startdate, enddate,
             dateadd(month, 1, month_start)
      from cte
      while month_start <= enddate
    )
select event, month_start,
       ((case when eomonth(enddate) = eomonth(month_start) -- last month
              then day(enddate)
              else day(eomonth(month_start))
         end) -
        (case when month_start < startdate  -- first month
              then day(startdate) - 1
              else 0
         end)
       ) as days_in_month
from cte;

This expands the date range by month for each event. It then calculates the number of days in the month.

By default, this will work for up to 100 months. You can use the maxrecursion option if you need support for more months.