January 19, 2012 at 1:53 am
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
January 19, 2012 at 2:00 am
This should be easy if you post the DDL and sample data in a readily usable format
You will get a tested solution back
Please check the link in my signature on how to do it
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 19, 2012 at 4:25 am
Hi
You can try this.
declare @tab table (prodid int,amt int,qty int,salesdate date)
insert into @tab values (1,1000,10,'2011-11-01'),
(2,1500,15,'2011-12-01'),
(3,2000,20,'2012-01-01'),
(4,2500,25,'2012-02-01');
with cte as(
select prodid,amt,qty,salesdate,ROW_NUMBER () over (order by prodid) rownum from @tab)
select a.prodid,a.qty,a.salesdate,a.amt,isnull(b.amt,0) prevamt from cte a left outer join cte b on
a.rownum = b.rownum+1
Regards
Siva Kumar J.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply