November 28, 2016 at 12:15 pm
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
November 28, 2016 at 12:28 pm
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/
November 28, 2016 at 1:41 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 29, 2016 at 2:08 pm
I ended up using this. Thanks
WHERE 1=1 AND I.InvoiceDate >= DATEADD(yy,-3,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
November 29, 2016 at 2:13 pm
Oops, right, YEAR - 3 not YEAR - 2.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 29, 2016 at 2:25 pm
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!
November 29, 2016 at 2:29 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 29, 2016 at 10:46 pm
Is this for a Gregorian calendar year, or a fiscal year?
November 30, 2016 at 11:08 am
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.
----------------------------------------------------
December 12, 2016 at 5:59 am
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
December 12, 2016 at 8:46 am
paulo.margarido (12/12/2016)
Or ... replaceMMartin1 (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
December 12, 2016 at 9:33 am
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply