locked
last 2 months records in sql RRS feed

  • Question

  • User639567535 posted

    I try this SQL query to get current total and also last 3 months records..

    Select distinct 
    tblRv.Owner,
    (Select Count(*) as total from tblvv WHERE MasterID =tblRv.ID and Name <> '') 
    as currentdata
    from tblRe
    inner join tblRv 
    On tblRe.RID = tblRv.RID
    WHERE 
    tblRe.StartDate >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -2, current_timestamp)), 0) and
    tblRe.StartDate >= '2016-07-01 00:00:00' AND 
    tblRe.EndDate <= '2016-07-08 23:59:59' and tblRe.Region = 'uk' and
    tblRv.Owner='Roh' 
    order by tblRv.Owner



    so when i check indiviually write query and check 2016-06-01 and 2016-06-08 dates then this give data also for 5 and 4 months
    ...

    but when i run above this query this show null
    like this

    owner currentdata



    and i want data like this

    owner currentdata(july) june May
    Roh     1                3    4



    also when there will be current month i.e. aug then data must display last 2 months i.e.june july

    Tuesday, July 26, 2016 8:22 AM

All replies

  • User-1404113929 posted

    hi ,

    can you attach sample data..

    thanks,

    murali

    Tuesday, July 26, 2016 12:32 PM
  • User639567535 posted

    hi .. now i try this 

    Select distinct tblRv.Owner , 
    DATENAME(MONTH,tblRe.StartDate) as [Month],
    count(tblVv.VName)
     as currentdata
    from tblRe 
    inner join 
    tblRv on tblRe .RID=tblRv .RID
    inner join 
     tblVv on tblRv.ID=tblVv .MID
    where tblRe .StartDate>=DATEADD(MONTH,-3,GETDATE())
    AND tblRe .EndDate<=GETDATE()
    AND tblRe .Region='uk'
    and tblRv .Owner='roh'
    and VName <> ''
    group by tblRv .Owner,
    DATENAME(MONTH,tblRe .StartDate)
    order by tblRv .Owner
    

    i get record like this 

    Owner Month      currentdata
    roh      July          3
    roh     June        10
    roh     May         6
    
    

    but when i execute query according to june and may date data is 1 for june and 0 for july so i want data like this 

    Owner Month      currentdata
    roh      July          3
    roh     June        1
    roh     May         0

    means in june month data is 1 and in may month data is 0 but query show that 10 and 6 

    Wednesday, July 27, 2016 4:57 AM