SQL Sum MTD &年初至今 [英] SQL Sum MTD & YTD
问题描述
我才刚刚开始研究 SQL.
I have only just started looking into SQL.
我有一个 SQL Server 2008 r2 数据库,它将返回两个字段 DocDate &价值.我需要将 InvValues 相加为 MTD &YTD 截至今天的日期 所以它看起来像
I have a SQL Server 2008 r2 database that will return two fields DocDate & InvValue. I need to sum the InvValues as MTD & YTD as of Today's Date So it looks like
**Period** /////// **Total value**
MTD ////////////111111.11
YTD /////////////999999.99
我已经做了大量的谷歌搜索,并且可以用 SUM & 做一个或另一个.DATEPART,但我坚持尝试同时做.
I have done a fair amount of Googling and can do one or the other with SUM & DATEPART, but I am stuck with trying to do both.
谁能给我一些伪代码来帮助我进一步搜索谷歌.
Can someone give me some pseudo-code that would help me google a little further.
谢谢@Gordon Linoff,这帮了我很多,我学到了一些东西,我将来会觉得有用.我的代码现在看起来像:
Thank you @Gordon Linoff, That helped a lot and I learned something, That I will find useful in the future. My code now looks like:
SELECT
SUM(CASE WHEN YEAR(T1.[DocDate]) = YEAR(GETDATE()) THEN T0.[TotalSumSy] END) AS YTD,
SUM(CASE WHEN YEAR(T1.[DocDate]) = YEAR(GETDATE()) AND MONTH(T1.[DocDate]) = MONTH(GETDATE()) THEN T0.[TotalSumSy] END) AS MTD
FROM [dbo].[INV1] T0 INNER JOIN [dbo].[OINV] T1 ON T1.[DocEntry] = T0.[DocEntry]
但是我现在得到
YTD.........MTD
99999.99....111111.11
我需要
YTD........99999.99
MTD........11111.11
如有任何进一步的帮助,我们将不胜感激.
Any further assistance would be appreciated.
推荐答案
SELECT
Period = 'MTD',
Total_value = SUM(T0.TotalSumSy)
FROM dbo.INV1 T0
INNER JOIN dbo.OINV T1
ON T1.DocEntry = T0.DocEntry
WHERE
T1.DocDate >= DATEADD(month,DATEDIFF(month,'20010101',GETDATE()),'20010101')
AND
T1.DocDate < DATEADD(month,1+DATEDIFF(month,'20010101',GETDATE()),'20010101')
UNION ALL
SELECT
'YTD',
SUM(T0.TotalSumSy)
FROM dbo.INV1 T0
INNER JOIN dbo.OINV T1
ON T1.DocEntry = T0.DocEntry
WHERE
T1.DocDate >= DATEADD(year,DATEDIFF(year,'20010101',GETDATE()),'20010101')
AND
T1.DocDate < DATEADD(year,1+DATEDIFF(year,'20010101',GETDATE()),'20010101') ;
WHERE
子句中的(复杂)条件用于代替 YEAR(column) = YEAR(GETDATE()
和您之前使用的另一个,因此索引可以使用.当您将函数应用于列时,会使索引不可用(某些函数和某些 SQL-Server 版本有一些小例外.)因此,最好的办法是尝试将条件转换为这种类型:
The (complicated) conditions at the WHERE
clauses are used instead of the YEAR(column) = YEAR(GETDATE()
and the other you had previously, so indexes can be used. WHen you apply a function to a column, you make indexes unsuable (with some minor exceptions for some functions and some verios of SQL-Server.) So, the best thing is to try to convert the conditions to this type:
column <operator> AnyComplexFunction()
这篇关于SQL Sum MTD &年初至今的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!