Share via


Year To Date, quarter to date and Months to date sum in single query

Question

Wednesday, January 14, 2009 6:47 AM

Hi,
I want to calculate the Year To Date, quarter to date and Months to date sum.
For example lets take AdventureWorks database and table Purchasing.PurchaseOrderHeader

And take following columns
select PurchaseOrderId,OrderDate,vendorId,TotalDue
from Purchasing.PurchaseOrderHeader

Now I want to calculate the Year to date, quarter to date and month to date sum of TotalDue on date level for all vendorId Leena

All replies (11)

Wednesday, January 14, 2009 7:32 PM ✅Answered

Leena DW,

> Now I want to calculate the Year to date, quarter to date and month to date sum of TotalDue on date level for all vendorId

Do you want to calculate it for every date where the verndorid has a purchase order or just for an specific date?

If you want it for a specific date, then Aaron solution will take you there. In case you want to do it for every row, then you could try:

;WITH VendorsDates  
AS  
(  
SELECT DISTINCT  
    [VendorID],  
    [OrderDate],  
    DATEADD(YEAR, DATEDIFF(YEAR, '19000101', [OrderDate]), '19000101') AS ysd,  
    DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', [OrderDate]), '19000101') AS qsd,  
    DATEADD(MONTH, DATEDIFF(MONTH, '19000101', [OrderDate]), '19000101') AS msd  
FROM  
    [Purchasing].[PurchaseOrderHeader]  
)  
SELECT  
    v.[VendorID],  
    YEAR(v.[OrderDate]) AS [Y],  
    DATENAME(quarter, v.[OrderDate]) AS [Q],  
    MONTH(v.[OrderDate]) AS [M],  
    v.[OrderDate],  
    SUM(CASE WHEN p.[OrderDate] >= v.[msd] AND p.[OrderDate] <= v.[OrderDate] THEN p.[TotalDue] END) AS MTD,  
    SUM(CASE WHEN p.[OrderDate] >= v.[qsd] AND p.[OrderDate] <= v.[OrderDate] THEN p.[TotalDue] END) AS QTD,      
    SUM(CASE WHEN p.[OrderDate] <= v.[OrderDate] THEN p.[TotalDue] END) AS YTD  
FROM  
    [VendorsDates] AS v  
    INNER JOIN  
    [Purchasing].[PurchaseOrderHeader] AS p  
    ON p.[VendorID] = v.[VendorID]  
    AND p.[OrderDate] >= v.ysd AND p.[OrderDate] <= v.[OrderDate]  
GROUP BY  
    v.[VendorID],  
    v.[OrderDate]  
ORDER BY  
    v.[VendorID],  
    v.[OrderDate];  
GO 

If you want to see all dates in certain period, even if the vendor does not have purchased anything in that date, then having a calendar table will be handy.

AMB


Wednesday, January 14, 2009 7:25 AM

Hi Leena,

This should do:

--There aren't any current orders in AdventureWorks  
DECLARE @Now datetime  
SET @Now = '03/25/2002'  
 
--The query  
select  VendorID,  
        SUM(CASE 
                WHEN OrderDate BETWEEN dateadd(yy, DATEDIFF(yy, 0, @Now), 0) AND @Now THEN TotalDue  
                ELSE 0  
            END) AS YTD,  
        SUM(CASE 
                WHEN OrderDate BETWEEN dateadd(mm, 3 * (DATEDIFF(mm, 0, @Now)/3), 0) AND @Now THEN TotalDue  
                ELSE 0  
            END) AS QTD,  
        SUM(CASE 
                WHEN OrderDate BETWEEN dateadd(mm, DATEDIFF(mm, 0, @Now), 0) AND @Now THEN TotalDue  
                ELSE 0  
            END) AS MTD  
FROM    Purchasing.PurchaseOrderHeader  
GROUP BY VendorID  
 

Let me break out the date math for you.  I used a date in 2002, because AdventureWorks data isn't current.  I'm then summing the result of a case statement for YTD, QTD, and MTD amounts.  The YTD case statement returns TotalDue for a given line if the OrderDate is between the first day of the year and now, the QTD case statement returns TotalDue for a given line if the OrderDate is between the first day of the current quarter and now, and the MTD statement does the same for the current month.

I can get "the first day of the year" in the YTD statement by calculating the number of years since "day zero" and now, and adding that to "day zero".  JFYI, "day zero" in SQL Server is generally 1900-01-01 00:00:00.000 if you're using a regular datetime.

I can get "the first day of the quarter" in the QTD statement by calculating the number of months since "day zero".  I then divide this by 3.  Because we're working with integer math, this returns "the number of quarters" since day zero.  I then multiply this by 3 to get
the number of months between day zero and the beginning of this quarter, and then add that to day zero.

Finally, I can get "the first day of the month" in the MTD statement by calculating the number of months between day zero and today, and adding that number of months to day zero.

Does this make sense?Aaron Alton | thehobt.blogspot.com


Wednesday, January 14, 2009 7:28 AM

Almost any solution to this will be 'messy'...

One approach would be to use corelated sub-queries, something like:

