Break the dates with respect to start_date and end_date in monthly, quarterly and yearly

Question

I have a existing query in which i have break dates according to yearly with a check that BusinessGoal.period = 'Year' and now i have to modify the query to insert a check on BusinessGoal.period = 'Monthly' and BusinesGoal.period = 'Quarterly'. I have inserted a sample table in which the first table is the original table (BusinessGoal) and the second table is the final output we want from the SQL Query.

BusinessRefId   Period  GoalType    Amount  StartDateUtc    EndDateUtc  Currency
Business A  Year    CommittedTransactionFee 45000   07/06/19 00:00      USD
Business B  Year    CommittedTransactionFee 18000   07/06/17 00:00      USD
Business C  Month   CommittedTransactionFee 18000   05/07/19 00:00      USD
Business D  Quarter CommittedTransactionFee 5000    01/04/19 00:00      USD
Business E  Year    CommittedTransactionFee 5000    01/04/2019  01/08/2019  USD



BusinessRefId   Period  GoalType    Amount  StartDateUtc    EndDateUtc  Currency
Business A  2019-2020   CommittedTransactionFee 45000   07/06/2019  07/06/2020  USD
Business B  2019-2020   CommittedTransactionFee 18000   07/06/19 00:00  07/06/2020  USD
Business B  2018-2019   CommittedTransactionFee 18000   07/06/18 00:00  07/06/2019  USD
Business B  2017-2018   CommittedTransactionFee 18000   07/06/17 00:00  07/06/2018  USD
Business C  2019-07 - 2019-08   CommittedTransactionFee 18000   05/07/19 00:00  05/08/2019  USD
Business C  2019-08 - 2019-09   CommittedTransactionFee 18000   05/08/19 00:00  05/09/2019  USD
Business C  2019-09 - 2019-10   CommittedTransactionFee 18000   05/09/19 00:00  05/10/2019  USD
Business D  2019-Q2 CommittedTransactionFee 5000    01/04/19 00:00  01/06/2019  USD
Business D  2019-Q3 CommittedTransactionFee 5000    01/06/19 00:00  01/10/2019  USD
Business E  2019    CommittedTransactionFee 5000    01/08/2019  01/04/2019  USD

The original query that was working initially was breaking the dates on the basis that BusinessGoal.Period has only one value and that is "Year".

;WITH Generate_Dates(Start_Date, End_Date) AS (
  SELECT
    CAST(StartDateUtc AS Date),
    CAST(
      CASE WHEN [bg].EndDateUtc IS NULL THEN GETDATE() ELSE CAST([bg].EndDateUtc AS Date) end AS Date
    )
  FROM
    BusinessGoal AS [bg] WITH (NOLOCK)
  WHERE
    [bg].Period = 'Year'
  UNION ALL
  SELECT
    DATEADD(yy, 1, Start_Date),
    End_Date
  FROM
    Generate_Dates
  WHERE
    DATEADD(yy, 1, Start_Date) < End_Date
)
SELECT
  (
    Generate_Dates.Start_Date
  ) AS "Start Period Date",
  (
    Generate_Dates.End_Date
  ) AS "End Period Date",
  [bg].BusinessRefId AS 'Brand',
  (
    CAST (
      [bg].Amount AS DECIMAL
    )
  ) AS "Committed Transaction Fee",
  (
    SELECT
      CAST (
        sum (
          (
            CASE WHEN [s].Credit = 0 THEN - [s].SettlementAmount ELSE [s].SettlementAmount END
          )
        ) AS DECIMAL
      )
  ) AS "Actual Transaction Fee",
  (
    SELECT
      TOP 1 (
        CASE [s].Culture WHEN 'en-US' THEN 'USD' WHEN 'en-CA' THEN 'CAD' WHEN 'en-IE' THEN 'EUR' WHEN 'en-AU' THEN 'AUD' WHEN 'en-NZ' THEN 'NZD' WHEN 'en-Gbg' THEN 'GbgP' WHEN 'es-MX' THEN 'MXN' WHEN 'jp-JP' THEN 'JPY' WHEN 'nbg-NO' THEN 'NOK' end
      )
    FROM
      Settlement AS [s] WITH (NOLOCK)
  ) AS "Currency"
