我每天都会发送一大堆计数列,所以我必须将整个月的计数相加,然后进行除法运算,以求出每个月的总计数。如何按月计算所有三种计算的结果?
下面是我试图计算的字段,如何计算整个月的记录总数:
CAST([Sid_Count] / NULLIF([Wrk_Count], 0) AS DECIMAL(18, 4)) AS Quality_Field,
CAST([Talk_Count] / NULLIF([Sid_Count], 0) AS DECIMAL(18, 4)) AS Utilization_Field,
CAST([Cell_Count] / NULLIF([Cell_No_Count] + Cell_No_Count, 0) AS DECIMAL(18, 4)) AS Consent_Field我正在尝试查看的示例:从1月8号到2月8号的计算
DateByMonth Quality_Field Utilization_Field Consent_Field
2021-02-08 Total count by month Total count by month Total count by month发布于 2021-02-09 10:07:48
DECLARE @derivedDateStart DATE = '1/1/2015' --Set as far back as needed
DECLARE @derivedDateEnd DATE = getdate()
CREATE TABLE #tempDerivedMonth (tYear int, tMonth int)
WHILE @derivedDateStart < @derivedDateEnd
BEGIN
INSERT INTO #tempDerivedMonth(tYear, tMonth)
SELECT YEAR(@derivedDateStart, MONTH(@derivedDateStart))
SET DATEADD(m,1,@derivedDateStart)
END
SELECT CONVERT(VARCHAR,CONVERT(DATE,CONVERT(VARCHAR,MONTH(myDate))+'/'+CONVERT(VARCHAR,DAY(getdate()))+'/'+CONVERT(VARCHAR,YEAR(myDate))),23) AS DateByMonth,
SUM(CAST([Sid_Count] / NULLIF([Wrk_Count], 0) AS DECIMAL(18, 4))) AS Quality_Field,
SUM(CAST([Talk_Count] / NULLIF([Sid_Count], 0) AS DECIMAL(18, 4))) AS Utilization_Field,
SUM(CAST([Cell_Count] / NULLIF([Cell_No_Count] + Cell_No_Count, 0)) AS DECIMAL(18, 4)) AS Consent_Field
FROM YourTable yt
JOIN #tempDerivedMonth tdm
ON tdm.tMonth = MONTH(myDate) AND tdm.tYear = YEAR(myDate) AND DAY(myDate) BETWEEN DAY(getdate()) and DATEADD(d,1,DATEADD(m,-1,getdate()))
GROUP BY CONVERT(VARCHAR,CONVERT(DATE,CONVERT(VARCHAR,MONTH(myDate))+'/'+CONVERT(VARCHAR,DAY(getdate()))+'/'+CONVERT(VARCHAR,YEAR(myDate))),23)假设您有一个名为myDate的日期列,您的解决方案可能如下所示(比我希望的要混乱)。要按派生月份分组,我认为您需要一个日程表,例如#tempDerivedMonth。通过对公式求和,可以在From语句中对输出进行分组。很想知道这对你是否有效。(感谢瑞秋在最初回答时的提示)
https://stackoverflow.com/questions/66111635
复制相似问题