how to display previous month data along with current month data

  • 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

  • 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.

  • 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.

  • 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

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

  • 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

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

    use datediff(dd,date,value) function

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

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

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

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