FROM
  Settlement AS [s] WITH (NOLOCK)
  INNER JOIN OrderProductVariant AS [opv] WITH (NOLOCK) ON
  (
    [opv].MRefId = [s].CompanyRefId
    AND [opv].Id = [s].OrderProductVariantId
  )
  INNER JOIN Manufacturer WITH (NOLOCK) on Manufacturer.RefId = [opv].MRefId
  INNER JOIN [Order] AS [o] WITH (NOLOCK) ON [o].Id = [opv].OrderId
  INNER JOIN BusinessGoal AS [bg] WITH (NOLOCK) ON [bg].BusinessRefId = [s].CompanyRefId 
  INNER JOIN Generate_Dates ON GoalType = 'CommittedTransactionFee'
WHERE
    StartDateUtc <= [s].CreatedOnUtc
    AND (
      (
        EndDateUtc >= [s].CreatedOnUtc
      )
      OR (EndDateUtc is null)
    )
GROUP BY
  Start_Date,
  End_Date,
  BusinessRefId,
  Amount,
  [bg].Currency

Answer 1

I worked on your query logic for yearly,monthly,quarterly. Check below queries. It might be helpful.

declare @BusinessGoal table    
(BusinessRefId varchar(50),   
Period varchar(50), 
GoalType varchar(100),    
Amount money, 
StartDateUtc datetime,   
EndDateUtc datetime, 
Currency varchar(10))


insert into @BusinessGoal values
('Business A',  'Year',    'CommittedTransactionFee', 45000,   '07/06/19', '00:00',      'USD'),
('Business B',  'Year',   'CommittedTransactionFee', 18000,   '07/06/17', '00:00',      'USD'),
('Business E',  'Year',    'CommittedTransactionFee', 5000 ,   '01/08/2019',  '01/04/2019',  'USD'),
('Business C',  'Month',    'CommittedTransactionFee', 18000 ,   '07/05/2019',  '00:00',  'USD'),
('Business D',  'Quarter',    'CommittedTransactionFee', 5000 ,   '04/01/2019',  '00:00',  'USD')

--This is for temporary @period_table inserrtion dynamically
--Start--
declare @Min_DateUtc datetime,@Max_DateUtc datetime
select @Min_DateUtc=min(DateUtc) ,@Max_DateUtc=dateadd(yy,1,max(DateUtc))  from 
(
select StartDateUtc as DateUtc  from @BusinessGoal
union 
select EndDateUtc as DateUtc from @BusinessGoal
) a where DateUtc<>'' and DateUtc is not null

declare @period_table_temp table   
(period_number int,
period_name varchar(50),period_type varchar(50))

while (@Min_DateUtc<=@Max_DateUtc)
begin

    insert into @period_table_temp (period_number,period_name,period_type)
    select DATEPART(YYYY,@Min_DateUtc),
    cast(DATEPART(YYYY,@Min_DateUtc) as varchar(20))+'-'+cast(DATEPART(YYYY,@Min_DateUtc)+1 as varchar(20)),
    'Year'

    union

    select cast(convert(varchar(6), @Min_DateUtc,112) as int),
    left(convert(varchar(6), @Min_DateUtc,112),4)+'-'+right(convert(varchar(6), @Min_DateUtc,112),2)+' - '+
    left(convert(varchar(6), dateadd(MM,1,@Min_DateUtc),112),4)+'-'+right(convert(varchar(6), dateadd(MM,1,@Min_DateUtc),112),2),
    'Month'

    union

    select cast(CAST(datepart(YYYY,@Min_DateUtc) as varchar(4))+cast(datepart(QQ,@Min_DateUtc) as varchar(1)) as int),
    CAST(datepart(YYYY,@Min_DateUtc) as varchar(4))+'-Q'+cast(datepart(QQ,@Min_DateUtc) as varchar(1)) +' - '+
    CAST(datepart(YYYY,dateadd(QQ,1,@Min_DateUtc)) as varchar(4))+'-Q'+cast(datepart(QQ,dateadd(QQ,1,@Min_DateUtc)) as varchar(1))  ,
    'Quarter'
    set @Min_DateUtc=DATEADD(MM,1,@Min_DateUtc)
end


declare @period_table table   
(period_number int,
period_name varchar(50),period_type varchar(50))

insert into @period_table 
select distinct period_number,period_name,period_type from @period_table_temp

--End--


----- Combine all periods
---Year wise  
select 
a.BusinessRefId,
b.period_name,
a.GoalType,
a.Amount, 
DATEADD(YY,b.period_number-YEAR(a.StartDateUtc),a.StartDateUtc) as StartDateUtc,
case when a.EndDateUtc='' then  DATEADD(YY,b.period_number-YEAR(a.StartDateUtc)+1,a.StartDateUtc) else a.EndDateUtc end as EndDateUtc ,
a.Currency
from @BusinessGoal a 
inner join @period_table b on b.period_number  >=year(a.StartDateUtc)
where a.Period='Year' and b.period_number <=YEAR(GETDATE()) 

