Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Need to create grouping of data by date

Posted on 2007-08-06
Medium Priority
Last Modified: 2010-03-20
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.



Question by:thumper631
  • 2
  • 2
LVL 39

Expert Comment

ID: 19642547
try like this

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
LastYear.ProductCode = thisYear.ProductCode

LVL 39

Accepted Solution

appari earned 1600 total points
ID: 19642552
may be you need to do grouping also.
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
LastYear.ProductCode = thisYear.ProductCode
group by isnull(LastYear.ProductCode ,thisYear.ProductCode )
LVL 22

Assisted Solution

JimBrandley earned 400 total points
ID: 19642651
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

-- ---------- ---------- ---------- ---------- ---------- ----------
AA          9         45         90         10        132        176
BB          6         35         60          8         66         99

LVL 22

Expert Comment

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


Author Comment

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

Thanks again


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question