首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >按月计算每次计算的记录

问按月计算每次计算的记录
EN

Stack Overflow用户
提问于 2021-02-09 09:14:21
回答 1查看 38关注 0票数 0

我每天都会发送一大堆计数列,所以我必须将整个月的计数相加,然后进行除法运算,以求出每个月的总计数。如何按月计算所有三种计算的结果?

下面是我试图计算的字段,如何计算整个月的记录总数:

代码语言:javascript
复制
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号的计算

代码语言:javascript
复制
DateByMonth      Quality_Field          Utilization_Field       Consent_Field           
2021-02-08       Total count by month   Total count by month    Total count by month
EN

回答 1

Stack Overflow用户

发布于 2021-02-09 10:07:48

代码语言:javascript
复制
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语句中对输出进行分组。很想知道这对你是否有效。(感谢瑞秋在最初回答时的提示)

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/66111635

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档