Q:

Splitting out a cost dynamically across weeks

I’m creating an interim table in SQL Server for use with PowerBI to query financial data. 
I have a finance transactions table tblfinance with
            CREATE TABLE TBLFinance
            (ID int,
            Value float,
            EntryDate date,
            ClientName varchar (250) 
            )

INSERT INTO TBLFinance(ID ,Value ,EntryDate ,ClientName)
                VALUES(1,'1783.26','2018-10-31 00:00:00.000','Alpha')
                , (2,'675.3','2018-11-30 00:00:00.000','Alpha')
                , (3,'243.6','2018-12-31 00:00:00.000','Alpha')
                , (4,'8.17','2019-01-31 00:00:00.000','Alpha')
                , (5,'257.23','2019-01-31 00:00:00.000','Alpha')
                , (6,'28','2019-02-28 00:00:00.000','Alpha')
                , (7,'1470.61','2019-03-31 00:00:00.000','Bravo')
                , (8,'1062.86','2019-04-30 00:00:00.000','Bravo')
                , (9,'886.65','2019-05-31 00:00:00.000','Bravo')
                , (10,'153.31','2019-05-31 00:00:00.000','Bravo')
                , (11,'150.24','2019-06-30 00:00:00.000','Bravo')
                , (12,'690.14','2019-07-31 00:00:00.000','Charlie')
                , (13,'21.67','2019-08-31 00:00:00.000','Charlie')
                , (14,'339.29','2018-10-31 00:00:00.000','Charlie')
                , (15,'807.96','2018-11-30 00:00:00.000','Delta')
                , (16,'48.94','2018-12-31 00:00:00.000','Delta')

I’m calculating transaction values that fall within a week. My week ends on a Sunday, so I have the following query:
                INSERT INTO tblAnalysis
                (WeekTotal
                , WeekEnd
                , Client
                )
            SELECT SUM (VALUE) AS WeekTotal
            ,  dateadd (day, case when datepart (WEEKDAY, EntryDate) = 1 then 0 else 8 - datepart (WEEKDAY, EntryDate) end, EntryDate) AS WeekEnd
            , ClientName as Client
            FROM dbo.tblFinance
            GROUP BY dateadd (day, case when datepart (WEEKDAY, EntryDate) = 1 then 0 else 8 - datepart (WEEKDAY, EntryDate) end, EntryDate), CLIENTNAME

I’ve now been informed that some of the costs incurred within a given week maybe monthly, and therefore need to be split into 4 weeks, or annually, so split into 52 weeks. I will write a case statement to update the costs based on ClientName, so assume there is an additional field called ‘Payfrequency’.
I want to avoid having to pull the values affected into a temp table, and effectively write this – because there’ll be different sums applied depending on frequency. 
            SELECT *
             INTO #MonthlyCosts
             FROM 
             ( 
             SELECT 
            client
            , VALUE / 4 AS VALUE 
            , WEEKENDING
              FROM tblAnalysis

                UNION 
                SELECT 
                client
                , nt_acnt 
                , VALUE / 4 AS VALUE 
                , DATEADD(WEEK,1,WEEKENDING) AS WEEKENDING 
                  FROM tblAnalysis
              UNION 
                   SELECT 
                client

                , VALUE / 4 AS VALUE 
                , DATEADD(WEEK,2,WEEKENDING) AS WEEKENDING 
                  FROM tblAnalysis
             UNION 
                   SELECT 
                client

                , VALUE / 4 AS VALUE 
                , DATEADD(WEEK,3,WEEKENDING) AS WEEKENDING 
                  FROM tblAnalysis
            ) AS A

I know I need a stored procedure to hold variables so the calculations can be carried out dynamically, but have no idea where to start.

A:

You can use recursive CTEs to split the data:
with cte as (
      select ID, Value, EntryDate, ClientName, payfrequency, 1 as n
      from TBLFinance f
      union all
      select ID, Value, EntryDate, ClientName, payfrequency, n + 1
      from cte
      where n < payfrequency
     )
select *
from cte;

Note that by default this is limited to 100 recursion steps.  You can add option (maxrecursion 0) for unlimited numbers of days.

