Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to display previous month data along with current month data Expand / Collapse
Author
Message
Posted Sunday, January 30, 2011 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, January 30, 2011 11:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 11:59 AM
Points: 4, Visits: 19
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
Posted Monday, January 31, 2011 12:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 3:43 AM
Points: 8, Visits: 196
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
Posted Monday, January 31, 2011 12:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 3:43 AM
Points: 8, Visits: 196
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
Posted Monday, January 31, 2011 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 1, 2011 12:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 30, 2014 9:13 PM
Points: 959, Visits: 2,885
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
Posted Tuesday, February 1, 2011 1:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 31, 2014 1:00 AM
Points: 464, Visits: 1,036
If the month is january then how can we display previous month?


use datediff(dd,date,value) function




--SQLFRNDZ
Post #1057042
Posted Thursday, October 6, 2011 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:11 PM
Points: 27, Visits: 345
Use 2 CTE and in First display January and in other -1
Post #1186501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse