CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Joe Bank
Joe Bank

Reputation: 663

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

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

Upvotes: 2

Views: 1772

Answers (2)

Dibstar
Dibstar

Reputation: 2364

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

Upvotes: 0

Fedor Hajdu
Fedor Hajdu

Reputation: 4695

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.

Upvotes: 1

Related Questions