union all
---Month wise
select 
a.BusinessRefId,
b.period_name,
a.GoalType,
a.Amount, 
DATEADD(MM,b.period_number-cast(convert(varchar(6), a.StartDateUtc,112) as int),a.StartDateUtc) as StartDateUtc,
case when a.EndDateUtc='' then  DATEADD(MM,b.period_number-cast(convert(varchar(6), a.StartDateUtc,112) as int)+1,a.StartDateUtc) else a.EndDateUtc end as EndDateUtc ,
a.Currency 
from @BusinessGoal a 
inner join @period_table b on b.period_number  >=cast(convert(varchar(6), a.StartDateUtc,112) as int)
where a.Period='Month' and b.period_number <= convert(varchar(6), GETDATE(),112)
union all

-- Quarter wise
select 
a.BusinessRefId,
b.period_name,
a.GoalType,
a.Amount,
DATEADD(QQ,b.period_number-cast(CAST(year(a.StartDateUtc) as varchar(4))+cast(datepart(QQ,a.StartDateUtc) as varchar(1)) as int),a.StartDateUtc) as StartDateUtc,
case when a.EndDateUtc='' then  DATEADD(QQ,b.period_number-cast(CAST(year(a.StartDateUtc) as varchar(4))+cast(datepart(QQ,a.StartDateUtc) as varchar(1)) as int)+1,a.StartDateUtc) else a.EndDateUtc end as EndDateUtc ,
a.Currency 
from @BusinessGoal a 
inner join @period_table b on b.period_number  >=cast(CAST(year(a.StartDateUtc) as varchar(4))+cast(datepart(QQ,a.StartDateUtc) as varchar(1)) as int)
where a.Period='Quarter' and b.period_number <= cast(CAST(year(GETDATE()) as varchar(4))+cast(datepart(QQ,GETDATE()) as varchar(1)) as int)  
order by a.BusinessRefId,b.period_name

You can insert @period_table table. Yearly, Monthly and Quarterly records based on above values I already inserted.

Sample output

----------------+-----------------------+---------------------------+---------------+---------------------------+---------------------------+-------------
BusinessRefId   |   period_name         |   GoalType                |   Amount      |   StartDateUtc            |   EndDateUtc              |   Currency
----------------+-----------------------+---------------------------+---------------+---------------------------+---------------------------+-------------
Business A      |   2019-2020           |   CommittedTransactionFee |   45000.00    |   2019-07-06 00:00:00.000 |   2020-07-06 00:00:00.000 |   USD
Business B      |   2017-2018           |   CommittedTransactionFee |   18000.00    |   2017-07-06 00:00:00.000 |   2018-07-06 00:00:00.000 |   USD
Business B      |   2018-2019           |   CommittedTransactionFee |   18000.00    |   2018-07-06 00:00:00.000 |   2019-07-06 00:00:00.000 |   USD
Business B      |   2019-2020           |   CommittedTransactionFee |   18000.00    |   2019-07-06 00:00:00.000 |   2020-07-06 00:00:00.000 |   USD
Business C      |   2019-07 - 2019-08   |   CommittedTransactionFee |   18000.00    |   2019-07-05 00:00:00.000 |   2019-08-05 00:00:00.000 |   USD
Business C      |   2019-08 - 2019-09   |   CommittedTransactionFee |   18000.00    |   2019-08-05 00:00:00.000 |   2019-09-05 00:00:00.000 |   USD
Business C      |   2019-09 - 2019-10   |   CommittedTransactionFee |   18000.00    |   2019-09-05 00:00:00.000 |   2019-10-05 00:00:00.000 |   USD
Business D      |   2019-Q2 - 2019-Q3   |   CommittedTransactionFee |   5000.00     |   2019-04-01 00:00:00.000 |   2019-07-01 00:00:00.000 |   USD
Business D      |   2019-Q3 - 2019-Q4   |   CommittedTransactionFee |   5000.00     |   2019-07-01 00:00:00.000 |   2019-10-01 00:00:00.000 |   USD
Business E      |   2019-2020           |   CommittedTransactionFee |   5000.00     |   2019-01-08 00:00:00.000 |   2019-01-04 00:00:00.000 |   USD