0

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

Edited by M.Waqas Aslam: n/a

3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by M.Waqas Aslam
0

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

1

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!

Votes + Comments
thank you so very much , for this great help
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.