Data Logic

Transact-SQL
1

Hi Team,

Can anyone help on getting below output dynamically based on Start and End Date value

Declare @StartDate datetime,@EndDate Datetime

set @StartDate = '2018-10-01 00:00:00.000'
set @EndDate = '2020-12-31 00:00:00.000'

Requirment : 5 months lag will rolling 12 months value should be repeated for each quarter
output should be :

Runmonth ServiceBeginDate ServiceEndDate
201810 6/1/2017 5/31/2018
201811 6/1/2017 5/31/2018
201812 6/1/2017 5/31/2018
201901 9/1/2017 8/31/2018
201902 9/1/2017 8/31/2018
201903 9/1/2017 8/31/2018
201904 12/1/2017 11/30/2018
201905 12/1/2017 11/30/2018
201906 12/1/2017 11/30/2018
201907 3/1/2018 2/28/2019
201908 3/1/2018 2/28/2019
201909 3/1/2018 2/28/2019
201910 6/1/2018 5/31/2019
201911 6/1/2018 5/31/2019
201912 6/1/2018 5/31/2019
202001 9/1/2018 8/31/2019
202002 9/1/2018 8/31/2019
202003 9/1/2018 8/31/2019
202004 12/1/2018 11/30/2019
202005 12/1/2018 11/30/2019
202006 12/1/2018 11/30/2019
202007 3/1/2019 2/28/2020
202008 3/1/2019 2/28/2020
202009 3/1/2019 2/28/2020
202010 6/1/2019 5/31/2020
202011 6/1/2019 5/31/2020
202012 6/1/2019 5/31/2020
2
DECLARE @StartDate DATETIME,
        @EndDate   DATETIME

SET @StartDate = '2018-10-02 00:00:00.000'
SET @EndDate = '2020-12-31 00:00:00.000';

WITH cte
     AS (SELECT @StartDate
                   AS
                    FromDate,
                CONVERT(VARCHAR(6), @StartDate, 112)
                    AS Runmonth,
                Dateadd(month, -16, Dateadd(quarter, Datediff(quarter, 0,
                                                     @StartDate)
                                    , 0
                                    )
                    ) AS
                ServiceBeginDate
         UNION ALL
         SELECT Dateadd(month, 1, fromdate),
                CONVERT(VARCHAR(6), Dateadd(month, 1, fromdate), 112),
                Dateadd(month, -16, Dateadd(quarter, Datediff(quarter, 0,
                                                     Dateadd(month, 1, fromdate)
                                                     ), 0)
                )
         FROM   cte
         WHERE  Dateadd(month, 1, fromdate) <= @EndDate)
SELECT runmonth,
       servicebegindate,
       Dateadd(dd, -1, Dateadd(year, 1, servicebegindate))
FROM   cte; 
1 Like
3

Here is a method that does not use a recursive CTE:

Declare @startDate date = '2018-10-01'
      , @endDate date = '2020-12-31';

 Select RunDate = convert(char(6), dateadd(month, n.n, @startDate), 112)
      , pd.ServiceStartDate
      , ServiceEndDate = dateadd(day, -1, dateadd(year, 1, pd.ServiceStartDate))
   From (Select n = row_number() over(Order By @@spid) - 1 From sys.all_columns ac) n(n)
  Cross Apply (
       Values (4, datefromparts(year(dateadd(month, n.n, @startDate)) - 1, 6, 1))
            , (1, datefromparts(year(dateadd(month, n.n, @startDate)) - 2, 9, 1))
            , (2, datefromparts(year(dateadd(month, n.n, @startDate)) - 2, 12, 1))
            , (3, datefromparts(year(dateadd(month, n.n, @startDate)) - 1, 3, 1))
              )                 pd(PeriodQuarter, ServiceStartDate)
  Where dateadd(month, n.n, @startDate) < @endDate
    And datepart(quarter, dateadd(month, n.n, @startDate)) = pd.PeriodQuarter;
4

Here is another way:

Declare @startDate date = '2018-10-01'
      , @endDate date = '2020-12-31';

 Select RunDate = convert(char(6), dateadd(month, n.n, @startDate), 112)
      , s.ServiceStartDate
      , e.ServiceEndDate
   From (Select n = row_number() over(Order By @@spid) - 1 From sys.all_columns ac) n(n)
  Cross Apply (Values (4, 6, 1), (1, 9, 2), (2, 12, 2), (3, 3, 1)) pd(q, m, i)
  Cross Apply (Values (datefromparts(year(dateadd(month, n.n, @startDate)) - pd.i, pd.m, 1))) s(ServiceStartDate)
  Cross Apply (Values (dateadd(day, -1, dateadd(year, 1, s.ServiceStartDate)))) e(ServiceEndDate)
  Where dateadd(month, n.n, @startDate) < @endDate
    And datepart(quarter, dateadd(month, n.n, @startDate)) = pd.q;

Or - you can do this:

Declare @startDate date = '2018-10-01'
      , @endDate date = '2020-12-31';

 Select r.RunDate
      , s.ServiceStartDate
      , e.ServiceEndDate
   From (Select n = row_number() over(Order By @@spid) - 1 From sys.all_columns ac) n(n)
  Cross Apply (Values (4, 6, 1), (1, 9, 2), (2, 12, 2), (3, 3, 1))                              pd(q, m, i)
  Cross Apply (Values (convert(char(6), dateadd(month, n.n, @startDate), 112)))                  r(RunDate)
  Cross Apply (Values (datefromparts(year(dateadd(month, n.n, @startDate)) - pd.i, pd.m, 1)))    s(ServiceStartDate)
  Cross Apply (Values (dateadd(day, -1, dateadd(year, 1, s.ServiceStartDate))))                  e(ServiceEndDate)
  Where dateadd(month, n.n, @startDate) < @endDate
    And datepart(quarter, dateadd(month, n.n, @startDate)) = pd.q;
5

Thanks Jeffw8713 and Lewie.. its working fine