Last 2 years and YTD

  • mbrady5

    SSCrazy

    Points: 2835

    I am losing my mind..:)

    Can someone please help me with how to implement:

    YTD Plus the prior 2 Full Years (In where clause)

    Where Invoice date includes Current YTD as well as the prior 2 years

    Thanks

  • Michael L John

    One Orange Chip

    Points: 25829

    DECLARE @TodaysDate datetime = getdate()

    DECLARE @StartDate datetime = DATEFROMPARTS ( DATEPART(year, @TodaysDate) - 2, 1, 1)

    SELECT @TodaysDate, @StartDate

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • ScottPletcher

    SSC Guru

    Points: 98315

    Something like this should do it:

    SELECT

    SUM(CASE WHEN YEAR(Invoice_date) = YEAR(curr_year_jan_01) - 2 THEN amount ELSE 0 END) AS Total_2_Years_Ago,

    SUM(CASE WHEN YEAR(Invoice_date) = YEAR(curr_year_jan_01) - 1 THEN amount ELSE 0 END) AS Total_1_Year_Ago,

    SUM(CASE WHEN YEAR(Invoice_date) = YEAR(curr_year_jan_01) THEN amount ELSE 0 END) AS Total_Current_Year

    FROM (

    SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS curr_year_jan_01

    ) AS assign_alias_names

    WHERE Invoice_date >= DATEADD(YEAR, -2, curr_year_jan_01)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • mbrady5

    SSCrazy

    Points: 2835

    I ended up using this. Thanks

    WHERE 1=1 AND I.InvoiceDate >= DATEADD(yy,-3,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

  • ScottPletcher

    SSC Guru

    Points: 98315

    Oops, right, YEAR - 3 not YEAR - 2.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jacob Wilkins

    One Orange Chip

    Points: 27856

    Are we sure about this?

    If the requirement is YTD and 2 full years prior, then for right now that would be 2016 YTD, all of 2015, and all of 2014.

    The expression the OP showed will return anything greater than or equal to January 1, 2013 if run right now. That's YTD for 2016 and the full 3 years prior.

    You were right the first time Scott, methinks 🙂

    Cheers!

  • ScottPletcher

    SSC Guru

    Points: 98315

    Double oops, you're right. I made sure, too, that I went back to Jan 1 00:00 to get the full year.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • DesNorton

    SSC-Insane

    Points: 22929

    Is this for a Gregorian calendar year, or a fiscal year?

  • MMartin1

    One Orange Chip

    Points: 27501

    The DATEFROMPARTS function does not work in the 2008 edition.

    As an alternative ...

    declare @fromDate date, @toDate date ;

    select @fromDate=

    dateadd(m,0,cast(Cast(year(getdate())-2 as char(4)) as date))

    select @toDate = cast(getdate() as date) ;

    Select @fromDate , @todate

    Next you can employ these variables in your select statement.

    Something like

    select

    year(orderDate ) as [year],

    sum(orderAmount) as totalAmount

    from

    yourTable

    Where

    orderDate >=@fromDate and orderDate < @toDate

    group by

    year(orderDate)

    Make sure to make a note somewhere in your report that the latest year value should be interpreted as a year to date, just in case someone is not paying attention.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • paulo.margarido

    Ten Centuries

    Points: 1245

    Or ... replace

    MMartin1 (11/30/2016)


    declare @fromDate date, @toDate date ;

    select @fromDate=

    dateadd(m,0,cast(Cast(year(getdate())-2 as char(4)) as date))

    select @toDate = cast(getdate() as date) ;

    Select @fromDate , @todate

    by

    declare @fromDate date, @toDate date ;

    select @fromDate=

    DATEADD(M,0,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0))

    select @toDate = cast(getdate() as date) ;

    Select @fromDate , @todate

  • drew.allen

    SSC Guru

    Points: 76705

    paulo.margarido (12/12/2016)


    Or ... replace

    MMartin1 (11/30/2016)


    declare @fromDate date, @toDate date ;

    select @fromDate=

    dateadd(m,0,cast(Cast(year(getdate())-2 as char(4)) as date))

    select @toDate = cast(getdate() as date) ;

    Select @fromDate , @todate

    by

    declare @fromDate date, @toDate date ;

    select @fromDate=

    DATEADD(M,0,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0))

    select @toDate = cast(getdate() as date) ;

    Select @fromDate , @todate

    DATEADD(M, 0, <anything>) is an identity operator. It will always return the same value as the final parameter if it is a date or datetime field. It will convert a string to a datetime field when possible, but if you are converting a string, it's much more straightforward to just use the CAST/CONVERT functions.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • paulo.margarido

    Ten Centuries

    Points: 1245

    sorry, (copy+paste typo)

    correct sugestion is simply

    declare @fromDate date, @toDate date ;

    select @fromDate=

    DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0)

    select @toDate = cast(getdate() as date) ;

    Select @fromDate , @todate

Viewing 12 posts - 1 through 12 (of 12 total)

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