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

Last Logins

To anyone who could help:
Let me explain what I'm trying to accomplish. We now have a business
requirement that we need to track last logins on all of our SQL servers. I've
been searching, reading, googling everything that I can think of and there is
no real defined way of doing this. Some suggested using C2 auditing, but that
is too intrusive and caused a performance hit on our servers. My perfect
solution would be to have it either:
1. Go into a new table that I could query
2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
just trying to find a solution)
3. Update a flat file
Does anyone have any suggestions?You can enable success level auditing (without needing C2) so that every time
a connection is established to SQL Server it is logged to the SQL Server
Errorlog ie.
Login succeeded for user 'sa'. Connection: Non-Trusted. or Login succeeded
for user 'WARDYIT01\pward'. Connection: Trusted.
To enable auditing in Enterprise Manager Right Click on your SQL Server
instance and select Properties. Select the security tab and select the
Success or All radio buttons. Note: SQL Server will need to be restart for
this to take effect.
You could then populate a table on a scheduled basis with the contents of
the SQL Server Errorlog and query this table. Below is a quick illustration
of how this could be done.
CREATE TABLE #audit
(
ERRORLOG VARCHAR(2000),
ContinuationRow INT
)
INSERT #audit EXEC xp_readerrorlog
SELECT MAX(SUBSTRING(ERRORLOG, 1, 22)) AS LastLogin
FROM #audit
WHERE ERRORLOG LIKE '%Login succeeded for user ''sa''%'
HTH
- Peter Ward
WARDY IT Solutions
"Big Ern" wrote:
> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers. I've
> been searching, reading, googling everything that I can think of and there is
> no real defined way of doing this. Some suggested using C2 auditing, but that
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?|||Set up a server side tracing for "Audit".. You can use SQL profiler to
generate the scripts..
Jayesh
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:3C4CCD66-CB0B-46DD-9CFE-CE2C73885E54@.microsoft.com...
> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers.
> I've
> been searching, reading, googling everything that I can think of and there
> is
> no real defined way of doing this. Some suggested using C2 auditing, but
> that
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?