title: Splitting out a cost dynamically across weeks
body: <p>I’m creating an interim table in SQL Server for use with PowerBI to query financial data. </p>
  
  <p>I have a finance transactions table tblfinance with</p>
  
  <pre><code>            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')
  </code></pre>
  
  <p>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
                  )</p>
  
  <pre><code>            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
  </code></pre>
  
  <p>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. </p>
  
  <pre><code>            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
  </code></pre>
  
  <p>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.</p>