Last year comparable Business Day

/**
DESCRIPTION
Okay, I have an app that I need to compare information from two dates
in. The two dates are whatever date is passed to it, and its equivalent
business day from last year. For instance, today is January 10th,
2006. January 10th is the tuesday of the second w in 2006. I need to
be able to compare it the tuesday of the second FULL w in 2005 (Full
w being the catch).
So, if you give it 1/6/06, it will output 1/7/05
1/2/06 => 1/3/05
12/31/06 => 1/1/06
I have a function below that is almost there, but it runs into some
hiccups towards the end of the year. Try inputing 12/31/05, and you
will see what I mean.
That being said, here is my function. You wil have to doctor a few
lines due to word wrap.
FUNCTION
**/
CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @.DATELY datetime,
@.WEEKTY int,
@.WEEKLY int,
@.WEEKDAYTY int,
@.WEEKDAYLY int,
@.WDSUNDAYLY datetime,
@.WDSATURDAYLY datetime,
@.BOY datetime,
@.BOLY datetime
SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
SET @.BOY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.datetime)))
SET @.BOLY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
SET @.WEEKTY = DATEPART("wk", @.datetime)
SET @.WEEKLY = DATEPART("wk", @.DATELY)
BEGIN
IF @.WEEKTY <> @.WEEKLY
IF @.WEEKTY > @.WEEKLY
SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
ELSE IF @.WEEKTY < @.WEEKLY
SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
END
SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
6),@.BOLY)
BEGIN
IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
(@.WEEKTY <> @.WEEKLY)
BEGIN
SET @.DATELY = DATEADD("d",7,@.DATELY)
END
END
SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
BEGIN
IF @.WEEKDAYTY <> @.WEEKDAYLY
IF @.WEEKDAYTY > @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
@.DATELY)
END
RETURN @.DATELY
ENDOn 10 Jan 2006 14:11:54 -0800, Brian Baumann wrote:

