3 Replies Latest reply: Jul 26, 2016 12:42 PM by Onno van Knotsenburg RSS

    Last 2 month record in SQL

    suer user

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

      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 between dateadd(m, -2, getdate()) and getdate()

      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

       

       

      when i exe this show me like this

       

       

      OwnerName currentdata

      Roh  1

      Roh  2

      Roh  3

      Roh  5

      and when i check individually write query and check from date 2016-07-01 and todate 2016-07-30 dates then this show me data 3 and 2016-06-01 00:00:00 and 2016-06-31 23:59:59show me data 1 and 2016-05-01 00:00:00 ,2016-05-31 23:59:59 show me data 0
      so i want data like this

      owner july june may

      roh  3   1   0

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