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:

Result :

I need a query using sql server

CodePudding user response：

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

CodePudding user response：

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.