>/**
>DESCRIPTION
>Okay, I have an app that I need to compare information from two dates
>in. The two dates are whatever date is passed to it, and its equivalent
>business day from last year. For instance, today is January 10th,
>2006. January 10th is the tuesday of the second w in 2006. I need to
>be able to compare it the tuesday of the second FULL w in 2005 (Full
>w being the catch).
(snip)
Hi Brian,
I didn't take the time to disect your code. Instead of going over the
top with complex date calculations, why not simply create a Calendar
table? (See www.aspfaq.com/2519). Your problem can then be solved by a
simple join on same w, same wday, and year = year - 1.
(snip)
>I have a function below that is almost there, but it runs into some
>hiccups towards the end of the year. Try inputing 12/31/05, and you
>will see what I mean.
I didn't test it, but might this not be related to an omission in the
specification? After all, some years have a 53rd w and others don't.
Or they do, but with different wdays in it. Your spec doesn't say how
to handle that case.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> I didn't take the time to disect your code. Instead of going over the
> top with complex date calculations, why not simply create a Calendar
> table? (See www.aspfaq.com/2519). Your problem can then be solved by a
> simple join on same w, same wday, and year = year - 1.
I don't think so. Brian said first full w. I interpret this as
that the corresponding day to 2007-01-08 will be 2006-01-09, but
the corresponding day to 2008-01-08, will be 2007-01-01, because
in 2008 New Years Day is on a Tuesday, so the first full w starts
on 2008-01-07. (Assuming here that you ws starts on Monday.)
In the two w-numbering system, w 1 is eiher the w that
Jan 1st falls on (this is what SQL Server uses) or the w that
Jan 4th falls on (this is the ISO standard for w numbering).
Of course Brian could number the ws in his calendar table after
his own liking, but then he still left with the problem that to number
them. There are also funny things, with New Years Eve mapping back
to New Years Day the same year. It would be a very strange calendar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 10 Jan 2006 23:30:58 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
>I don't think so.
(snip)
>Of course Brian could number the ws in his calendar table after
>his own liking,
Hi Erland,
That's what I meant. I wanted to add this to my message, but forgot
about it and clicked Send.
Thanks for the addition!
Hugo Kornelis, SQL Server MVP|||So if it is not in the first full w (starting Monday?) then it is the
Monday of the next w?
So if Tues is the day in year x, and w 1 in year x-1 is Tues, then the
day you want is the next Monday?
What about holidays that land on business days?
William Stacey [MVP]
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1136931114.105675.287240@.g44g2000cwa.googlegroups.com...
> /**
> DESCRIPTION
> Okay, I have an app that I need to compare information from two dates
> in. The two dates are whatever date is passed to it, and its equivalent
> business day from last year. For instance, today is January 10th,
> 2006. January 10th is the tuesday of the second w in 2006. I need to
> be able to compare it the tuesday of the second FULL w in 2005 (Full
> w being the catch).
> So, if you give it 1/6/06, it will output 1/7/05
> 1/2/06 => 1/3/05
> 12/31/06 => 1/1/06
> I have a function below that is almost there, but it runs into some
> hiccups towards the end of the year. Try inputing 12/31/05, and you
> will see what I mean.
> That being said, here is my function. You wil have to doctor a few
> lines due to word wrap.
> FUNCTION
> **/
> CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
> RETURNS DATETIME
> AS
> BEGIN
> DECLARE @.DATELY datetime,
> @.WEEKTY int,
> @.WEEKLY int,
> @.WEEKDAYTY int,
> @.WEEKDAYLY int,
> @.WDSUNDAYLY datetime,
> @.WDSATURDAYLY datetime,
> @.BOY datetime,
> @.BOLY datetime
>
> SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
> SET @.BOY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.datetime)))
> SET @.BOLY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
> SET @.WEEKTY = DATEPART("wk", @.datetime)
> SET @.WEEKLY = DATEPART("wk", @.DATELY)
> BEGIN
> IF @.WEEKTY <> @.WEEKLY
> IF @.WEEKTY > @.WEEKLY
> SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
> ELSE IF @.WEEKTY < @.WEEKLY
> SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
> END
> SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
> SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
> 6),@.BOLY)
>
> BEGIN
> IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
> (@.WEEKTY <> @.WEEKLY)
> BEGIN
> SET @.DATELY = DATEADD("d",7,@.DATELY)
> END
> END
>
> SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
> SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
> BEGIN
> IF @.WEEKDAYTY <> @.WEEKDAYLY
> IF @.WEEKDAYTY > @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
> ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
> @.DATELY)
> END
>
> RETURN @.DATELY
> END
>|||This whole approach will fail when you get to lunar/solar holidays,
like Easter. Want to see the code for Easter after the Y2K
non-leapyear? Want to see the code for Easter before the Y2K
non-leapyear?
The best way is to build a Calendar table with a coumn for "equivalent
business day from last year" values. Build a decade or two with a
spreadsheet after talking to the accounting department.|||Not sure who to respond to on this, so I will respond to my original
post and hopefully it will get to everyone.
I figured out what to do, and might I add, it is way simpler than I had
ever imagined. Kinda makes me wanna kick myself in butt.
DATEADD("wk",-52,@.DATE)
Works like a champ and hasn't failed. Always gets me day for day of a
comparable look last year.|||Brian,
One thing you will want to take a look at is what happens in the last two
days of the year. Dec 31st will return Jan 1st of the current year in a
normal year, and Jan 2nd in a leap year. Dec 30th will return Jan 1st
during leap year.
Also, no matter what day it is you will never get back Jan 1st of the
previous year. Following a leap year you wont be able to get back Jan 2nd
either.
This may be perfectly acceptable for your business need. It is something
that needs to be understood before using this approach, however.
Jusat
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1137162876.033640.264260@.z14g2000cwz.googlegroups.com...
> Not sure who to respond to on this, so I will respond to my original
> post and hopefully it will get to everyone.
> I figured out what to do, and might I add, it is way simpler than I had
> ever imagined. Kinda makes me wanna kick myself in butt.
> DATEADD("wk",-52,@.DATE)
> Works like a champ and hasn't failed. Always gets me day for day of a
> comparable look last year.
>