declare @DateTable table ( Date date, Period int ) inser...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

declare @DateTable table
(
  Date date,
  Period int
)

insert into @DateTable values
(getdate()-2, 1),
(getdate()-1, 1),
(getdate()+0, 2),
(getdate()+1, 2),
(getdate()+2, 2),
(getdate()+3, 2),
(getdate()+4, 2),
(getdate()+5, 2),
(getdate()+6, 2),
(getdate()+7, 2),
(getdate()+8, 2),
(getdate()+9, 3);


with CStart as
(
  select Date,
         Period
  from @DateTable
  where Date = cast(getdate() as date)
  union all
  select D.Date,
         D.Period
  from @DateTable as D
    inner join CStart
      on dateadd(day, -1, CStart.Date) = D.Date and
         CStart.Period = D.Period
),
CEnd as
(
  select Date,
         Period
  from @DateTable
  where Date = cast(getdate() as date)
  union all
  select D.Date,
         D.Period
  from @DateTable as D
    inner join CEnd
      on dateadd(day, 1, CEnd.Date) = D.Date and
         CEnd.Period = D.Period
),
CPeriod as
(
  select Period
  from @DateTable
  where Date = cast(getdate() as date)
)
select Period,
       (select min(Date) from CStart) as StartDate,
       (select max(Date) from CEnd) as EndDate 
from CPeriod

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached