Get each date of month in sql and from group by take cost and distribute-CodePudding

ExpenseTransactionTable -

    Id                                  Cost    TransactionDate
8CA2152B-FFE2-46BD-B2AC-094105669E37    7500    2022-04-29 01:00:00.357
1110E38B-7BFE-4FB7-9C06-0D46BB60F865    1500    2022-04-29 01:00:05.157
897A9354-2A9C-410C-9F2F-204AC958C2EC    1616    2022-06-01 04:05:00.000
168D5E5B-98AB-4701-940D-24B862ACAB71    773     2022-06-19 10:20:00.000
BFF13EBB-35FD-4956-A5B7-2B8C4C29ECB3    522.3   2022-05-16 16:46:00.000
21FE77CF-F161-45F6-8BCC-328750DB09A0    14500   2022-05-29 01:00:03.927

Have lot more columns and data than this but added as asked in comment

I have calculated cost per month using

Select SUM(CONVERT(DECIMAL(10,2),Cost)) MonthCost, YEAR(ET.TransactionDate) Year, MONTH(ET.TransactionDate) Month 
from dbo.ExpenseTransaction ET
GROUP BY YEAR(ET.TransactionDate), MONTH(ET.TransactionDate)

Output:

MonthCost   Year    Month
59045.00    2022    4
56866.67    2022    5
5212.84     2022    6

I want to distribute it to per day. So, it should be MonthCost/DaysInThat month and I have to select each date of month

Output:

MonthCost   Year    Month        Day            DayCost
59045.00    2022    4           2022-04-01      1968.17
59045.00    2022    4           2022-04-02      1968.17
59045.00    2022    4           2022-04-03      1968.17
59045.00    2022    4           2022-04-04      1968.17
59045.00    2022    4           2022-04-05      1968.17
59045.00    2022    4           2022-04-06      1968.17
.
.
.
.


56866.67    2022    5           2022-05-01      1904.68
56866.67    2022    5           2022-05-02      1904.68
56866.67    2022    5           2022-05-03      1904.68
56866.67    2022    5           2022-05-04      1904.68
56866.67    2022    5           2022-05-05      1904.68
56866.67    2022    5           2022-05-06      1904.68
.
.
.
.

CodePudding user response：

You can derive CTE the list of dates and then use the CTE to Join to get the list of dates.

OR You can generate All the dates and Insert into a table as one time effort and use it.

Below is the code which I generated the dates and used the CTE further.

--Create table avgmontcost(MonthCost  float,[Year] INT,[Month] INT)

--iNSERT INTO avgmontcost
--SELECT 59045.00,2022,4 UNION
--SELECT 56866.67,2022,5 UNION
--SELECT 5212.84,2022,6


DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate = '2022-04-01', @EndDate = '2022-05-31'; 

WITH ListDates(AllDates,Year,Month,TotalDaysInMonth) 
AS
(   SELECT @StartDate AS DATE,year(@StartDate) as Year, Month(@StartDate) as Month, DAY(EOMONTH(@StartDate)) AS TotalDays
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates),year(DATEADD(DAY,1,AllDates)) as Year, Month(DATEADD(DAY,1,AllDates)) as Month, DAY(EOMONTH(@StartDate)) AS TotalDays
    FROM ListDates 
    WHERE AllDates < @EndDate
)

SELECT A.MonthCost,D.AllDates,D.Year,D.Month,1.00*a.MonthCost/TotalDaysInMonth as DayCost
FROM avgmontcost A
INNER JOIN ListDates  D ON A.MONTH = D.MONTH AND A.YEAR = D.YEAR

CodePudding user response：

I generated a quick tally table to do the calendar math. This should get you close to what you wanted:

CREATE TABLE #tmp(MonthCost decimal(10,2),   Year int,    Month int)
INSERT INTO #tmp VALUES
(59045.00,    2022,    4),
(56866.67,    2022,    5),
(5212.84 ,    2022,    6);


WITH TallyTable AS (
SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2 
),
CTE AS
(
    SELECT *, DATEFROMPARTS([YEAR], [MONTH],1) MonStart,
            DAY(EOMONTH(DATEFROMPARTS([YEAR], [MONTH],1)) ) NumDays
    FROM #tmp
)
SELECT MonthCost,[YEAR], [MONTH],DATEADD(Day,N-1,MonStart) [DAY], CONVERT(decimal(10,2), MonthCost / NumDays ) DayCost
from CTE t1
CROSS APPLY(SELECT N FROM TallyTable where N <= NumDays ) X
ORDER BY [DAY];

CodePudding user response：

Do you have a calendar table in your db you can work with? You could join monthly aggregates to a calendar table on year and month and therefore getting a list of all days in a given month with a sum for the whole month. And then divide the the monthly sum with number of days of given month: right(EOMONTH(CalendarDate),2).