SELECT
   PurchaseOrderId,
   OrderDate,
   VendorId,
   TotalDue,
   (  SELECT sum( TotalDue )
      FROM Purchasing.PurchaseOrderHeader p1
      WHERE (   p1.VendorID = p0.VendorID
            AND p1.OrderDate >= dateadd( month, datediff( month, 0, getdate()), 0 )
            )
    ) AS MonthToDate,
   (  SELECT sum( TotalDue ) 
      FROM Purchasing.PurchaseOrderHeader p1
      WHERE (   p1.VendorID = p0.VendorID
            AND p1.OrderDate >= dateadd( quarter, datediff( quarter, 0, getdate()), 0 )
            )
   ) AS QuarterToDate,
   (  SELECT sum( TotalDue )
      FROM Purchasing.PurchaseOrderHeader p1
      WHERE (   p1.VendorID = p0.VendorID
            AND p1.OrderDate >= dateadd( year, datediff( year, 0, getdate()), 0 )
            )
   ) AS YearToDate
FROM Purchasing.PurchaseOrderHeader p0
ORDER BY p0.PurchaseOdreId

Personally, I like Aaron's approach. It is a bit cleaner.
Now if he would only use 'quarter' in the datediff...


You may be only one person in the world, but you may also be the world to one person.


Wednesday, January 14, 2009 9:46 AM

What's wrong with using subqueries (as in derived tables) with joins as oposed to correlating? I think you'll find it will be more efficient :)George


Wednesday, January 14, 2009 10:10 AM

hi,
I can't use the variable here.  Even if we don't current data if we run the query it should calculate the YTD,QTD, MTD till the date of that particular date.
I run the Arnie's query, but I got all last 3 rows as NULL.

Ok, here is what I tried on my own... See the result, that is what I'm expecting, but not able to combine 2 queris.
Actually this is something very easily done in SSAS and MDX but I need to implement it in T-SQL

WITH CTE AS ( 
select PurchaseOrderId,OrderDate,Year(OrderDate) as Years, month(OrderDate) as months,vendorId,TotalDue  
 
from Purchasing.PurchaseOrderHeader 
 
SELECT a.vendorId,a.years,a.months,a.orderdate,SUM(b.TotalDue) as MTD--,SUM(c.TotalDue) YTD 
FROM CTE A LEFT JOIN CTE B 
ON a.vendorId = b.vendorId AND a.years = B.YEARS AND a.months =B.months 
AND a.orderdate >= B.Orderdate 
/*LEFT JOIN CTE c 
ON a.vendorId = c.vendorId and  a.years = c.YEARS  
and a.orderdate >= c.Orderdate 
*/ 
group by a.vendorId,a.years,a.months,a.orderdate 
order by 1,4 
 
 
;WITH CTE AS ( 
select PurchaseOrderId,OrderDate,Year(OrderDate) as Years, month(OrderDate) as months,vendorId,TotalDue  
 
from Purchasing.PurchaseOrderHeader 
 
SELECT a.vendorId,a.years,a.months,a.orderdate,SUM(b.TotalDue) as YTD 
FROM CTE A LEFT JOIN CTE B 
ON a.vendorId = b.vendorId AND a.years = B.YEARS  
AND a.orderdate >= B.Orderdate 
 
group by a.vendorId,a.years,a.months,a.orderdate 
order by 1,4 
select vendorId,PurchaseOrderId,OrderDate,Year(OrderDate) as Years, month(OrderDate) as months,vendorId,TotalDue  
 

Leena


Wednesday, January 14, 2009 2:55 PM

The code that I gave you doesn't need a variable, I just used one so you could see summary totals.  There aren't any 2009 PO's in AdventureWorks, which is why I introduced the variable.  Feel free to substitute GetDate() instead.

Aaron Alton | thehobt.blogspot.com


Wednesday, January 14, 2009 4:20 PM

If you examine the execution plan for the suggestion that Aaron provided, you will find that it is very efficient, and is most likely, the best option.You may be only one person in the world, but you may also be the world to one person.


Wednesday, January 14, 2009 6:49 PM

Thanks, Arnie.  I didn't think about a WHERE clause, but there should probably be one to limit the rows that are aggregated to the current year only - like this:

WHERE OrderDate BETWEEN dateadd(yy, DATEDIFF(yy, 0, @Now), 0) AND GetDate() 

With this predicate, the case statement could be dropped in the YTD calculation, and it could be changed to

SUM(TotalDue) AS YTD 

HTH....Aaron Alton | thehobt.blogspot.com


Wednesday, January 14, 2009 6:54 PM

Aaron why not use dateadd(qq, DATEDIFF(qq, 0, @Now), 0) for QTD?

Denis The SQL Menace 

The Ten Most Asked SQL Server Questions And Their Answers
SQL Server Programming Hacks


Tuesday, January 27, 2009 6:26 AM

Sorry for late reply. Hunchback's solution is what I was looking for. 

Thanks.

Leena


Tuesday, January 27, 2009 6:34 AM

SQL_Menace said:

Aaron why not use dateadd(qq, DATEDIFF(qq, 0, @Now), 0) for QTD?

Denis The SQL Menace 

The Ten Most Asked SQL Server Questions And Their Answers
SQL Server Programming Hacks

Because I didn't think of it ;-)

Good catch, Denis.  Thanks for pointing it out.

Aaron Alton | thehobt.blogspot.com