how to display previous month data along with current month data

Question

Post reply

how to display previous month data along with current month data

ramya.praba

SSC Veteran

Points: 285
More actions
January 30, 2011 at 10:55 pm

#231179

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

k_arumugam82 Valued Member Points: 58 More actions · Answer 1

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.

Ciber2010 Mr or Mrs. 500 Points: 550 More actions · Answer 2

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.

Ciber2010 Mr or Mrs. 500 Points: 550 More actions · Answer 3

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

ramya.praba SSC Veteran Points: 285 More actions · Answer 4

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

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

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

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

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

use datediff(dd,date,value) function

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

vax09 Mr or Mrs. 500 Points: 591 More actions · Answer 7

Use 2 CTE and in First display January and in other -1