March 14, 2017 at 2:42 pm
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.
Yes, a single UPDATE statement every time a non-work day change is done. Any slip up and you've invalidated every date calc using the effected dates. :;shudder:: Not me, I'm not going anywhere near that approach. My non-work-days calendar is much easier, and as for having to read the rows, one would have to be adding up decades to need more than two pages of data at once.
Btw, my company does do international shipments and we use port codes whenever possible (because then users don't have to select the city, we can derive it from the port code).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 15, 2017 at 9:38 am
jcelko212 32090 - Tuesday, March 14, 2017 2:04 PMJeff 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😉
That's nice, but what does that have to do with this topic, or the price of tea in China for that matter?
March 15, 2017 at 6:21 pm
The single update on a calendar table to renumber the working day enumerations is a no brainer, folks. And, like anything else, it needs to be written correctly. It's not a fuss and I keep the update code in my "utility belt". If you need a 2 million day table, you might be doing something wrong. 😉
But, that's not what this tread is actually about. I'm still waiting for someone to write the code to solve the aging problem using a "calendar" table. I personally won't attempt it because, in my simple mind, it simply isn't worth doing considering the simplicity, accuracy, and performance of the code to solve the aging problem without it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2017 at 6:23 pm
jcelko212 32090 - Tuesday, March 14, 2017 2:04 PMJeff 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😉
Heh... now THAT's funny. It's been a tough day and I needed the laugh. Thanks, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2017 at 7:40 pm
Jeff Moden - Tuesday, March 14, 2017 11:25 AMThat's nice, but what does that have to do with this topic, or the price of tea in China for that matter?
Perhaps what the metric "AGE" means? DUH. What is the first thing a data model does? A: define terms, scales, unit of measurement, etc.was my approach. What ids yours? Assumptions without documentation? After 30+ years in the trade , I know this ;mindset will screw me and my clients..
Please post DDL and follow ANSI/ISO standards when asking for help.
March 15, 2017 at 9:02 pm
jcelko212 32090 - Tuesday, March 14, 2017 2:04 PMJeff Moden - Tuesday, March 14, 2017 11:25 AMThat's nice, but what does that have to do with this topic, or the price of tea in China for that matter?
Perhaps what the metric "AGE" means? DUH. What is the first thing a data model does? A: define terms, scales, unit of measurement, etc.was my approach. What ids yours? Assumptions without documentation? After 30+ years in the trade , I know this ;mindset will screw me and my clients..
While that may be true from your standpoint, it is a bit less interesting to those not living in that one country. What would be much more interesting is you posting a solution for the original problem using the method that you proposed. In other words, you proposed that the problem of calculating the correct age in whole years for a date span marked by two given dates could be done using a calendar table. We'd love to see the "English" version of that. Personally, I don't believe it can be done and, if I'm proven incorrect there, I'm positive that it won't be done with anything close to the performance realized by the simple calculations posted by some on this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2017 at 8:52 am
Jeff Moden - Wednesday, March 15, 2017 6:21 PMThe single update on a calendar table to renumber the working day enumerations is a no brainer, folks. And, like anything else, it needs to be written correctly. It's not a fuss and I keep the update code in my "utility belt". If you need a 2 million day table, you might be doing something wrong. 😉But, that's not what this tread is actually about. I'm still waiting for someone to write the code to solve the aging problem using a "calendar" table. I personally won't attempt it because, in my simple mind, it simply isn't worth doing considering the simplicity, accuracy, and performance of the code to solve the aging problem without it.
OK, what update do I do to set Christmas Eve as half a day off? Or for two hours on June 24 due to a plant shutdown?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 16, 2017 at 9:23 am
ScottPletcher - Thursday, March 16, 2017 8:52 AMJeff Moden - Wednesday, March 15, 2017 6:21 PMThe single update on a calendar table to renumber the working day enumerations is a no brainer, folks. And, like anything else, it needs to be written correctly. It's not a fuss and I keep the update code in my "utility belt". If you need a 2 million day table, you might be doing something wrong. 😉But, that's not what this tread is actually about. I'm still waiting for someone to write the code to solve the aging problem using a "calendar" table. I personally won't attempt it because, in my simple mind, it simply isn't worth doing considering the simplicity, accuracy, and performance of the code to solve the aging problem without it.
OK, what update do I do to set Christmas Eve as half a day off? Or for two hours on June 24 due to a plant shutdown?
What if you store your date counters as numeric with 3 decimal places. so a half day would work out to be previous value + 0.5. 2 hours (presuming an 8 hour work day) would be 0.25. Where I think it would get fun is when specific groups get 1/2 day off. You'd either need multiple columns for those special groups or a second table to determine when those groups get extra time off.
Where I work, we work the entire day on Christmas Eve, but it is a very relaxed day for most (not the DBA mind you... but production is pretty slow as they get the following week off without pay unless they use their vacation). But we do get Christmas and Boxing day and New years off.
But does a DBA really ever get a day off? Even on my days off my phone sometimes chirps at me that there was a deadlock (even though nobody shoudl be there) or the worst was when a set of databases failed over and didn't come up successfully so they were down... at 2:00 AM... and people were going to be coming in to work at 6:30 AM. That was a fun little battle.
But I feel we are getting quite off topic. I think for this particular problem, we have provided multiple solutions. I am curious as to which the OP went with.
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 16, 2017 at 9:32 am
ScottPletcher - Thursday, March 16, 2017 8:52 AMJeff Moden - Wednesday, March 15, 2017 6:21 PMThe single update on a calendar table to renumber the working day enumerations is a no brainer, folks. And, like anything else, it needs to be written correctly. It's not a fuss and I keep the update code in my "utility belt". If you need a 2 million day table, you might be doing something wrong. 😉But, that's not what this tread is actually about. I'm still waiting for someone to write the code to solve the aging problem using a "calendar" table. I personally won't attempt it because, in my simple mind, it simply isn't worth doing considering the simplicity, accuracy, and performance of the code to solve the aging problem without it.
OK, what update do I do to set Christmas Eve as half a day off? Or for two hours on June 24 due to a plant shutdown?
I would add a column with working hours to the calendar table to take care of Christmas Eve, but I don't think I'd do it for the plant shutdown, that should instead be part of a regular schedule that's already in your calendar. If its an unplanned shutdown, I wouldn't put it in the calendar table.
March 16, 2017 at 11:40 am
patrickmcginnis59 10839 - Thursday, March 16, 2017 9:32 AMScottPletcher - Thursday, March 16, 2017 8:52 AMJeff Moden - Wednesday, March 15, 2017 6:21 PMThe single update on a calendar table to renumber the working day enumerations is a no brainer, folks. And, like anything else, it needs to be written correctly. It's not a fuss and I keep the update code in my "utility belt". If you need a 2 million day table, you might be doing something wrong. 😉But, that's not what this tread is actually about. I'm still waiting for someone to write the code to solve the aging problem using a "calendar" table. I personally won't attempt it because, in my simple mind, it simply isn't worth doing considering the simplicity, accuracy, and performance of the code to solve the aging problem without it.
OK, what update do I do to set Christmas Eve as half a day off? Or for two hours on June 24 due to a plant shutdown?
I would add a column with working hours to the calendar table to take care of Christmas Eve, but I don't think I'd do it for the plant shutdown, that should instead be part of a regular schedule that's already in your calendar. If its an unplanned shutdown, I wouldn't put it in the calendar table.
I just have total work time for the day. It could be any value less than 8 hours. Days with extended work times -- 12-, 16-, 20-hour days -- are handled differently. I just can't imagine trying to do that with a rolling total. Again, ::shudder::.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 16, 2017 at 11:57 am
ScottPletcher - Thursday, March 16, 2017 11:40 AMpatrickmcginnis59 10839 - Thursday, March 16, 2017 9:32 AMScottPletcher - Thursday, March 16, 2017 8:52 AMJeff Moden - Wednesday, March 15, 2017 6:21 PMThe single update on a calendar table to renumber the working day enumerations is a no brainer, folks. And, like anything else, it needs to be written correctly. It's not a fuss and I keep the update code in my "utility belt". If you need a 2 million day table, you might be doing something wrong. 😉But, that's not what this tread is actually about. I'm still waiting for someone to write the code to solve the aging problem using a "calendar" table. I personally won't attempt it because, in my simple mind, it simply isn't worth doing considering the simplicity, accuracy, and performance of the code to solve the aging problem without it.
OK, what update do I do to set Christmas Eve as half a day off? Or for two hours on June 24 due to a plant shutdown?
I would add a column with working hours to the calendar table to take care of Christmas Eve, but I don't think I'd do it for the plant shutdown, that should instead be part of a regular schedule that's already in your calendar. If its an unplanned shutdown, I wouldn't put it in the calendar table.
I just have total work time for the day. It could be any value less than 8 hours. Days with extended work times -- 12-, 16-, 20-hour days -- are handled differently. I just can't imagine trying to do that with a rolling total. Again, ::shudder::.
It would be interesting, having a rolling total of business hours in a column for a date calendar. Say you wanted to calculate business hours between two dates, wouldn't you then just subtract the earlier rolling total from the later rolling total using the appropriate rows from the calendar table? Sure the rolling totals themselves need recalced, but pretty much the entire calendar would be recalced with a change anyways right?
Obviously I don't want to get in the way of yalls quality time with Joe, just knocking around the topic a bit.
March 17, 2017 at 6:01 pm
While the conversation on a workdays cumulative column and some of its permutations is certainly a good one, it has nothing to do with the original post of how to accurately count the number of whole years (age). All that came about because Joe brought it up with his calendar table and we've still to see how he would use it to accurately calculate age in whole years between two dates.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2017 at 8:37 pm
jcelko212 32090 - Tuesday, March 14, 2017 2:04 PMJeff Moden - Tuesday, March 14, 2017 11:25 AMThat's nice, but what does that have to do with this topic, or the price of tea in China for that matter?
Perhaps what the metric "AGE" means? DUH. What is the first thing a data model does? A: define terms, scales, unit of measurement, etc.was my approach. What ids yours? Assumptions without documentation? After 30+ years in the trade , I know this ;mindset will screw me and my clients..
After 30 years in the trade, you should also know that when someone of your reputation makes a suggestion as to how to solve a problem, you should have code at the ready to demonstrate your suggested solution. Have you come up with code for what you previously suggested? If so, please post it. If not, please stop hi-jacking this thread. And, by the way... learn how to post on this forum. You have me as the author of the "tea in China" quote above and it was not I.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2017 at 1:54 am
Aaron N. Cutshall - Wednesday, March 8, 2017 11:12 AMI have business logic for date comparisons that go beyond the typical DATEDIFF functionality. For example, when determining the number of years between two dates:
declare @DateTest table (FirstDate date, SecondDate date, ExpectedResult smallint);
insert into @DateTest (FirstDate, SecondDate, ExpectedResult) values
('2012-03-10', '2013-02-18', 0), ('2012-03-10', '2013-03-10', 1), ('2012-03-10', '2013-03-20', 1), ('2012-02-29', '2014-02-28', 1), ('2012-03-10', '2013-08-15', 1), ('2012-02-29', '2014-03-01', 2),
('2013-02-18', '2012-03-10', 0), ('2013-03-10', '2012-03-10', 1), ('2013-03-20', '2012-03-10', 1), ('2014-02-28', '2012-02-29', 1), ('2013-08-15', '2012-03-10', 1), ('2014-03-01', '2012-02-29', 2);SELECT FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
CASE WHEN SecondDate >= FirstDate THEN
CASE WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) < DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) < DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate) -1)
WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) >= DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) > DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) < DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) < DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate) -1)
WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) >= DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) > DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate)) END
END AS NewMethod
FROM @DateTest;As the complex CASE statement is executed in multiple places, the natural thing to do is to place this into a user-defined function. However, we all know how such scalar UDFs affect performance by turning set-based logic into RBAR operations. Another thought I had was to encapsulate the logic in a table-valued UDF or even a view except that the selection of the records that produce both FirstDate and SecondDate are two different tables that are joined by logic unique to each query that needs the date comparison.
Does someone knows how to create non-scalar, deterministic functions like DATEDIFF? I appreciate any insight that may help me from replicating this quite ugly code throughout my system.
How about this?
SELECT FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
Y.N RightMethod
FROM @DateTest dt
cross apply (select top 1 N from [Tally] t
where DATEADD(yy, N, FirstDate) <= SecondDate
order by N desc
) Y
;
_____________
Code for TallyGenerator
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply