Solved

Need to create grouping of data by date

Posted on 2007-08-06
182 Views
Have a problem with a query that I am hoping that someone might be able to help me with

Sales Table

Date            Quanity      ExtCost      ExtPrice  Product Code
Jan 1/06            2            10               15          AA
Jan 4/06            5            30               50          BB
Jan 11/06          7            35               75          AA
Jan 18/06          1             5               10          BB

Jan 12/07          2            12               16          AA
Jan 44/07          5             6                9          BB
Jan 22/07         8           120              160          AA
Jan 24/07          3            60               90          BB

What I am looking for a query that would allow me to do the following

LY = Last Year         TY = This Year

Product Code LYQuanity LYExtCost  LYExtPrice  TYQuanity      TYExtCost  TYPrice
AA      9         45      90      10      132      176
BB      6         35      60      8       66       99

I am hoping that I can pass in 2 date ranges (Date1 >= '2006/01/01' and Date1 <= '2006/01/31') and (Date2 >= '2007/01/01' and Date2 <= '2007/01/31') and some how be able to build a output simular to what you see above.  I hope that this makes sense.

Thanks

James

0
Question by:thumper631

LVL 39

Expert Comment

try like this

Select
isnull(LastYear.ProductCode ,thisYear.ProductCode ),
LastYear.Quanity LYQuanity, LastYear.ExtCost LYExtCost , LastYear.ExtPrice LYExtPrice ,
thisYear.Quanity TYQuanity      , thisYear.ExtCost TYExtCost  , thisYear.ExtPrice TYPrice
from ( Select 1 yearNo,* from sales where date between  '2006/01/01' and '2006/01/31') LastYear
full outer join  ( Select 2 yearNo,* from sales where date between  '2007/01/01' and '2007/01/31') thisYear
on
LastYear.ProductCode = thisYear.ProductCode

0

LVL 39

Accepted Solution

may be you need to do grouping also.
Select
isnull(LastYear.ProductCode ,thisYear.ProductCode ) Prod_Code,
sum(LastYear.Quanity) LYQuanity, sum(LastYear.ExtCost LYExtCost) , sum(LastYear.ExtPrice LYExtPrice) ,
sum(thisYear.Quanity) TYQuanity      , sum(thisYear.ExtCost) TYExtCost  , sum(thisYear.ExtPrice) TYPrice
from ( Select 1 yearNo,* from sales where date between  '2006/01/01' and '2006/01/31') LastYear
full outer join  ( Select 2 yearNo,* from sales where date between  '2007/01/01' and '2007/01/31') thisYear
on
LastYear.ProductCode = thisYear.ProductCode
group by isnull(LastYear.ProductCode ,thisYear.ProductCode )
0

LVL 22

Assisted Solution

This one produced the following:
Select a.procuctcode, lyquantity, LYExtCost, LYExtPrice, tyquantity, tYExtCost, tYExtPrice
FROM (SELECT procuctcode, SUM(quantity) lyquantity, SUM(extcost) LYExtCost, SUM(extprice) LYExtPrice
FROM sales
WHERE saledate >= '2006/01/01' AND saledate <= '2006/01/31'
GROUP BY procuctcode ) a
FULL OUTER JOIN (SELECT procuctcode, SUM(quantity) tyquantity, SUM(extcost) tYExtCost, SUM(extprice) tYExtPrice
FROM sales
WHERE saledate >= '2007/01/01' AND saledate <= '2007/01/31'
GROUP BY procuctcode) b
ON a.procuctcode = b.procuctcode
ORDER BY procuctcode

PR LYQUANTITY  LYEXTCOST LYEXTPRICE TYQUANTITY  TYEXTCOST TYEXTPRICE
-- ---------- ---------- ---------- ---------- ---------- ----------
AA          9         45         90         10        132        176
BB          6         35         60          8         66         99

0

LVL 22

Expert Comment

appari - Sorry - I had the question open while I was testing my select. This one is yours.

Jim
0

Author Comment

Thanks to the two of you.  Jim imply that the solution came from Appari but I use the example that Jim provided.

Thanks again

James
0

Featured Post

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.