Sales Total by Multiple Date Range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sales Total by Multiple Date Range

Author

Topic

SysMan
Starting Member

4 Posts

Posted - 2003-06-19 : 08:40:51

I have a problem linking four tables, Product, Sales, Class and Owner (this is a simplification of the actual implementation but illustrates the issues).

Product has columns ID, Description, ClassID, OwnerID

Sales has columns ID, ProdID, InvValue, InvDate

Class has columns ID, Name

Owner has columns ID, Name

Sales contains a list of invoice dates and invoice values where Sales.ProdID references Product.ID

I want to create a stored procedure that returns Product.ID, Product.Description, Class.Name, Owner.Name and total Sales Value across four quarters, e.g. Q1=Jan-01 to Mar-31, Q2=Apr-01 to Jun-30, etc....

Looking through the forum I've been able to create a table which gives me total Sales Value by quarter by Product.ID using the following (assume @Q1, @Q2, etc are the quarter date boundaries) :

select s.ProdID,
sum (case when s.InvDate >= @Q1 and s.InvDate < @Q2 then s.InvValue else 0 end) as q1,
sum (case when s.InvDate >= @Q2 and s.InvDate < @Q3 then s.InvValue else 0 end) as q2,
sum (case when s.InvDate >= @Q3 and s.InvDate < @Q4 then s.InvValue else 0 end) as q3,
sum (case when s.InvDate >= @Q4 and s.InvDate < @Q5 then s.InvValue else 0 end) as q4
from Sales as s
group by s.ProdID

This works great.

What I can't work out is how to join this into the select that produces the final output (I'm also only interested in returning rows from Product which have a particular ClassID) :

select p.ID, p.Description, c.Name, o.Name, [sales by quarter result goes here]
from Product as p
join Company as c on c.ID=p.ClassID
join Owner as o on o.ID=p.OwnerID
where p.ClassID=1
order by p.Description

I'd appreciate any help.

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-19 : 08:45:28

select p.ID, p.Description, c.Name, o.Name, q1, q2, q3, q4 from Product as p
join Company as c on c.ID=p.ClassID
join Owner as o on o.ID=p.OwnerID
left outer join
(

select s.ProdID,
sum (case when s.InvDate >= @Q1 and s.InvDate < @Q2 then s.InvValue else 0 end) as q1,
sum (case when s.InvDate >= @Q2 and s.InvDate < @Q3 then s.InvValue else 0 end) as q2,
sum (case when s.InvDate >= @Q3 and s.InvDate < @Q4 then s.InvValue else 0 end) as q3,
sum (case when s.InvDate >= @Q4 and s.InvDate < @Q5 then s.InvValue else 0 end) as q4
from Sales as s
group by s.ProdID

) s on s.prodid = p.id
where p.ClassID=1
order by p.Description

?

-------
Moo.

SysMan
Starting Member

4 Posts

Posted - 2003-06-19 : 09:07:56

Thanks mr_mist that did the trick.

Wish I'd found this forum a week ago :)

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-19 : 09:38:16

quote:

Thanks mr_mist that did the trick.

Wish I'd found this forum a week ago :)

Okay no probs, there is probably a more efficient way to do it, and if there is someone else will post it later :D

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources