Complicated Query Want Some Help

[M.Waqas Aslam]

Hello Every one !
i am trying to make query to show the growth of sale of the product month wise ,i have one table name Sales .having following fields , SalesID,ProductID,amt,Qty,SalesDate
i use this query to show my records

select ProductID,Amt,Qty,substring (datename(month,SalesDate ),1,3)as months,DATENAME(YEAR,salesdate ) as years

this query shows my records in this way
-------------------------------------------
ProductID*****Amt*****Qty*****months*****years
----------------------------------------------
1****************400********6**********Dec**********2011
2****************300********4**********Jan**********2012
----------------------------------------------
I WANT TO SHOW MY RECORDS IN THIS WAY
-------------------------------------------
ProductID*****Amt*****Per.MonthQty*****Cur.MonthQty*****months*****years
------------------------------------------------------------------------
1****************400**********0****************6*************Dec******2011
2****************300**********6****************4*************Jan******2012
------------------------------------------------------------------------

sorry if you are feeling bad after reading it ,
can any one help me to solve this prob :(

Regards

M.Waqas Aslam

[sufyan2011]

There is nothin to feel bad about this question. Your question is not complete, you divided the Qty into two parts but didn't specify the formula for them.

select ProductID,Amt,Qty,(formula???) as Per.MonthQty, (formula???) as Cur.MonthQty ,substring (datename(month,SalesDate ),1,3)as months,DATENAME(YEAR,salesdate ) as years

[BitBlt]

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!

[M.Waqas Aslam]

still prob is same :( , i am using mssql server 2008 , is there any wrong with it ?

[M.Waqas Aslam]

Thanks BitBlt :) thanks for your help , prob is solved :) yaaa hooo