August 31, 2010 at 4:30 pm
Hello folks, I'm trying to find a non-brute force method to locating the most recent recurrence of a date pattern. Here's the short version:
I've inherited a system with restricted schema modifications that I need to report against. It's basically an activity chart and a recursion event id. So, I'll have a date like 8/4/2004, and an identifier for quarterly, so it occurs on 8/4/2004, 11/4/2004, 2/4/2005, 5/4/2005, etc. My identifiers occur for yearly, semi-annual, quarterly, and monthly.
Now, building out a table on the fly, running the recursion through iteratively, and loading values is easy enough, and finding the max date less then today from the result set is something I can do. I have a LOT of rows to aggregate here so I'm hoping to find a more mathmatical solution to finding the most recent date of recursion.
I've been fussing with using datediff by month to try to find an integer divide that I could use, but it's not effective. I'm hoping someone out there has found a nice simple computation to do this with.
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
August 31, 2010 at 5:41 pm
I did find my own answer, but I'd love to find a more elegent solution.
The code:
dateadd( mm,
( datediff( mm, BeginDate, getdate()) - CASE WHEN datepart(dd, BeginDate) > datepart(dd,getdate()) then 1 else 0 end)
- ( datediff( mm, BeginDate, getdate())- CASE WHEN datepart(dd, BeginDate) > datepart(dd,getdate()) then 1 else 0 end)
%RecurEveryNumMonths,
BeginDate) AS MostRecentRecursionDate
RecurEveryNumMonths is how often it should recur. 12 = yearly, 6 = semiannual, 3=quarterly, 1= monthly.
The Case on dateparts is in case the day of the month is in the same month and AFTEr today, we need to back it up to the prior recursion.
% = modulo... remainder division, for the unacquainted.
This works rather quick, but as mentioned, up for a better solution.
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
August 31, 2010 at 6:05 pm
I'm not sure what your data looks like, but this is probably going to be the fastest, most efficient way of doing this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 12:00 pm
Rats, I'd hoped someone was going to come back with a way to do it by days that didn't end up tripping on the leap year for multiple year scenarios. Ah well.
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
September 1, 2010 at 2:24 pm
Craig Farrell (9/1/2010)
Rats, I'd hoped someone was going to come back with a way to do it by days that didn't end up tripping on the leap year for multiple year scenarios. Ah well.
Well, if you can concoct some sample data, with the appropriate CREATE TABLE and INSERT statements that demonstrate this problem, you'll be more likely to get someone to look at the problem. (See the first link in my signature for how to do this.) Please include what your expected results should be, based on the sample data provided.
You're already using the date functions, and they are leap-year-aware. It's time to see data that demonstrates the problem to be able to go any further with this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 3:15 pm
Fair enough, and good links, thanks. You make a good point. So, to wit:
create table #tmp
(RepeatIDINT,
BeginDateDATETIME,
RecurEveryNumMonthsINT
)
INSERT INTO #tmp VALUES ( 1, '4/5/2001',3)
INSERT INTO #tmp VALUES ( 2, '8/25/2005',6)
INSERT INTO #tmp VALUES ( 3, '1/2/2002',1)
INSERT INTO #tmp VALUES ( 4, '9/1/2006',12)
INSERT INTO #tmp VALUES ( 5, '7/5/2010',3)
INSERT INTO #tmp VALUES ( 6, '11/3/2004',6)
INSERT INTO #tmp VALUES ( 7, '12/31/2002',6)
INSERT INTO #tmp VALUES ( 8, '8/7/2009',6)
INSERT INTO #tmp VALUES ( 9, '8/31/1994',6)
INSERT INTO #tmp VALUES ( 10, '6/10/2008',6)
So, using this select statement:
DECLARE @DateToCheckAgainst DATETIME
SET @DateToCheckAgainst = '8/22/2010'
select
repeatID,
BeginDate,
dateadd( mm, ( datediff( mm, BeginDate, @DateToCheckAgainst) - CASE WHEN datepart(dd, BeginDate) > datepart(dd,@DateToCheckAgainst) then 1 else 0 end)
- ( datediff( mm, BeginDate, @DateToCheckAgainst)- CASE WHEN datepart(dd, BeginDate) > datepart(dd,@DateToCheckAgainst) then 1 else 0 end)
%RecurEveryNumMonths,
BeginDate) AS MostRecentDate
FROM
#tmp
One of the keys is Line 2: it needs to deal with the fact that the date being computed occurs after today in the same month, so it needs to reverse to the previous recursion.
If you do this by day, instead of month, and try to use that the day component will add the Feb 29s between the original date and this one. So, absent being able to use day calculations, the CASE WHEN datepart(... section is the only way I can compute getting out of that.
So, yes, I agree the datetime functions are definately leap year aware, and that pushes the recursion date back by a day for every 4 years it crosses, which is what screws me up using that method. Approaching it from the year method ends up with having to back it up as well, as a cross from 1/4/2010 to 12/29/2009 is a 1 year cross for the date function, causing other forms of mathmatical havoc.
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
September 1, 2010 at 8:17 pm
Okay Craig, how's this?
declare @DateToCheckAgainst datetime;
--SET @DateToCheckAgainst = DateAdd(day, DateDiff(day, 0, GetDate()), 0);
SET @DateToCheckAgainst = '20100822';
SELECT repeatID,
BeginDate,
MostRecentDate = max(MostRecentDate)
FROM ( SELECT repeatID,
BeginDate,
RecurEveryNumMonths,
MostRecentDate = DateAdd(month, (N-1)*RecurEveryNumMonths, BeginDate)
FROM #tmp
CROSS JOIN TALLY
WHERE N <= DateDiff(month, BeginDate, @DateToCheckAgainst)+1) dates
WHERE MostRecentDate <= @DateToCheckAgainst
GROUP BY RepeatID, BeginDate
ORDER BY RepeatID;
This assumes that you have a tally table; if not, please see this article[/url] for how to build one and how else you can use it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 8:48 pm
Hm, that tally table looks interesting, and I'll explore it more shortly, I'm not immediately familiar with it. The crossjoin computations seems like it takes a lot more passes to result at the same value. I appreciate the effort though, and I'll keep it in mind if I need a different solution. Thanks!
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
September 1, 2010 at 8:52 pm
Craig Farrell (9/1/2010)
Hm, that tally table looks interesting, and I'll explore it more shortly, I'm not immediately familiar with it. The crossjoin computations seems like it takes a lot more passes to result at the same value. I appreciate the effort though, and I'll keep it in mind if I need a different solution. Thanks!
No problem.
At least is doesn't "end up tripping on the leap year for multiple year scenarios."
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 8:56 pm
WayneS (9/1/2010)
No problem.
At least is doesn't "end up tripping on the leap year for multiple year scenarios."
True, but neither does the mm one, just gotta deal with that stupid 'in the same month' case statement smack in the middle of it. Rather ugly. 🙂
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
September 1, 2010 at 9:21 pm
Craig Farrell (9/1/2010)
WayneS (9/1/2010)
No problem.
At least is doesn't "end up tripping on the leap year for multiple year scenarios."
True, but neither does the mm one, just gotta deal with that stupid 'in the same month' case statement smack in the middle of it. Rather ugly. 🙂
Yes, but as I mentioned earlier, it's probably the most efficient that you're going to see. Even what I coded is both slower and has more disk IO (not slower by much on the sample data, but I'm sure that if you are running against 10,000+ rows you'll see a bigger difference). Those date functions are tweaked for maximum performance, and they are hard to beat. If you can keep it so that it's using just variables or columns in the row you're processing, it just isn't going to get much better. But if you need a quick range, combining it with a tally table won't be able to be beat either.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply