費用交易表 -
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
有比這更多的列和資料，但按照評論中的要求添加
我已經計算了每月的費用
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)
輸出：
MonthCost Year Month
59045.00 2022 4
56866.67 2022 5
5212.84 2022 6
我想每天分發它。所以，它應該是 MonthCost/DaysInThat 月份，我必須選擇每個月的日期
輸出：
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
.
.
.
.
uj5u.com熱心網友回復：
您可以從日期串列中匯出 CTE，然后使用 CTE 來加入以獲取日期串列。
或者您可以一次性生成所有日期并插入表格并使用它。
下面是我生成日期并進一步使用 CTE 的代碼。
--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
uj5u.com熱心網友回復：
我生成了一個快速計數表來進行日歷數學運算。這應該讓你接近你想要的：
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];
uj5u.com熱心網友回復：
您的資料庫中有可以使用的日歷表嗎？您可以按年和月將月度聚合加入日歷表，從而獲得給定月份中所有天的串列以及整個月的總和。然后將每月總和除以給定月份的天數：right(EOMONTH(CalendarDate),2)。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/493662.html