How to spread month to day with amount value divided by total days per month

Question

I have data with an amount of 1 month and want to change it to 30 days. if 1 month the amount is 20000 then per day is 666.67

The following are sample data and results:

Account	Project	Date	Segment	Amount
Acc1	1	September 2022	Actual	20000

Result :

I need a query using sql server

Answer 1

You can use a recursive CTE to generate each day of the month and then divide the amount by the number of days in the month to achive the required output

DECLARE @Amount NUMERIC(18,2) = 20000,
        @MonthStart DATE = '2022-09-01'

;WITH CTE
AS
(
    SELECT
        CurrentDate = @MonthStart,
        DayAmount = CAST(@Amount/DAY(EOMONTH(@MonthStart)) AS NUMERIC(18,2)),
        RemainingAmount =  CAST(@Amount - (@Amount/DAY(EOMONTH(@MonthStart))) AS NUMERIC(18,2))
    UNION ALL
    SELECT
        CurrentDate = DATEADD(DAY,1,CurrentDate),
        DayAmount = CASE WHEN DATEADD(DAY,1,CurrentDate) = EOMONTH(@MonthStart)
                                THEN RemainingAmount
                            ELSE DayAmount END,
        RemainingAmount = CASE WHEN DATEADD(DAY,1,CurrentDate) = EOMONTH(@MonthStart)
                                THEN 0 
                            ELSE CAST(RemainingAmount-DayAmount AS NUMERIC(18,2)) END
        FROM CTE
            WHERE CurrentDate < EOMONTH(@MonthStart)
)
SELECT
    CurrentDate,
    DayAmount
    FROM CTE

Answer 2

You may try a set-based approach using an appropriate number table and a calculation with windowed COUNT().

Data:

SELECT *
INTO Data
FROM (VALUES
   ('Acc1', 1, CONVERT(date, '20220901'),  'Actual',  20000.00)
) v (Account, Project, [Date], Segment, Amount)

Statement for all versions, starting from SQL Server 2016 (the number table is generated using JSON-based approach with OPENJSON()):

SELECT d.Account, d.Project, a.[Date], d.Segment, a.Amount
FROM Data d
CROSS APPLY (
  SELECT 
    d.Amount / COUNT(*) OVER (ORDER BY (SELECT NULL)), 
    DATEADD(day, CONVERT(int, [key]), d.[Date])
  FROM OPENJSON('[1' + REPLICATE(',1', DATEDIFF(day, d.[Date], EOMONTH(d.[Date]))) + ']')
) a (Amount, Date)

Statement for SQL Server 2022 (the number table is generated with GENERATE_SERIES()):

SELECT d.Account, d.Project, a.[Date], d.Segment, a.Amount
FROM Data d
CROSS APPLY (
  SELECT 
    d.Amount / COUNT(*) OVER (ORDER BY (SELECT NULL)), 
    DATEADD(day, [value], d.[Date])
  FROM GENERATE_SERIES(0, DATEDIFF(day, d.[Date], EOMONTH(d.[Date])))
) a (Amount, Date)

Notes:

• Both approaches calculate the days for each month. If you always want 30 days per month, replace DATEDIFF(day, d.[Date], EOMONTH(d.[Date])) with 29.
• There is a rounding issue with this calculation. You may need to implement an additional calculation for the last day of the month.

Answer 3

In case you want an equal split without rounding errors and without loops you can use this calculation. It spreads the rounding error across all entries, so they are all as equal as possible.

DECLARE @Amount NUMERIC(18,2) = 20000,
        @MonthStart DATE = '20220901'

SELECT DATEADD(DAY,Numbers.i - 1,@MonthStart)
     , ShareSplit.Calculated_Share
     , SUM(ShareSplit.Calculated_Share) OVER (ORDER BY (SELECT NULL)) AS Calculated_Total
  FROM (SELECT DISTINCT number FROM master..spt_values WHERE number BETWEEN 1 AND DAY(EOMONTH(@MonthStart)))Numbers(i)
 CROSS APPLY(SELECT CAST(ROUND(@Amount * 100 / DAY(EOMONTH(@MonthStart)),0) * 0.01
             + CASE
                  WHEN Numbers.i
                       <= ABS((@Amount - (ROUND(@Amount * 100 / DAY(EOMONTH(@MonthStart)),0) / 100.0 * DAY(EOMONTH(@MonthStart)))) * 100)
                       THEN 0.01 * SIGN(@Amount - (ROUND(@Amount * 100 / DAY(EOMONTH(@MonthStart)),0) / 100.0 * DAY(EOMONTH(@MonthStart))))
                  ELSE 0
               END AS DEC(18,2)) AS Calculated_Share
       )ShareSplit