April 24, 2012 at 7:19 pm
So, thanks to all that helped with the first problem I had. Much like an onion, I am now presented with another issue. My hope in the first query issue, was to get a list of employees that fell under a specific supervisor and result in employee info with supervisor email.
The plan is to have a SSIS job run a month before the employee's hiredate anniversary to remind them to do their evaluation of said employee.
I initially thought DATEADD(MM,1,emphiredate) would work, but that only increased the month, keeping the year the original year. So then I thought of comparing DATEPART(M,emphiredate)=MONTH(getdate()) to get the anniversary month. That did it, but then when I went -1 or +1 on either the DATEPART or GETDATE statement, everything was fine until month 01 or 12 (depending on -1 or +1). When Month 12 came, I get zero results from my query because there is no month 13, same for month 01, as there is no month 00.
Here is the test code I am working with. (Hopefully in a usable format this time):-D
WITH emp AS
(
SELECT 1000 AS empid, 1500 AS supid, 'Sparrow' AS ln, 'Jack' AS fn, 'Jack.Sparrow@abc.com' AS email, '1990-04-01' AS emphiredate
UNION ALL
SELECT 1001, 1500, 'Teach', 'Edward', 'Edward.Teach@abc.com', '1991-12-01'
UNION ALL
SELECT 1002, 1500, 'Roberts', 'Bart', 'Bart.Roberts@abc.com', '1991-01-16'
UNION ALL
SELECT 1003, 1500, 'Kidd', 'William', 'William.Kidd@abc.com', '2003-06-02'
UNION ALL
SELECT 1004, 1500, 'Morgan', 'Henry', 'Henry.Morgan@abc.com', '2010-03-01'
UNION ALL
SELECT 1500, 2000, 'Aubrey', 'Jack', 'Jack.Aubrey@abc.com', '2008-12-16'
UNION ALL
SELECT 2000, NULL, 'Avery','Henry', 'Henry.Avery@abc.com', '2001-11-20'
)
SELECT a.empid, a.supid, a.ln, a.fn, b.email, a.emphiredate, DATEPART(mm,a.emphiredate)as ReviewMonth
FROM emp a
Left outer Join emp b
on a.supid = b.empid
where DATEPART(MM,a.emphiredate)=MONTH('2012-12-1')+1/*getdate()-1)*/
I have the getdate statement commented out, and using a hard coded date to test the parameters.
Any thoughts? I sure hope to get better at this stuff. I really enjoy it.
Thanks,
Steve E.
April 24, 2012 at 7:27 pm
First off, why is this an SSIS job? Seems to me like a simple SP run by SQL Server agent could locate employees based on their hire date, then send an email to appropriate managers to remind them of the upcoming review, would be the right approach.
And I think the function you need to look at is DATEDIFF.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 24, 2012 at 7:33 pm
It can be a bit confusing, but basically what you end up having to do is clean off the 'year' and replace it.
There's a few approaches, but direct datediff/dateadd math typically runs the fastest. There are exceptions but you've really got to dig to find the cases and desperately need a few milliseconds per million rows for them to matter.
Check out the results of the following:
SELECT
empid, supid,
ln, fn,
email,
emphiredate,
dateadd(yy, -1 * datediff(yy, 0, emphiredate), emphiredate) AS NoYearDate,
DATEADD( yy, datediff( yy, 0, getdate()), dateadd(yy, -1 * datediff(yy, 0, emphiredate), emphiredate)) AS ThisYearsAnniversary
FROM
emp
From there you can determine if the date is already past today, add in another year.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 25, 2012 at 8:53 am
Evil Kraig F (4/24/2012)
It can be a bit confusing, but basically what you end up having to do is clean off the 'year' and replace it.
Thanks for the example....it makes sense....confusing, but I follow the logic.
dwain.c (4/24/2012)
First off, why is this an SSIS job? Seems to me like a simple SP run by SQL Server agent...
Thanks for the advice....frankly, I hadnt thought of a SP doing it. I'll look into it.
I appreciate the help.
Steve E.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply