Selecting records for a yearly report (for the past 3 years)

Question

Consider the following SQL statement:

DECLARE @CustomerId INT = 48;
DECLARE @CurrentYear INT = DATEPART(YEAR, GETDATE());

SELECT
    YEAR(o.OrderDate) AS Year,
    0 AS Month,
    SUM(o.GrandTotal) AS GrandTotal
FROM [Order] o
WHERE
    o.CustomerId = @CustomerId AND
    o.OrderTypeId = 4 AND
    o.IsVoid = 0 AND
    YEAR(o.OrderDate) BETWEEN @CurrentYear - 2 AND @CurrentYear
GROUP BY
    YEAR(o.OrderDate)

which correctly produces the result:

2012, 0, 89.00
2011, 0, 230.00
2010, 0, 450.0

However, if another customer has placed an order just for year 2011, I need to have those 2 other rows generated (albeit with 0 values):

2011, 0, 230.00
2012, 0, 0
2010, 0, 0

How am I suppose to do this correctly? Please note that the report is going to be generated for the last 3 years.

4th UPDATE I modified the code as suggested:

DECLARE @CustomerId INT = 48;
DECLARE @CurrentYear INT = YEAR(GETDATE());

DECLARE @years TABLE (
    yr INT
);
INSERT INTO @years VALUES
    (@CurrentYear),
    (@CurrentYear - 1),
    (@CurrentYear - 2)

This produces a result set as follows (I've checked that and made sure that this is the case).

2012
2011
2010

Then I join the tables (RIGHT JOIN) as follows:

SELECT
    y.yr AS Year,
    0 AS Month,
    SUM(ISNULL(o.GrandTotal, 0)) AS GrandTotal
FROM [Order] o
RIGHT JOIN @years y ON y.yr = YEAR(o.OrderDate)
WHERE
    o.CustomerId = @CustomerId AND
    o.OrderTypeId = 4 AND
    o.IsVoid = 0
GROUP BY
    y.yr

But, consider that the customer hasn't placed an order yet, so, this needs to produce 3 rows with ZERO values. However, none of those solutions suggested does this.

FINAL UPDATE [SOLVED]: The where clause prevented those rows from being in the final result set. So, as Darvin suggested, I just replaced the WHERE clause with AND and the problem is gone.

SELECT
    y.yr AS Year,
    0 AS Month,
    SUM(ISNULL(o.GrandTotal, 0)) AS GrandTotal
FROM [Order] o
RIGHT JOIN @years y ON y.yr = YEAR(o.OrderDate) AND
    o.CustomerId = @CustomerId AND
    o.OrderTypeId = 4 AND
    o.IsVoid = 0
GROUP BY
    y.yr

the reason why the other rows aren't showing is that by putting the filter on the where clause (as opposed to the join), the rows are being excluded after the dataset is created. — Dibstar, Jun 13 '12 at 12:20
Well, I figured it out. However, how am I supposed to solve this? (I need to filter those rows, by the way). — Joe Bank, Jun 13 '12 at 12:21
See amendments to my version below - should do the trick, if not provide some sample data and I'll have a look to see where it's going wrong — Dibstar, Jun 13 '12 at 12:22

score 1 · Answer 1 · answered Jun 13 '12 at 11:39

1

I wrote the sample code in fiddle. http://sqlfiddle.com/#!3/eddfe/9/0 Hope it helps.

The idea is that you create an UDF with all the years and then do outer join to that table.

answered Jun 13 '12 at 11:39

Fedor Hajdu

4,657
3
32
50

Dibstar · Accepted Answer · 2012-06-13T12:19:12.223

0

How about the following:

DECLARE @CustomerId INT = 48; 
DECLARE @CurrentYear INT = DATEPART(YEAR, GETDATE());  

;with years as 
( 
SELECT YEAR(GETDATE()) as yr 
UNION ALL SELECT YEAR(DATEADD(YY,-1,GETDATE())) 
UNION ALL SELECT YEAR(DATEADD(YY,-2,GETDATE())) 
) 
SELECT      
years.yr 
,0 AS Month 
,SUM(ISNULL(o.GrandTotal,0)) AS GrandTotal  
FROM years 
LEFT OUTER JOIN Order o  
ON YEAR(o.OrderDate) = years.yr 
AND o.customerid = @customerid
AND     o.OrderTypeId = 4  
AND     o.IsVoid = 0  
GROUP BY      
years.yr

edited Jun 13 '12 at 12:19

answered Jun 13 '12 at 11:37

Dibstar

2,334
2
24
38

@Mehdi - the where clause was excluding rows wrongly before - try amended version – Dibstar Jun 13 '12 at 12:18
Thank you, this one works. However, would you please describe whats the difference between the first and the second version of the query you wrote? I need to understands this once and forever :) – Joe Bank Jun 13 '12 at 12:23
1

The difference is that the filter criteria have been moved onto the join (LEft outer JOIN order on x = y, a = b etc). The prior version joined the date onto the predefined range and then filtered, the new version only joins cases where the customerID, ordertype and IsvOId are valid. See [here](http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) for another example of this – Dibstar Jun 13 '12 at 12:27

Selecting records for a yearly report (for the past 3 years)

2 Answers2