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屋！