If I may be so bold, it looks like you are trying to get PRIOR month quantity and compare to CURRENT month quantity, but your example data is misleading (as in, it implies a relationship between the rows you show).
I think the complexity arises because you are trying to join the results of two different aggretation groupings in the same statement. You can avoid this by using a temp table to hold interim results, then joining the interim result table to itself.
Try something like this:
select
ProductID,
MONTH(DATEADD(m, -1, salesdate)) as priormonth,
year(DATEADD(m, -1, salesdate)) as prioryear,
MONTH(salesdate) as currmonth,
year(salesdate) as curryear,
substring (datename(month, SalesDate), 1, 3)as monthName,
DATENAME(YEAR, salesdate) as yearName,
sum(Qty) as CurQty,
sum(amt) as CurAmt
into #tempStuff
from dbo.Sales
group by productid,
MONTH(DATEADD(m, -1, salesdate)),
year(DATEADD(m, -1, salesdate)),
MONTH(salesdate),
year(salesdate),
substring (datename(month, SalesDate), 1, 3),
DATENAME(YEAR, salesdate)
go
select
x.productid,
isnull(y.curqty, 0) as priorqty,
isnull(y.curamt, 0) as prioramt,
x.curqty,
x.curamt,
x.monthName,
x.yearName
from #tempstuff x
left join #tempstuff y
on x.productid = y.productid
and x.prioryear = y.curryear
and x.priormonth = y.currmonth
order by x.productid, x.curryear, x.currmonth
go
Here's the test table and data I used:
create table dbo.Sales
(
SalesID int identity(1, 1) not null,
ProductID int,
amt float,
Qty int,
SalesDate datetime
)
go
insert into dbo.Sales
(ProductID, amt, Qty, SalesDate)
values
(1,20,2,'12/1/2011'),
(1,30,2,'12/5/2011'),
(1,200,1,'12/6/2011'),
(1,50,2,'1/1/2012'),
(1,100,1,'1/3/2012'),
(2,100,4,'12/2/2011'),
(2,50,4,'12/2/2011'),
(2,100,4,'12/2/2011'),
(2,200,4,'12/2/2011')
go
And here are the results:
productid priorqty prioramt curqty curamt monthName yearName
1 0 0 5 250 Dec 2011
1 5 250 3 150 Jan 2012
2 0 0 16 450 Dec 2011
This is a "brute force" solution. More elegant ones probably exist, I just didn't take time to look for them. Of course, you can sort any way you like. And, I hope I didn't completely misinterpret what you were after!
Good luck!
