Ungroup a date grouping-CodePudding

The problem: I have a timeframe represented by three years of twelve months, in the form of 36 financial periods. I find it easiest to just refer to each month by the last day of each month (EOMONTH).

I have two tables I want to compare. But my problem is that one table needs translating, and I can't see how to do it.

Table1: Financials - Already perfect.
Table2: Activities - Needing transformation. <<<< this is my difficulty

From a business point of view, this data represents project work.

The financial view is ready, and tells me that x dollars are being spent in y period.
The Activities view is supposed to tell me that the activities ABC were occuring in period y.

With those two tables, I can easily compare the periods and get a sense of what is going on.

Here's the timeframe backbone, just for interest

declare @StartDate date = '2021-07-01';
declare @EndDate date = '2024-06-30';

WITH cte AS (
    SELECT @StartDate AS myDate
    UNION ALL
    SELECT DATEADD(MONTH,1,myDate) as myDate
    FROM cte
    WHERE DATEADD(MONTH,1,myDate) <=  @EndDate
)
SELECT EOMONTH(myDate)
FROM cte
OPTION (MAXRECURSION 0)

And then my data looks like this

Table #1 - The dollars by ProjectID, Financial Period

ProjectID   FinPeriod   PlannedSpend
AB309       2021-07-31  24087.51
AB302       2021-07-31  2877.5
AB315       2021-07-31  360
AB326       2021-07-31  0
AB331       2021-07-31  720
AB335       2021-07-31  -69325.02
AB454       2021-07-31  18876.8
AB449       2021-07-31  225
AB455       2021-07-31  -24972.1
AB430       2021-07-31  450
AB448       2021-07-31  9825.5

Table #2 - The Activities by Financial Period

ProjectID   PhaseName       FinPeriodStart  FinPeriodFinish
AB315       Closure         2022-06-30      2024-06-30
AB315       Implementation  2022-10-31      2023-07-31
AB315       Investigation   2021-08-31      2022-04-30
AB335       Closure         2020-10-31      2021-09-30
AB335       Design          2020-09-30      2021-03-31
AB448       Closure         2022-03-31      2023-03-31
AB448       Design          2020-02-29      2020-05-31
AB448       Design          2022-04-30      2023-02-28
AB448       Implementation  2022-02-28      2022-05-31

The goal: to produce this.

ProjectID   FinPeriod   Activities
AB315       2022-10-31  Implementation, Closure
AB448       2022-03-31  Closure
AB335       2020-09-30  Design, Clos
AB335       2021-09-30  Closure
AB326       2021-07-31  Design, Implementation, Closure, 
AB331       2021-07-31  xxxxx

My attempt:

Firstly, I have a table of financial periods representing 3 years or 36 months or 36 financial periods. Interesting, but not really directly useful to solving the problem until it comes to visualising the layout.

Secondly, I can derive a financial period for a Phase start date, and a Phase end date using the EOMONTH() function. So For Project ID x, Investigation Phase started in 2021-12-13 which is financial period 2021-12-31 and it ended in 2022-06-29 which is financial period 2022-06-30

But how do I "backfill" or "translate" the fact that if activity X started in Jan and ended in Dec, then it must have been happening through all of the 12 months in-between? I think the crux of my problem is I can't see how to change

AB335       Design          2020-09-30      2021-03-31

Into

AB335       2020-09-30  Design          
AB335       2020-10-31  Design          
AB335       2020-11-30  Design
...
AB335       2021-03-31  Design

CodePudding user response：

It looks like you sort of have your solution given your CTE that gives you the list of months required.

You could make use of that to produce your desired list by utlising apply, such as:

declare @StartDate date = '20200101', @EndDate date = '20211231';

with cte as (
    select @StartDate as myDate
    union all
    select DateAdd(month,1,myDate) as myDate
    from cte
    where DateAdd(month,1,myDate) <=  @EndDate
), dates as (
    select EOMonth(myDate) mydate
    from cte
), activities as (
  select 'AB335' ProjectId, 'Design' Phasename, Convert(date,'20200930') FinPeriodStart, Convert(date,'20210331') FinPeriodEnd
)
select ProjectId, mydate, Phasename
from activities a
outer apply(
  select mydate from dates
    where mydate >= FinPeriodStart and mydate <= FinPeriodEnd
)md;

Just use your CTE with a suitably large range to cover requirements and create a list of dates in a temp or permanent table.