May 3, 2018 at 11:15 am
patrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 11:20 am
Jeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
May 3, 2018 at 11:23 am
To the OP:
I'd suggest not over-complicating what you have unless/until it's actually needed. Why spends hours doing something that may never be applicable or useful? If the conditions change, then rethink it, but completely, from scratch. You can design as flexible a system as you ever likely to need at that time, when time must be spent on it anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2018 at 11:30 am
ScottPletcher - Thursday, May 3, 2018 11:23 AMTo the OP:I'd suggest not over-complicating what you have unless/until it's actually needed. Why spends hours doing something that may never be applicable or useful? If the conditions change, then rethink it, but completely, from scratch. You can design as flexible a system as you ever likely to need at that time, when time must be spent on it anyway.
I think this is reasonable if the OP can suggest what his business process is or provide any specs. I know in my case (within this threads context) is that the day of week thing was unambiguous but inseparable from the day of week as it was a repeating schedule that the business process was based on.
Could I have made a case for ditching this, of course, but at the time (and still), I don't have any downsides to present to the folks in the actual domain of enterprise (end users making the request).
May 3, 2018 at 11:58 am
patrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 12:31 pm
Jeff Moden - Thursday, May 3, 2018 11:58 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
So set a constraint in the table that the two day name columns can't have the same value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2018 at 12:32 pm
Jeff Moden - Thursday, May 3, 2018 11:58 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
LOL sorry, you're the incorrect one here and let me tell you why. The OP asked how to determine the number of days between two weekdays, and according to his examples he disambiguates the situation with Friday and Monday, based on his required answer, three days between those, he's looking for the next weekday following. If he wants Monday and Monday to be 7 I would not complain a bit, I would instead congratulate him on enumerating the relatively low combination of weekdays as suggested by someone else which I agree with.
I've literally coded the same thing except in my case I supplied the next week day (exclusive) and the previous week day (inclusive). This stuff gets worked out. Heck you're asking me how to identify which two Mondays you're talking about? I wasn't talking about your two Mondays. You literally said you don't do weekday stuff. I was talking about the OP's two Mondays, and if he said no he'd rather it be 7 then he's the man and 7 it is!
Now what am I missing here?
May 3, 2018 at 2:32 pm
patrickmcginnis59 10839 - Thursday, May 3, 2018 12:32 PMJeff Moden - Thursday, May 3, 2018 11:58 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
LOL sorry, you're the incorrect one here and let me tell you why. The OP asked how to determine the number of days between two weekdays, and according to his examples he disambiguates the situation with Friday and Monday, based on his required answer, three days between those, he's looking for the next weekday following. If he wants Monday and Monday to be 7 I would not complain a bit, I would instead congratulate him on enumerating the relatively low combination of weekdays as suggested by someone else which I agree with.
I've literally coded the same thing except in my case I supplied the next week day (exclusive) and the previous week day (inclusive). This stuff gets worked out. Heck you're asking me how to identify which two Mondays you're talking about? I wasn't talking about your two Mondays. You literally said you don't do weekday stuff. I was talking about the OP's two Mondays, and if he said no he'd rather it be 7 then he's the man and 7 it is!
Now what am I missing here?
What you're missing is that there's no guarantee that what the OP states will always be true even for the OP. And, there's no way to know from the given data.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 3:38 pm
Jeff Moden - Thursday, May 3, 2018 2:32 PMpatrickmcginnis59 10839 - Thursday, May 3, 2018 12:32 PMJeff Moden - Thursday, May 3, 2018 11:58 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
LOL sorry, you're the incorrect one here and let me tell you why. The OP asked how to determine the number of days between two weekdays, and according to his examples he disambiguates the situation with Friday and Monday, based on his required answer, three days between those, he's looking for the next weekday following. If he wants Monday and Monday to be 7 I would not complain a bit, I would instead congratulate him on enumerating the relatively low combination of weekdays as suggested by someone else which I agree with.
I've literally coded the same thing except in my case I supplied the next week day (exclusive) and the previous week day (inclusive). This stuff gets worked out. Heck you're asking me how to identify which two Mondays you're talking about? I wasn't talking about your two Mondays. You literally said you don't do weekday stuff. I was talking about the OP's two Mondays, and if he said no he'd rather it be 7 then he's the man and 7 it is!
Now what am I missing here?
What you're missing is that there's no guarantee that what the OP states will always be true even for the OP. And, there's no way to know from the given data.
There's no guarantee that anybody can produce a working spec for any program, and honestly I get your trepidation and experiences and I don't have any answers for you there. I'm all about the errors, I've made quite the study of that sort of thing, and the question does arise, what do you do in the face of possible errors? Isn't there a point where you have to cowboy up and get some programming done? At some point somebody needs to earn their paycheck.
Really, with this one we have maybe 3 edge conditions when you think of it. You have a day, then another day that follows, you have a day, then another day that precedes, you have the same day twice. Obviously, either day can be null but couldn't we "not null" those out?
I get you, how do we know that any given OP does his due diligence? I don't have any answers there, do we give everybody an exam upon user registration? I didn't mean to get into some existential thread about whether we should program at all, the general shortage of skilled programmers, the crisis in software specifications etc, I was really looking for specifics here with this particular rather routine looking problem. Really, the OP took care of two of the edges, we could have just pointed out the Monday Monday thing, was it really all that impossible in your understanding that the OP could make a decision there? Does our industry actually lack the confidence of even that level of analysis? In the face of that sort of analysis paralysis how can we get anything done?
May 3, 2018 at 4:15 pm
patrickmcginnis59 10839 - Thursday, May 3, 2018 3:38 PMJeff Moden - Thursday, May 3, 2018 2:32 PMpatrickmcginnis59 10839 - Thursday, May 3, 2018 12:32 PMJeff Moden - Thursday, May 3, 2018 11:58 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
LOL sorry, you're the incorrect one here and let me tell you why. The OP asked how to determine the number of days between two weekdays, and according to his examples he disambiguates the situation with Friday and Monday, based on his required answer, three days between those, he's looking for the next weekday following. If he wants Monday and Monday to be 7 I would not complain a bit, I would instead congratulate him on enumerating the relatively low combination of weekdays as suggested by someone else which I agree with.
I've literally coded the same thing except in my case I supplied the next week day (exclusive) and the previous week day (inclusive). This stuff gets worked out. Heck you're asking me how to identify which two Mondays you're talking about? I wasn't talking about your two Mondays. You literally said you don't do weekday stuff. I was talking about the OP's two Mondays, and if he said no he'd rather it be 7 then he's the man and 7 it is!
Now what am I missing here?
What you're missing is that there's no guarantee that what the OP states will always be true even for the OP. And, there's no way to know from the given data.
There's no guarantee that anybody can produce a working spec for any program, and honestly I get your trepidation and experiences and I don't have any answers for you there. I'm all about the errors, I've made quite the study of that sort of thing, and the question does arise, what do you do in the face of possible errors? Isn't there a point where you have to cowboy up and get some programming done? At some point somebody needs to earn their paycheck.
Really, with this one we have maybe 3 edge conditions when you think of it. You have a day, then another day that follows, you have a day, then another day that precedes, you have the same day twice. Obviously, either day can be null but couldn't we "not null" those out?
I get you, how do we know that any given OP does his due diligence? I don't have any answers there, do we give everybody an exam upon user registration? I didn't mean to get into some existential thread about whether we should program at all, the general shortage of skilled programmers, the crisis in software specifications etc, I was really looking for specifics here with this particular rather routine looking problem. Really, the OP took care of two of the edges, we could have just pointed out the Monday Monday thing, was it really all that impossible in your understanding that the OP could make a decision there? Does our industry actually lack the confidence of even that level of analysis? In the face of that sort of analysis paralysis how can we get anything done?
The point that I'm trying to make is that there is no chance of data integrity here even if the requirements are crystal clear and well understood. That's what I'm trying to warn the OP about.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 4:32 pm
Jeff Moden - Thursday, May 3, 2018 4:15 PMpatrickmcginnis59 10839 - Thursday, May 3, 2018 3:38 PMJeff Moden - Thursday, May 3, 2018 2:32 PMpatrickmcginnis59 10839 - Thursday, May 3, 2018 12:32 PMJeff Moden - Thursday, May 3, 2018 11:58 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 11:20 AMJeff Moden - Thursday, May 3, 2018 11:15 AMpatrickmcginnis59 10839 - Thursday, May 3, 2018 5:00 AMJeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Let me answer that with a question. What is the difference between Monday and Monday?
0 !
Also, let me clarify this for you, I really have coded for day of week stuff, I'm asking what the actual issue is. I can sense you have serious reservations here and I am willing to fill in the blanks if you have some difficulties expressing it, but I gotta know what the actual issues are as I'm still stumped.
No sir. The answer is that you don't actually know because there's absolutely no way to identify which two Mondays I'm talking about.
The trouble is that the code given would also come up with "0" even if it's not the correct answer.
LOL sorry, you're the incorrect one here and let me tell you why. The OP asked how to determine the number of days between two weekdays, and according to his examples he disambiguates the situation with Friday and Monday, based on his required answer, three days between those, he's looking for the next weekday following. If he wants Monday and Monday to be 7 I would not complain a bit, I would instead congratulate him on enumerating the relatively low combination of weekdays as suggested by someone else which I agree with.
I've literally coded the same thing except in my case I supplied the next week day (exclusive) and the previous week day (inclusive). This stuff gets worked out. Heck you're asking me how to identify which two Mondays you're talking about? I wasn't talking about your two Mondays. You literally said you don't do weekday stuff. I was talking about the OP's two Mondays, and if he said no he'd rather it be 7 then he's the man and 7 it is!
Now what am I missing here?
What you're missing is that there's no guarantee that what the OP states will always be true even for the OP. And, there's no way to know from the given data.
There's no guarantee that anybody can produce a working spec for any program, and honestly I get your trepidation and experiences and I don't have any answers for you there. I'm all about the errors, I've made quite the study of that sort of thing, and the question does arise, what do you do in the face of possible errors? Isn't there a point where you have to cowboy up and get some programming done? At some point somebody needs to earn their paycheck.
Really, with this one we have maybe 3 edge conditions when you think of it. You have a day, then another day that follows, you have a day, then another day that precedes, you have the same day twice. Obviously, either day can be null but couldn't we "not null" those out?
I get you, how do we know that any given OP does his due diligence? I don't have any answers there, do we give everybody an exam upon user registration? I didn't mean to get into some existential thread about whether we should program at all, the general shortage of skilled programmers, the crisis in software specifications etc, I was really looking for specifics here with this particular rather routine looking problem. Really, the OP took care of two of the edges, we could have just pointed out the Monday Monday thing, was it really all that impossible in your understanding that the OP could make a decision there? Does our industry actually lack the confidence of even that level of analysis? In the face of that sort of analysis paralysis how can we get anything done?
The point that I'm trying to make is that there is no chance of data integrity here even if the requirements are crystal clear and well understood. That's what I'm trying to warn the OP about.
yes there is. I think you're misunderstanding the day of week problem. It is essentially about "periods", reoccurring schedules. I get you if he's trying to specify any particular monday, but once thats eliminated, this is about reoccuring periods. Since he's already told us what he's expecting, the spec is that this works for EVERY MONDAY, not just one monday in particular.
He told us that he expects to see "2" between monday and wednesday right? This means the following wednesday after any given monday. This is for EVERY MONDAY. I think thats the part you're missing. Remember, its not the 2nd wednesday, or the 3rd wednesday, he knows exactly which wednesday for any given monday. Its an OFFSET.
What you're saying is that its impossible to code weekly scheduling in computers and thats just not the case. Maybe you just don't program much. I do seem to see you specialize in string splitting. Its probably just a lack of programming on your part which is cool, heck I'm not that big a coder at work lately, but once you do day of week stuff you'll know what I'm talking about.
Think modulo math, with a modulo of 7. Thats routine stuff, even if you have to do integer offsets. Once you say that can't be done, you're eliminating lots of workloads from IT that I know for a fact are routinely done, you just probably haven't run across the requirement before.
Maybe you want it out of the database, but thats for you and Celko to figure out 😉
edit:
I still want to believe theres a reason to the posts your making, but I just doubt thats its applicable here. He's probably working with periods in a system thats already in existence and in that case its again going to be just like Celko telling him that his database design is all wrong. Do you think thats it?
I was for instance presented with pretty much a similar issue in one case, I was presented with the day of week spelled out, for me it was as simple as changing it to an integer and then applying modulo math and date functions to get the date of the "following day of week" which was also spelled out (and again for the preceding). Maybe thats what you're getting at but you would need lots more context from the OP to get there right?
Are you trying to say the OP should convert everything back to dates to get the difference between days? Wouldn't that particular date be then a rather arbitrary pick? Not saying thats a bad thing.
Just guessing at what you're trying to say.
May 3, 2018 at 5:32 pm
patrickmcginnis59 10839 - Thursday, May 3, 2018 4:32 PMyes there is. I think you're misunderstanding the day of week problem. It is essentially about "periods", reoccurring schedules. I get you if he's trying to specify any particular monday, but once thats eliminated, this is about reoccuring periods. Since he's already told us what he's expecting, the spec is that this works for EVERY MONDAY, not just one monday in particular.
He told us that he expects to see "2" between monday and wednesday right? This means the following wednesday after any given monday. This is for EVERY MONDAY. I think thats the part you're missing. Remember, its not the 2nd wednesday, or the 3rd wednesday, he knows exactly which wednesday for any given monday. Its an OFFSET.
What you're saying is that its impossible to code weekly scheduling in computers and thats just not the case. Maybe you just don't program much. I do seem to see you specialize in string splitting. Its probably just a lack of programming on your part which is cool, heck I'm not that big a coder at work lately, but once you do day of week stuff you'll know what I'm talking about.
Think modulo math, with a modulo of 7. Thats routine stuff, even if you have to do integer offsets. Once you say that can't be done, you're eliminating lots of workloads from IT that I know for a fact are routinely done, you just probably haven't run across the requirement before.
Maybe you want it out of the database, but thats for you and Celko to figure out 😉
edit:
I still want to believe theres a reason to the posts your making, but I just doubt thats its applicable here. He's probably working with periods in a system thats already in existence and in that case its again going to be just like Celko telling him that his database design is all wrong. Do you think thats it?
I was for instance presented with pretty much a similar issue in one case, I was presented with the day of week spelled out, for me it was as simple as changing it to an integer and then applying modulo math and date functions to get the date of the "following day of week" which was also spelled out (and again for the preceding). Maybe thats what you're getting at but you would need lots more context from the OP to get there right?
Are you trying to say the OP should convert everything back to dates to get the difference between days? Wouldn't that particular date be then a rather arbitrary pick? Not saying thats a bad thing.
Just guessing at what you're trying to say.
You'll make a very quick and nasty enemy of me if you ever compare me to Celko again, even in jest.
While everything you say is true, there is no way to enforce any of that. The Wednesday you speak of could easily be weeks out instead of the same week and no one would ever know. The only way to know is if the OP can guarantee that the days of the week are from the same week and the only way to do that is to derive it from data that has a date and if you have that, then there's no need to do compares by days of the week to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 6:10 pm
Jeff Moden - Thursday, May 3, 2018 5:32 PMpatrickmcginnis59 10839 - Thursday, May 3, 2018 4:32 PMyes there is. I think you're misunderstanding the day of week problem. It is essentially about "periods", reoccurring schedules. I get you if he's trying to specify any particular monday, but once thats eliminated, this is about reoccuring periods. Since he's already told us what he's expecting, the spec is that this works for EVERY MONDAY, not just one monday in particular.
He told us that he expects to see "2" between monday and wednesday right? This means the following wednesday after any given monday. This is for EVERY MONDAY. I think thats the part you're missing. Remember, its not the 2nd wednesday, or the 3rd wednesday, he knows exactly which wednesday for any given monday. Its an OFFSET.
What you're saying is that its impossible to code weekly scheduling in computers and thats just not the case. Maybe you just don't program much. I do seem to see you specialize in string splitting. Its probably just a lack of programming on your part which is cool, heck I'm not that big a coder at work lately, but once you do day of week stuff you'll know what I'm talking about.
Think modulo math, with a modulo of 7. Thats routine stuff, even if you have to do integer offsets. Once you say that can't be done, you're eliminating lots of workloads from IT that I know for a fact are routinely done, you just probably haven't run across the requirement before.
Maybe you want it out of the database, but thats for you and Celko to figure out 😉
edit:
I still want to believe theres a reason to the posts your making, but I just doubt thats its applicable here. He's probably working with periods in a system thats already in existence and in that case its again going to be just like Celko telling him that his database design is all wrong. Do you think thats it?
I was for instance presented with pretty much a similar issue in one case, I was presented with the day of week spelled out, for me it was as simple as changing it to an integer and then applying modulo math and date functions to get the date of the "following day of week" which was also spelled out (and again for the preceding). Maybe thats what you're getting at but you would need lots more context from the OP to get there right?
Are you trying to say the OP should convert everything back to dates to get the difference between days? Wouldn't that particular date be then a rather arbitrary pick? Not saying thats a bad thing.
Just guessing at what you're trying to say.
You'll make a very quick and nasty enemy of me if you ever compare me to Celko again, even in jest.
While everything you say is true, there is no way to enforce any of that. The Wednesday you speak of could easily be weeks out instead of the same week and no one would ever know. The only way to know is if the OP can guarantee that the days of the week are from the same week and the only way to do that is to derive it from data that has a date and if you have that, then there's no need to do compares by days of the week to begin with.
I've been nothing but polite in this thread. If you can't muster the bare minimum of humor to engage in friendly exchanges then I'll gladly surrender the thread to you.
good grief this forum and its malfunctioning editor
May 4, 2018 at 7:51 am
patrickmcginnis59 10839 - Thursday, May 3, 2018 6:10 PMJeff Moden - Thursday, May 3, 2018 5:32 PMpatrickmcginnis59 10839 - Thursday, May 3, 2018 4:32 PMyes there is. I think you're misunderstanding the day of week problem. It is essentially about "periods", reoccurring schedules. I get you if he's trying to specify any particular monday, but once thats eliminated, this is about reoccuring periods. Since he's already told us what he's expecting, the spec is that this works for EVERY MONDAY, not just one monday in particular.
He told us that he expects to see "2" between monday and wednesday right? This means the following wednesday after any given monday. This is for EVERY MONDAY. I think thats the part you're missing. Remember, its not the 2nd wednesday, or the 3rd wednesday, he knows exactly which wednesday for any given monday. Its an OFFSET.
What you're saying is that its impossible to code weekly scheduling in computers and thats just not the case. Maybe you just don't program much. I do seem to see you specialize in string splitting. Its probably just a lack of programming on your part which is cool, heck I'm not that big a coder at work lately, but once you do day of week stuff you'll know what I'm talking about.
Think modulo math, with a modulo of 7. Thats routine stuff, even if you have to do integer offsets. Once you say that can't be done, you're eliminating lots of workloads from IT that I know for a fact are routinely done, you just probably haven't run across the requirement before.
Maybe you want it out of the database, but thats for you and Celko to figure out 😉
edit:
I still want to believe theres a reason to the posts your making, but I just doubt thats its applicable here. He's probably working with periods in a system thats already in existence and in that case its again going to be just like Celko telling him that his database design is all wrong. Do you think thats it?
I was for instance presented with pretty much a similar issue in one case, I was presented with the day of week spelled out, for me it was as simple as changing it to an integer and then applying modulo math and date functions to get the date of the "following day of week" which was also spelled out (and again for the preceding). Maybe thats what you're getting at but you would need lots more context from the OP to get there right?
Are you trying to say the OP should convert everything back to dates to get the difference between days? Wouldn't that particular date be then a rather arbitrary pick? Not saying thats a bad thing.
Just guessing at what you're trying to say.
You'll make a very quick and nasty enemy of me if you ever compare me to Celko again, even in jest.
While everything you say is true, there is no way to enforce any of that. The Wednesday you speak of could easily be weeks out instead of the same week and no one would ever know. The only way to know is if the OP can guarantee that the days of the week are from the same week and the only way to do that is to derive it from data that has a date and if you have that, then there's no need to do compares by days of the week to begin with.
I've been nothing but polite in this thread. If you can't muster the bare minimum of humor to engage in friendly exchanges then I'll gladly surrender the thread to you.
good grief this forum and its malfunctioning editor
Perhaps unintended on your part but I took the comparison as a passive-aggressive slam. That may be due to cultural differences between us. I don't imply that someone is like someone else unless I mean it. Now that I understand your humor, let's leave it as no harm done and move on.
As for the subject of this thread, my only concern is the possibility of undetected errors in the future. There have been many such types of posts and situations in real life where the OP or Developer thinks they know and can control the limits of use only to find out later that they can't and haven't and then someone else has to fix things because they've moved on.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2018 at 11:19 am
Why the need to over-engineer everything? Each entry is, for example, "Wednesdays" not a specific Wednesday, Simple enough. Although I do agree that "Monday", "Monday" in the same row should not be allowed, but that is easily avoidable with a CHECK constraint.
It's the same over-engineering mentality that insists that rather than doing a simple date calc to determine, say, the first Monday of a month, you need a bloated calendar table with dozens of heavily redundant columns, most of which are likely never used.
Sometimes a simple answer is best and is all that is needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 16 through 30 (of 91 total)
You must be logged in to reply to this topic. Login to reply