时间戳

首页 > 解决方案 > 将编码列添加到现有表

sql - 将编码列添加到现有表

问题描述

我有一个选择声明，按月提取今年到目前为止的发票价值。我正在尝试添加一列以显示每个月的不同目标。所以我可以每周或每月将实际值与目标值进行比较。下面的查询每月重复 260000，我怎样才能将其更改为每月不同的货币值或在答案未知的情况下显示零？

SELECT 
FORMAT (SUM(dbo.ARInvoices.arpInvoiceSubtotalBase), 'C0') AS "Total",  
DATEPART(month, dbo.ARInvoices.arpInvoiceDate) as "Month", '260000' AS TARGET
FROM dbo.ARInvoices     
WHERE dbo.ARInvoices.arpInvoiceDate >= '{%Current Year%}' 
GROUP BY DATEPART(month, dbo.ARInvoices.arpInvoiceDate)

这给出了一个结果

在此处输入图像描述

理想情况下，结果将如下所示：

MONTH   TARGET   ACTUAL
Jan     $260000   $223735
Feb     $383000   $69299
Mar     $400000   $0
etc

标签： sqlsql-servertsql

解决方案

使用派生表/子查询/cte计算每月总计，然后JOIN到您的目标表

SELECT A.Total, A.Month, T.Target
FROM
(
    SELECT SUM(dbo.ARInvoices.arpInvoiceSubtotalBase) AS Total
           ,  DATEPART(month, dbo.ARInvoices.arpInvoiceDate) as Month
    FROM   dbo.ARInvoices     
    WHERE  dbo.ARInvoices.arpInvoiceDate >= '{%Current Year%}' 
    GROUP BY DATEPART(month, dbo.ARInvoices.arpInvoiceDate)
) A
LEFT JOIN dbo.Targets T ON A.Month = T.Month

推荐阅读