SQL Sum MTD & YTD

Question

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.

Answer 1

You can do this with conditional aggregation:

select sum(case when year(docdate) = year(getdate()) then InvValue end) as YTD,
       sum(case when year(docdate) = year(getdate()) and month(docdate) = month(getdaate())
                then InvValue
           end) as MTD
from table t;

This assumes you have no future dates in the table. If you do, add in docdate < getdate() to both clauses.

EDIT:

If you need this in two rows, you can simply do this:

select (case when n.n = 1 then 'YTD' else 'MTD' end) as which,
       (case when n.n = 1 then YTD else MTD end) as value
from (select sum(case when year(docdate) = year(getdate()) then InvValue end) as YTD,
             sum(case when year(docdate) = year(getdate()) and month(docdate) = month(getdaate())
                      then InvValue
                 end) as MTD
      from table t
     ) cross join
     (select 1 as n union all select 2) n;