# Last 2 years and YTD

SSCrazy

Points: 2835

I am losing my mind..:)

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

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

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.

----------------------------------------------------

• 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

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