how to display previous month data along with current month data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » how to display previous month data along with...

how to display previous month data along with current month data

Rate Topic

Display Mode

Topic Options

Author

Message

ramya.praba

Posted Sunday, January 30, 2011 10:55 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2011 5:55 AM
Points: 9, Visits: 39

table Mst_Region
----------------

Region_Id RegionName
----------------------
1 central
2 South
3 north

Table Voc_Trans_Details
-----------------------

Trans_id Region_id Month_value Answer Year_value
___________________________________________________________

1 1 7 53.333 2010
2 1 6 21.89 2010
3 3 7 75.98 2010

select B.RegionName,
avg(D.Answer) as Answer
from Mst_Region B,
Voc_Trans_Details D where D.Month_Value=7 and Region_id=1 group by RegionName

My OUTPUT is
____________

RegionName Answer
__________________
Central 53.333

I want OUTPUT like
------------------

RegionName PreviousmnthAnswer Answer
______________________________________
Central 21.89 53.333

Post #1055981

k_arumugam82

Posted Sunday, January 30, 2011 11:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 2:18 AM
Points: 4, Visits: 16

Hi Ramya,
I hope the below query should do the magic.

Select Current.RegionName, Previous.Answer, Current.Answer From
(select B.RegionName,
avg(D.Answer) as Answer
from Mst_Region B,
Voc_Trans_Details D where D.Month_Value=7 and Region_id=1 group by RegionName) Current JOIN (select A.RegionName,
avg(C.Answer) as Answer
from Mst_Region A,
Voc_Trans_Details C where C.Month_Value=6 and Region_id=1 group by RegionName) Previous ON Current.RegionName = Previous.RegionName

Pls contact me at k_arumugam82@yahoo.co.in, if you have any other queries.

Post #1055985

Ciber2010

Posted Monday, January 31, 2011 12:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 3:03 AM
Points: 8, Visits: 194

Hi,

Am Venky i executed that query which u posted,but its showing error for that Current and Prevoius words,I didnt get that ,can u please explain me those.

Thanks in Advance.

Post #1055993

Ciber2010

Posted Monday, January 31, 2011 12:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 3:03 AM
Points: 8, Visits: 194

Hi Ramya,

Just try Below query ,this is same as k_arumugam82 had sent,but bit modifications.

Select Curren.RegionName, Previous.Answer, Curren.Answer From
(select B.RegionName,
avg(D.Answer) as Answer
from Mst_Region B,
Voc_Trans_Details D where D.Month_Value=7 and D.Region_id=1 group by RegionName)as Curren JOIN
(select A.RegionName,
avg(C.Answer) as Answer
from Mst_Region A,
Voc_Trans_Details C where C.Month_Value=6 and A.Region_Id=1 group by RegionName) as Previous ON Curren.RegionName = Previous.RegionName

Post #1055997

ramya.praba

Posted Monday, January 31, 2011 3:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2011 5:55 AM
Points: 9, Visits: 39

If the month is january then how can we display previous month?

Post #1056058

tfifield

Posted Tuesday, February 01, 2011 12:53 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:10 PM
Points: 958, Visits: 2,873

What you want to do is fairly easy if you use a calendar table. Here is an article I published on the subject last July and it has an example of returning prior periods to the current period (in this case the periods are months).
http://www.sqlservercentral.com/articles/T-SQL/70482/
Todd Fifield

Post #1057027