时间戳

首页 > 解决方案 > TSQL 运行总计

sql-server - TSQL 运行总计

问题描述

我正在运行 MS SQL Server 2012。我正在尝试按月计算迄今为止的运行总计，并将成本类型计算为趋势。此查询按成本类型正确地为我提供了当前月份的总计：

SELECT 
JCCD.Job
,JCCD.CostType
,JCCT.Description
,[Total] = SUM(JCCD.ProjCost) - SUM(JCCD.ActualCost)
FROM
JCCD JCCD
JOIN
JCCT JCCT
ON
JCCD.PhaseGroup = JCCT.PhaseGroup AND JCCD.CostType = JCCT.CostType
WHERE 
JCCo = 1 AND Job = 1378 AND JCCD.Mth <= '7/1/2019'
GROUP BY
JCCD.Job
,JCCD.CostType
,JCCT.Description
ORDER BY CostType

正确结果：

Job CostType    Description Total
 1378.  1   Subcontractors  16145427.78
 1378.  3   Materials/Supplies  11338262.26
 1378.  4   Direct Labor (PR Use Only)  7132036.05
 1378.  5   Repairs 166219.65
 1378.  6   Fuel    976015.51
 1378.  7   Equipment   5309309.12
 1378.  9   Other Direct Costs  1994790.36
 1378.  90  Contingency 1859419.04
 1378.  95  Management Reserve  0.00
 1378.  100 Allocated Indirects 427724.92

但是按成本类型计算，我每月的累计运行总额都失败了。我似乎无法弄清楚分区函数以按成本类型获取每月的累积值。这是我尝试过的一个不正确的版本：

SELECT
JCCD.Job
,JCCD.CostType
,Mth
,[Total] = SUM(JCCD.ProjCost) - SUM(JCCD.ActualCost)
,[RT] = SUM(JCCD.ProjCost) - SUM(JCCD.ActualCost) OVER (PARTITION BY Mth, CostType ORDER BY Mth, CostType RANGE UNBOUNDED PRECEDING)
FROM
JCCD JCCD
WHERE 
JCCo = 1 AND Job = ' 1378.' AND JCCD.Mth <= '7/1/2019'
GROUP BY
JCCD.Job
,JCCD.CostType
,ActualCost
,ProjCost
,Mth
ORDER BY Mth

结果片段：

Job CostType    Mth Total   RT
 1378.  9   2019-07-01 00:00:00 -4207.50    -99105.59
 1378.  9   2019-07-01 00:00:00 -5036.61    -99105.59
 1378.  9   2019-07-01 00:00:00 -5880.19    -99105.59
 1378.      9   2019-07-01 00:00:00 -7788.37    -99105.59
 1378.  9   2019-07-01 00:00:00 -35778.60   -99105.59
 1378.  9   2019-07-01 00:00:00 -29694.75   -99105.59
 1378.  100 2019-07-01 00:00:00 -35079.22   -17539.61

帮助！:-)

我尝试了下面的建议，但这并没有完全提供我感兴趣的结果，这里是这些结果的一个片段。如果我按成本类型每月有一笔款项，那将是正确的。

Job CostType    Mth Total   RT  RT2
 1378.      9   2019-05-01 00:00:00 -191.99 2302027.86  42364678.68
 1378.      9   2019-05-01 00:00:00 -628.76 2302027.86  42364678.68
 1378.      9   2019-05-01 00:00:00 -681.00 2302027.86  42364678.68
 1378.      9   2019-05-01 00:00:00 -1481.18    2302027.86  42364678.68
 1378.      9   2019-05-01 00:00:00 -765.06 2302027.86  42364678.68
 1378.  9   2019-05-01 00:00:00 -944.25 2302027.86  42364678.68
 1378.      9   2019-05-01 00:00:00 -1068.50    2302027.86  42364678.68
 1378.      9   2019-05-01 00:00:00 -1082.00    2302027.86  42364678.68

标签： sql-servertsql

解决方案

希望这会奏效，您可能必须更改PARTITION BY适合您需要的列。

select   T.*
        ,[RT] = SUM(t.[Total]) OVER (PARTITION BY Mth, CostType ORDER BY Mth, CostType RANGE UNBOUNDED PRECEDING)
        ,[RT2] = SUM(t.[Total]) OVER (PARTITION BY Mth ORDER BY CostType)
from    (  SELECT   JCCD.Job
                    ,JCCD.CostType
                    ,Mth
                    ,[Total] = SUM(JCCD.ProjCost) - SUM(JCCD.ActualCost)
            FROM JCCD JCCD
            WHERE JCCo = 1 AND Job = ' 1378.' AND JCCD.Mth <= '7/1/2019'
            GROUP BY JCCD.Job, JCCD.CostType, ActualCost, ProjCost,Mth
        ) as T
ORDER BY Mth

推荐阅读