SQL Sum MTD & YTD

sql, sql-server, sql-server-2008-r2

Solution

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') ;

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()

Problem

I have only just started looking into SQL. 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 ``` 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. 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] ``` However I now get ``` YTD.........MTD 99999.99....111111.11 ``` And I need ``` YTD........99999.99 MTD........11111.11 ``` Any further assistance would be appreciated.

Original source