#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    116
    Rep Power
    11

    Talking Today MTD and YTD Values


    I have to write a query gathers all sold product from db for the given date as well as for that month and year its simple yes

    Code:
    -- For Given Date 
    Select A.Quantity
    From Products As A
    Where A.Date = '01-01-2012'
    
    -- Month of Given Date
    Select A.Quantity
    From Products As A
    Where Month(A.Date) = Month('01-01-2012') And Year(A.Date) = Year('01-01-2012')
    
    -- Year of Given Date
    Select A.Quantity
    From Products As A
    Where  Year(A.Date) = Year('01-01-2012')
    Now i m stuck with the year as its not a calender year but its a fiscal year which starts from 1 Oct 2011 and ends on 30 Sep 2012 now how can i implement this in year value query need assistance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    declare @year int
    declare @date datetime,@startdate datetime,@enddate datetime
    
    set @date = '2012-01-01'  --
    
    set @year = year(@date) - case when @month < 10 then 1 else 0 end
    
    set @startdate = datefromparts(@year,10,1)
    set @enddate = datefromparts(@year+1,10,1)
    
    select products.Quantity
      from Products
     Where Products.Date >= @startdate
       and Products.Date < @enddate
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    116
    Rep Power
    11
    Thanks swampBoogie this resolves the issue.

IMN logo majestic logo threadwatch logo seochat tools logo