March 13, 2017 at 1:24 pm
jcelko212 32090 - Thursday, March 9, 2017 3:35 PMI would add an ordinal year to the calendar. This is a variant on the business days trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
ordinal_business_day INTEGER NOT NULL,
...);INSERT INTO Calendar VALUES ('2007-04-05', 42);
INSERT INTO Calendar VALUES ('2007-04-06', 43); -- Good Friday
INSERT INTO Calendar VALUES ('2007-04-07', 43);
INSERT INTO Calendar VALUES ('2007-04-08', 43); -- Easter Sunday
INSERT INTO Calendar VALUES ('2007-04-09', 44);
INSERT INTO Calendar VALUES ('2007-04-10', 45); -- Tuesday, back to workTo compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.ordinal_business_day - C1.ordinal_business_day -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';This saves having to scan all of the rows within the range to get a count. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company.
When someone says they have a complicated rule for calculating durations, I think of various physical calendars. There’s over 150 of them in the GAAP, the last time I looked. My personal favorite, however, is crop calendars. Many decades ago I worked for a cigarette company.
They buy tobacco futures and their calendar is a 16 month “crop year†of 30 days per month. We try doing this with a regular calendar and a hell of a lot of really messy COBOL programming. Contracts require actions every month by both parties (i.e. In month five of the 2016 crop, we want to do some sampling and require the tobacco be of such and such a quality and quantity level). The crop calendar does not match up with the common era calendar very well. In fact it has to be re-adjusted if the weather changes one season. And you can have more than one crop overlapping in its lifecycle, from planting the delivery of the factory. The calendar is set up in the future, not the past.
The best way to handle complicated calendars that I found is to use this sort of look up table approach. It’s fast, it ports to any other implementation of SQL We seem to assume that everybody’s a SQL Server user in their shop; these days nobody works with just one database. If nothing else, they have to get stuff from somebody else outside their business was probably on Oracle, DB2, or post grass or who knows what.
That's a nice story, but the code does not solve the problem presented. That would only count business days instead of calculating whole years. There's no need to include holidays and definitively no need to read tables.
March 13, 2017 at 3:50 pm
jcelko212 32090 - Thursday, March 9, 2017 3:35 PMI would add an ordinal year to the calendar. This is a variant on the business days trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
ordinal_business_day INTEGER NOT NULL,
...);INSERT INTO Calendar VALUES ('2007-04-05', 42);
INSERT INTO Calendar VALUES ('2007-04-06', 43); -- Good Friday
INSERT INTO Calendar VALUES ('2007-04-07', 43);
INSERT INTO Calendar VALUES ('2007-04-08', 43); -- Easter Sunday
INSERT INTO Calendar VALUES ('2007-04-09', 44);
INSERT INTO Calendar VALUES ('2007-04-10', 45); -- Tuesday, back to workTo compute the business days from '2007-04-05', Thursday before Easter in 2007 to '2007-04-10', the next Tuesday:
SELECT (C2.ordinal_business_day - C1.ordinal_business_day -1) AS business_day_cnt
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05'
AND C2.cal_date = '2007-04-10';This saves having to scan all of the rows within the range to get a count. The -1 is to avoid counting the current day as one of the business days, but you might have a different rule in your company.
When someone says they have a complicated rule for calculating durations, I think of various physical calendars. There’s over 150 of them in the GAAP, the last time I looked. My personal favorite, however, is crop calendars. Many decades ago I worked for a cigarette company.
They buy tobacco futures and their calendar is a 16 month “crop year†of 30 days per month. We try doing this with a regular calendar and a hell of a lot of really messy COBOL programming. Contracts require actions every month by both parties (i.e. In month five of the 2016 crop, we want to do some sampling and require the tobacco be of such and such a quality and quantity level). The crop calendar does not match up with the common era calendar very well. In fact it has to be re-adjusted if the weather changes one season. And you can have more than one crop overlapping in its lifecycle, from planting the delivery of the factory. The calendar is set up in the future, not the past.
The best way to handle complicated calendars that I found is to use this sort of look up table approach. It’s fast, it ports to any other implementation of SQL We seem to assume that everybody’s a SQL Server user in their shop; these days nobody works with just one database. If nothing else, they have to get stuff from somebody else outside their business was probably on Oracle, DB2, or post grass or who knows what.
That's nice info but you've not solved the problem that was defined by the OP. Also, your calendar table does not contain the 100 years that you specified. I provided one for you and I provided the DDL and data that resembles the OP's post but bigger for performance testing purposes. Please read that post and try again. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2017 at 4:27 pm
Jeff Moden - Monday, March 13, 2017 3:50 PMThat's nice info but you've not solved the problem that was defined by the OP. Also, your calendar table does not contain the 100 years that you specified. I provided one for you and I provided the DDL and data that resembles the OP's post but bigger for performance testing purposes. Please read that post and try again. Thanks.
For fun, I decided to test the original method (with only NewMethod not CurrentMethod) and time it and compare with your results. I got CPU time = 2797 ms, elapsed time = 3675 ms. So your system must be beefier than mine.
Next, I ran the method that I had suggested (the modified version that uses 365.25 + .001) using the cast and division:
CPU time = 1297 ms, elapsed time = 3970 ms.
So faster CPU time in a single run of each, but worse elapsed time (likely due to my slow machine). But still pretty good time since it is doing 5 casts followed by division. And I did a comparison of the data from the original "NewMethod" to the "SimpleMethod" and the values appear to match. I probably shouldn't compare a single run of each of those queries to be a "good" test though. I should be clearing the plan cache for this, eh?
Since the OP indicated that the values are stored as datetime in the original table (back in a post on the first page), 2 of those casts can be removed if more optimizations were required.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 13, 2017 at 7:49 pm
bmg002 - Monday, March 13, 2017 4:27 PMFor fun, I decided to test the original method (with only NewMethod not CurrentMethod) and time it and compare with your results. I got CPU time = 2797 ms, elapsed time = 3675 ms. So your system must be beefier than mine.
Next, I ran the method that I had suggested (the modified version that uses 365.25 + .001) using the cast and division:
CPU time = 1297 ms, elapsed time = 3970 ms.So faster CPU time in a single run of each, but worse elapsed time (likely due to my slow machine). But still pretty good time since it is doing 5 casts followed by division. And I did a comparison of the data from the original "NewMethod" to the "SimpleMethod" and the values appear to match. I probably shouldn't compare a single run of each of those queries to be a "good" test though. I should be clearing the plan cache for this, eh?
Since the OP indicated that the values are stored as datetime in the original table (back in a post on the first page), 2 of those casts can be removed if more optimizations were required.
The complex CASE expression I used was but an example to highlight the need I had of encapsulating some code for repetitive use which was resolved with Phil Parkin's approach to using an iTVF. The simplification of the year difference calculation that you provided was a really nice bonus. I really do appreciate the input that all of you provided which only proves that we are all able to make contributions to further enlighten us all.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
March 14, 2017 at 5:32 am
bmg002 - Monday, March 13, 2017 4:27 PMJeff Moden - Monday, March 13, 2017 3:50 PMThat's nice info but you've not solved the problem that was defined by the OP. Also, your calendar table does not contain the 100 years that you specified. I provided one for you and I provided the DDL and data that resembles the OP's post but bigger for performance testing purposes. Please read that post and try again. Thanks.
For fun, I decided to test the original method (with only NewMethod not CurrentMethod) and time it and compare with your results. I got CPU time = 2797 ms, elapsed time = 3675 ms. So your system must be beefier than mine.
Next, I ran the method that I had suggested (the modified version that uses 365.25 + .001) using the cast and division:
CPU time = 1297 ms, elapsed time = 3970 ms.So faster CPU time in a single run of each, but worse elapsed time (likely due to my slow machine). But still pretty good time since it is doing 5 casts followed by division. And I did a comparison of the data from the original "NewMethod" to the "SimpleMethod" and the values appear to match. I probably shouldn't compare a single run of each of those queries to be a "good" test though. I should be clearing the plan cache for this, eh?
Since the OP indicated that the values are stored as datetime in the original table (back in a post on the first page), 2 of those casts can be removed if more optimizations were required.
You machine is probably faster than mine because I don't return anything to the screen while testing except the performance results.
While we're at it, I combined Arron's optimization of duplicated code with Luis' optimization using CONVERT and Scott's optimization of simplification and added a minor tweak of my own and came up with the following.
CREATE FUNCTION dbo.AgeInYearsSwappable
/**********************************************************************************
Purpose:
Given two dates in any order, calculate the difference in the dates by years.
-----------------------------------------------------------------------------------
Programmers Notes:
1. This is a high performance iTVF (Inline Table Valued Function), which is faster
than an equivalent Scalar Function and just as fast as inline code. Please
refer to the following link for proof of that.
http://www.sqlservercentral.com/articles/T-SQL/91724/
2. When the output is dumped to a variable to take disk and screen performance
out of the picture, it executes a million row table of two dates in ~820ms.
Usage Examples:
--===== Basic syntax
SELECT AgeInYears
FROM dbo.AgeInYearsSwappable(@pDate1,@pDate2)
;
--===== Use against a table
SELECT AgeInYears
FROM dbo.SomeTable st
CROSS APPLY dbo.AgeInYearsSwappable(st.SomeDate1,st.SomeDate2)
;
--===== Test Harness
SET STATISTICS TIME ON;
DECLARE @Bitbucket INT;
SELECT @Bitbucket = age.AgeInYears
FROM dbo.TestTable dt
CROSS APPLY dbo.AgeInYearsSwappable(dt.FirstDate,dt.SecondDate) age;
SET STATISTICS TIME OFF;
GO 10
-----------------------------------------------------------------------------------
Revision History:
Rev 00 - 11 Mar 2017 - Group effort by the folks at the following link.
- https://www.sqlservercentral.com/Forums/1863521/
- Formalized code - Jeff Moden
**********************************************************************************/
--===== Declare the I/O for this function
(--======= These dates can be out of order.
@pDate1 DATE
,@pDate2 DATE
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Return the corrected age in years.
SELECT AgeInYears =
CASE --Process dates that are in the expected order
WHEN @pDate1 <= @pDate2
THEN DATEDIFF(yy,@pDate1,@pDate2)
- CASE WHEN CONVERT(CHAR(5),@pDate1,1) > CONVERT(CHAR(5),@pDate2,1)
THEN 1
ELSE 0
END
ELSE --Process dates that are in the reverse of the expected order
DATEDIFF(yy,@pDate2,@pDate1)
- CASE WHEN CONVERT(CHAR(5),@pDate2,1) > CONVERT(CHAR(5),@pDate1,1)
THEN 1
ELSE 0
END
END
;
After testing for accuracy using the data from Aaron's original post, I executed against a million rows using the test harness listed in the header. Here's the output from that...
Beginning execution loop
SQL Server Execution Times:
CPU time = 826 ms, elapsed time = 826 ms.
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 816 ms.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 810 ms.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 813 ms.
SQL Server Execution Times:
CPU time = 827 ms, elapsed time = 813 ms.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 811 ms.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 817 ms.
SQL Server Execution Times:
CPU time = 811 ms, elapsed time = 812 ms.
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 818 ms.
SQL Server Execution Times:
CPU time = 826 ms, elapsed time = 812 ms.
Batch execution completed 10 times.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2017 at 5:50 am
Jeff, that's pretty amazing! I have truly blown away by the performance this provides. Using an iTVF is something I would not have considered. This also provides me with ideas for a number of other scenarios that would be benefited from this approach. Thanks for your thoroughness!!
Aaron
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
March 14, 2017 at 9:47 am
Jeff, that makes sense then. I had it set to results to grid, so I did get the million rows plus those messages. Well, on one of the 2 SSMS windows I had open. I have a bunch of stuff open and I have a bad habit of not closing SSMS for weeks on end. Due to this, I sometimes get a "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.". First time I saw this I thought my SQL server ran out of memory but google calmed my nerves and indicated that SSMS ran out of memory to display the results.
But back on topic, I like how there are so many different ways to write a query in SQL. And that they all have different benefits. The original was easy for any developer to pick up and run with it (be it a SQL developer or not). My method was short and nice for a "one time run" thing, but repeated use makes it not all that good. Jeff's method is nice and reusable and fast. And while Joe's method didn't solve the original problem, it could be modified to work with the original problem by adding in some more math. The nice thing about the calendar table method is that you can then use it to determine working days between 2 dates. But that was not what the OP wanted but could be helpful in the future.
I know we have a similar table set up, but all it stores is holiday information. Weekends can be figured out from SQL pretty easily and we don't care (currently) about a date count, just which days are holidays. Although that is being phased out too.
EDIT - I forgot people. sgmunson, ScottPletcher, Luis Cazares, Phil Parkin all had good solutions as well. I did not test performance or validity nor see any performance or validity testing for their solutions though, but from reading them, they look like they should provide valid results as well and are quite nice. Sorry for excluding you in the original reply.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 14, 2017 at 10:58 am
bmg002 - Tuesday, March 14, 2017 9:47 AMJeff, that makes sense then. I had it set to results to grid, so I did get the million rows plus those messages. Well, on one of the 2 SSMS windows I had open. I have a bunch of stuff open and I have a bad habit of not closing SSMS for weeks on end. Due to this, I sometimes get a "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.". First time I saw this I thought my SQL server ran out of memory but google calmed my nerves and indicated that SSMS ran out of memory to display the results.But back on topic, I like how there are so many different ways to write a query in SQL. And that they all have different benefits. The original was easy for any developer to pick up and run with it (be it a SQL developer or not). My method was short and nice for a "one time run" thing, but repeated use makes it not all that good. Jeff's method is nice and reusable and fast. And while Joe's method didn't solve the original problem, it could be modified to work with the original problem by adding in some more math. The nice thing about the calendar table method is that you can then use it to determine working days between 2 dates. But that was not what the OP wanted but could be helpful in the future.
I know we have a similar table set up, but all it stores is holiday information. Weekends can be figured out from SQL pretty easily and we don't care (currently) about a date count, just which days are holidays. Although that is being phased out too.EDIT - I forgot people. sgmunson, ScottPletcher, Luis Cazares, Phil Parkin all had good solutions as well. I did not test performance or validity nor see any performance or validity testing for their solutions though, but from reading them, they look like they should provide valid results as well and are quite nice. Sorry for excluding you in the original reply.
People keep talking about using a Calendar table to solve the "correct age in years" problem but I've never seen anyone pull it off never mind with good performance. The challenge is still open to anyone who may want to try.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2017 at 11:13 am
Jeff Moden - Tuesday, March 14, 2017 10:58 AMPeople keep talking about using a Calendar table to solve the "correct age in years" problem but I've never seen anyone pull it off never mind with good performance. The challenge is still open to anyone who may want to try.
Oh I completely agree with you. I'd never try to solve a "correct age in years" problem with a calendar table. I can see that it is possible, but there is no benefit of using it.
The calendar table concept for solving "correct age in years" can just as easily be solved by converting it to an int and subtracting, or using datediff, or any of the methods on this post and those would still be required.
The calendar table that Joe provided could cause problems too as weekends and holidays were marked with the same date counter. So, hypothetically, lets say that we have the following 2 rows in the table (along with every row inbetween):
('2017-03-19',122),('2016-03-18',122)
I did not accurately calculate the "122" part, but just picked a number as an example. the 19th is a sunday and the 18th is a saturday. In this example you would expect a 1 year difference. But lets change it to:
('2017-03-17',122),('2016-03-18',122)
here we should NOT get a 1 year difference, but comparing the second column, we'd expect to. The calendar table counting net working days does not help with calculating number of years difference between 2 dates. You'd be using the date column for this and in that case you'd just be making extra work.
Plus with a calendar table you are limited to the dates within the calendar. So hypothetically you go back 10 years and ahead 10 years and your query will work great.... for 10 years and then it will suddenly fail. And in 10 years when it worked the day before but suddenly failed today, my first thought likely isn't going to be "we need to add dates to the calendar table". It'll be investigating why the SP/report/view/application broke and tracing through everything only to find out we ran out of dates.
So I guess my previous comment was a little off. Joe's example does not really help to solve this particular problem, but could be helpful if you needed to know the number of working days between 2 dates. Still has the problem of being a fixed range though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 14, 2017 at 11:25 am
Yep, agreed. I've actually done similar to what Joe suggests for the work day thing. I have two columns with a "work day" enumeration... one where the weekend days (for example) either has the work day number the same as the Friday and another for where they have the same work day number as the Monday. That works very accurately and can be made to work fairly quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2017 at 11:31 am
jcelko212 32090 - Thursday, March 9, 2017 3:35 PMA calendar table is needed for complex things (but not really for routine calendar calcs). But your approach of an ordinal business day seems very problematic and error-inducing to me. For example, if I designate a new holiday later, I have to renumber every row from that date forward. Or, less common, what about official half days off? That is perhaps rare, but some companies do have them.
Actually, getting a new holiday or getting rid of an old holiday is a single update statement on the calendar. You just renumber things. I never ran into the half day problem. If you want another problem that is hell work for commercial shipping companies that have to deal with foreign seaports.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 14, 2017 at 11:45 am
Jeff Moden - Saturday, March 11, 2017 8:42 PMjcelko212 32090 - Thursday, March 9, 2017 3:35 PMA calendar table is needed for complex things (but not really for routine calendar calcs). But your approach of an ordinal business day seems very problematic and error-inducing to me. For example, if I designate a new holiday later, I have to renumber every row from that date forward. Or, less common, what about official half days off? That is perhaps rare, but some companies do have them.
Actually, getting a new holiday or getting rid of an old holiday is a single update statement on the calendar. You just renumber things. I never ran into the half day problem. If you want another problem that is hell work for commercial shipping companies that have to deal with foreign seaports.
While I agree it should be a single update statement, lets say you have a few million rows in there (to keep it future proof). Just hypothetically. You change the second date in that table from a holiday to not a holiday, you now have to update the few million rows all at once. Thankfully, most calendar tables should remain fairly static and you can run the updates on company downtime presumably.
Most of the holidays we consider are stat holidays so it is pretty easy to figure out which ones should and should not apply. But it is still a manual process for entering those days. Weekends we can automate quite easily, but holidays somebody has to manually enter which is a bit of a pain.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 14, 2017 at 2:02 pm
"While I agree it should be a single update statement, lets say you have a few million rows in there (to keep it future proof)."
2 million days for a calendar table? Why not 4 million days and solve the y10k problem at the same time?
March 14, 2017 at 2:04 pm
Jeff Moden - Tuesday, March 14, 2017 11:25 AMYep, agreed. I've actually done similar to what Joe suggests for the work day thing. I have two columns with a "work day" enumeration... one where the weekend days (for example) either has the work day number the same as the Friday and another for where they have the same work day number as the Monday. That works very accurately and can be made to work fairly quickly.
This points out another problem with age. In Asia, the convention is to give your age for the year that you're currently working on, but in the West. We talk about what you've finished doing. I prefer the Asian system, so I can say things like "I just finish the 'the live to 70' project and I'm working on the 71 project. It gives me a sense of not having finished living yet😉
Please post DDL and follow ANSI/ISO standards when asking for help.
March 14, 2017 at 2:13 pm
patrickmcginnis59 10839 - Tuesday, March 14, 2017 2:02 PM"While I agree it should be a single update statement, lets say you have a few million rows in there (to keep it future proof)."
2 million days for a calendar table? Why not 4 million days and solve the y10k problem at the same time?
Ok. So we keep the numbers smaller and only hold 10 years back and 10 years forward. In 10 years I need to update the table. I make that table HUGE and I don't need to worry about the date the table runs out of days as I'll have long run out of days myself by then. It'll be new guys problem :P.
But I've seen some crazy things implemented... we still have a Numbers table with 8000 numbers in it and it is just used for some math that I think never goes over 1000 and I'm pretty sure it is under 100 for 99% of the applications that use it. One of my low priority tasks is to turn that into a CTE which won't be hard, but other higher priority things keep getting in the way.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply