May 8, 2018 at 7:24 am
Lynn Pettis - Monday, May 7, 2018 5:56 PMjasona.work - Monday, May 7, 2018 5:49 PMScottPletcher - Monday, May 7, 2018 2:10 PMpatrickmcginnis59 10839 - Monday, May 7, 2018 1:59 PMHeh Scott turn back, there is no light here only darkness
If it's a generic table that says, "If we ship on Monday then it should arrive on Thursday", then, no, it makes no sense to use actual dates in that table. The true shipment itself will always have dates, but not the "ground rules" table. Again, as with SQL Jobs, you don't list all dates on a generic schedule.
I don't disagree with your point about not listing all the dates. If you look in sysschedules, there's four separate columns that determine when a job is going to run. If you have a job set to run one or two days a week (let's say Mon / Wed / Fri,) it's *NOT* stored in the table as MWF or anything like that, it's stored as a bitmapped int (42 for the aforementioned schedule, and that was TOTALLY accidental that I chose the answer to Life, the Universe, and Everything...) So *somehow* within the engine, SQL is converting that bitmap over to a way to know which day of the week we're looking at.
But, I suspect that even the OS doesn't actually have the name of the day of the week, I would suspect it converts that value to the name. So, again, even for a "generic" table, why not store the value as a date (which, in your comment is how it's going to be recorded somewhere,) and handle the date math the easy way instead of the frankly kludgy methods previously posted? If you're so determined to store the day names in the table, then again, easy enough to do, add a couple columns that are calculated columns that grab the startdate / enddate and use the DATENAME to populate them?
Thus far, from reading the topic, I've not seen a solid reason from either you or McGinnis to not use actual dates. I don't deny the fun in finding ways to solve the OPs problem, but by, for whatever reason, you're choosing to not use existing tools to solve the problem, feels like buying some IKEA furniture and then immediately throwing out the allen wrench they include in favor of making your own tool instead...
From what I have gleaned the OP has an OrderDate and a ShipDate. Sunday through Thursday the order needs to ship the same or following day (Sunday orders ship on Monday). Orders received on Friday or Saturday also ship on the following Monday. There is no schedule. If the order doesn't ship on the day it is supposed to ship that order does not meet the Shipping SLA. Yes, the dates should be captured in this situation.
There is sort of an implied weekly pattern and I think that's what folks got confused on. The discussion devolved into schedules thanks to me, as I was trying (and obviously failed) to describe what a weekday was to Jeff and what we could do with a column that contained a reference to a weekday. It was my fault the conversation went into schedules because it looks like he wants to do the same thing each week and yes, with each week actual dates would likely get instantiated. Depending on what day of the week is, he might want a value. I wouldn't go as far as to say there is no schedule, but I'm sure semantics about what words mean could eat up a thread too 😉
May 8, 2018 at 7:35 am
Thus far, from reading the topic, I've not seen a solid reason from either you or McGinnis to not use actual dates.
Jason, can you code my issue with actual dates then. I need a task to happen every day of the week and should generate records with actual dates. These tasks happen based on a weekday that is specific to a list of tasks and the weekday. These tasks and the weekday need to be somehow "remembered" so as each date occurs, the task can be generated (writing a record with the task id and the actual date will be fine). Each task id has a day of week it needs to happen on.
The representation of the weekday isn't really critical, we could specify it as an integer, spelled out day of week, etc. Storing it as a datetime would work EXCEPT its misleading because the code would really no longer be self documenting in that way the spelled out weekday or even an integer would be (although the integer isn't as self describing, at least it can't be confused with a date), and everytime I used the datetime, I'd be discarding everything but the day of week. Hardwiring the tasks and the day of week they occur count as storing, because if I have thousands of tasks and weekday's it obviously becomes a requirement to store them but really, hardwiring them is still storing them, its just dumb. Spelling out the weekday I don't think was anybody's actual requirement because translating between the spelling and another representation unambiguously is trivial.
You COULD store the first instance of the task by the first date it occurs, with the implication that it reoccurs weekly, but after the first occurrence it is now simply a substitution for the weekday. If the weekday then changed, it would actually break or otherwise become an instance of just using datetime, extracting the day of week and discarding the rest which I'd rather not do because it is misleading to anyone reading the code in the future. We do want to change weekdays when the task requires a different weekly schedule.
Looking forward to your response and please do not do the "I'm not a programmer copout" because that would be incredibly lame. Heck pseudo code would work or maybe a description that I could follow. I'm willing to accept legit answers and I have been wrong before, that's why I worked so hard with Jeff to see if he could explain my mistake.
May 8, 2018 at 3:00 pm
"Wow!" is an understatement. For a while there, it looked like a flame war based on utter nonsense. There is absolutely NO need to have a shipping schedule table in order to measure whether or not the ship date met the SLA. All you need is a way to calculate business days, or alternately a calendar table so that holidays can be taken into account. The idea that you need a schedule table is flawed from the beginning. There's absolutely zero reason to compare days of the week for that purpose, and it can and would lead to a potential problem given the OP's actual situation, as an order that shipped so late that a week went by would NOT be identified as not meeting SLA, when it should have been identified as such.
As to creating a schedule based on a recurring weekday event, that's trivial, and can certainly have dates computed from only holding on to a weekday name, ... but what happens when you need a temporary exception? What happens when a Monday is a holiday? These kinds of exceptions make working without dates impractical. Arguing over whether it's a programming task or a database task is largely irrelevant in comparison. The larger issue is that for Patrick's case with customer appointments, I can't imagine you could ever get away without having specific dates. Simply saying "your appointment is on Monday at 3 pm" doesn't work well if they forget on Monday, but the following Sunday they look at the card they received and it only says Monday at 3 pm. Same problem in the computer system. If the appointment only says Monday at 3 pm, then which Monday does that actually mean? How could you possibly schedule appointments more than a week out? That just doesn't work. Jeff was simply prepared to solve the WHOLE problem, and not just some small part of it, because he was willing to ask more questions and not just accept answers that don't really solve the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2018 at 3:45 pm
sgmunson - Tuesday, May 8, 2018 3:00 PM"Wow!" is an understatement. For a while there, it looked like a flame war based on utter nonsense. There is absolutely NO need to have a shipping schedule table in order to measure whether or not the ship date met the SLA. All you need is a way to calculate business days, or alternately a calendar table so that holidays can be taken into account. The idea that you need a schedule table is flawed from the beginning. There's absolutely zero reason to compare days of the week for that purpose, and it can and would lead to a potential problem given the OP's actual situation, as an order that shipped so late that a week went by would NOT be identified as not meeting SLA, when it should have been identified as such.As to creating a schedule based on a recurring weekday event, that's trivial, and can certainly have dates computed from only holding on to a weekday name, ... but what happens when you need a temporary exception? What happens when a Monday is a holiday? These kinds of exceptions make working without dates impractical. Arguing over whether it's a programming task or a database task is largely irrelevant in comparison. The larger issue is that for Patrick's case with customer appointments, I can't imagine you could ever get away without having specific dates. Simply saying "your appointment is on Monday at 3 pm" doesn't work well if they forget on Monday, but the following Sunday they look at the card they received and it only says Monday at 3 pm. Same problem in the computer system. If the appointment only says Monday at 3 pm, then which Monday does that actually mean? How could you possibly schedule appointments more than a week out? That just doesn't work. Jeff was simply prepared to solve the WHOLE problem, and not just some small part of it, because he was willing to ask more questions and not just accept answers that don't really solve the problem.
The scheduling doesn't prevent any handling of any particular individual scheduled event. That's the beauty of the weekday thing, its ONLY job is to schedule the event, then once the event is scheduled, emailed and recorded then that's whats needed, that gets the actual date, and the weekday column paired with the event id has done its job. Whats not to like about it? When the very general weekday gets successfully compared to the weekday of the current day it matches, THATS when you generate an actual date. I've never said, I don't eventually get the actual date, I said I need to make use of the generic weekday value that specifies and selects ALL occurences of the weekday that the desired task id needs matched with.
If the records that's generated isn't needed for that Monday then whats the big deal? We'll get another one next Monday, or whatever day of the week that the event id requires. If we don't want to schedule for a holiday, well heck, lets check the holiday table, whadya say? For that matter, creating the yearly table for holidays is a pretty good idea, but creating a table in advance for all the events, well, why not just wait for the actual event that is triggered by, what else, a weekday value matching the day of the week that is returned by datepart(dw,getdate()) I mean how awesome is that?
Heck, if you want me to yearly roll out the list of events, yeah I can do that too, but wouldn't I still consult the "event id","weekday" list to do it? Right? Wouldn't this still require interpreting a weekday value? I don't care that its spelled out, an integer, individual bit setting whatever. It could be a datetime, but then I'd still toss out everything but the day of week, and additional documentation would be needed that its not really a datetime or a date, so that's sort of an argument against using the datetime type. Its not impossible to use it that way though, just undesirable for the reasons stated.
But its fine with me that OP doesn't need to use that. What messes me up is that I'm somehow wrong using that, and that I need to somehow browbeat MY people into doing all of that manually. That to me is where my mind starts to boggle.
Sheesh this is like the third time I spec'ed my criteria and its crazy that there are no programmers who can tell what I need, well of course except for Scott as he knew from the get go what I was talking about, he and the OP know what a weekday value unambiguously specifies.
I do agree, its a flame war based on nonsense. The idea that there is no such thing as a weekday or any method to represent it unambiguously is entirely outside the realm of my experience, and anybody who even hints at such an assertion, well, I gotta stay polite and just say I at least try to see your side, but dang, you still gotta let me fufill my requirements right? To say I should browbeat my users because they're not manually typing in tens of thousands of records with all the intricate information involved is flatly ridiculous. I'm happy if the OP can be successful with or without it, but to pretend that representing and using a weekday value means nothing and isn't useful, well this was well, lets just say "news to me."
May 8, 2018 at 4:11 pm
patrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMsgmunson - Tuesday, May 8, 2018 3:00 PM"Wow!" is an understatement. For a while there, it looked like a flame war based on utter nonsense. There is absolutely NO need to have a shipping schedule table in order to measure whether or not the ship date met the SLA. All you need is a way to calculate business days, or alternately a calendar table so that holidays can be taken into account. The idea that you need a schedule table is flawed from the beginning. There's absolutely zero reason to compare days of the week for that purpose, and it can and would lead to a potential problem given the OP's actual situation, as an order that shipped so late that a week went by would NOT be identified as not meeting SLA, when it should have been identified as such.As to creating a schedule based on a recurring weekday event, that's trivial, and can certainly have dates computed from only holding on to a weekday name, ... but what happens when you need a temporary exception? What happens when a Monday is a holiday? These kinds of exceptions make working without dates impractical. Arguing over whether it's a programming task or a database task is largely irrelevant in comparison. The larger issue is that for Patrick's case with customer appointments, I can't imagine you could ever get away without having specific dates. Simply saying "your appointment is on Monday at 3 pm" doesn't work well if they forget on Monday, but the following Sunday they look at the card they received and it only says Monday at 3 pm. Same problem in the computer system. If the appointment only says Monday at 3 pm, then which Monday does that actually mean? How could you possibly schedule appointments more than a week out? That just doesn't work. Jeff was simply prepared to solve the WHOLE problem, and not just some small part of it, because he was willing to ask more questions and not just accept answers that don't really solve the problem.
The scheduling doesn't prevent any handling of any particular individual scheduled event. That's the beauty of the weekday thing, its ONLY job is to schedule the event, then once the event is scheduled, emailed and recorded then that's whats needed, that gets the actual date, and the weekday column paired with the event id has done its job. Whats not to like about it? When the very general weekday gets successfully compared to the weekday of the current day it matches, THATS when you generate an actual date. I've never said, I don't eventually get the actual date, I said I need to make use of the generic weekday value that specifies and selects ALL occurences of the weekday that the desired task id needs matched with.
If the records that's generated isn't needed for that Monday then whats the big deal? We'll get another one next Monday, or whatever day of the week that the event id requires. If we don't want to schedule for a holiday, well heck, lets check the holiday table, whadya say? For that matter, creating the yearly table for holidays is a pretty good idea, but creating a table in advance for all the events, well, why not just wait for the actual event that is triggered by, what else, a weekday value matching the day of the week that is returned by datepart(dw,getdate()) I mean how awesome is that?
Heck, if you want me to yearly roll out the list of events, yeah I can do that too, but wouldn't I still consult the "event id","weekday" list to do it? Right? Wouldn't this still require interpreting a weekday value? I don't care that its spelled out, an integer, individual bit setting whatever. It could be a datetime, but then I'd still toss out everything but the day of week, and additional documentation would be needed that its not really a datetime or a date, so that's sort of an argument against using the datetime type. Its not impossible to use it that way though, just undesirable for the reasons stated.
But its fine with me that OP doesn't need to use that. What messes me up is that I'm somehow wrong using that, and that I need to somehow browbeat MY people into doing all of that manually. That to me is where my mind starts to boggle.
Sheesh this is like the third time I spec'ed my criteria and its crazy that there are no programmers who can tell what I need, well of course except for Scott as he knew from the get go what I was talking about, he and the OP know what a weekday value unambiguously specifies.
I do agree, its a flame war based on nonsense. The idea that there is no such thing as a weekday or any method to represent it unambiguously is entirely outside the realm of my experience, and anybody who even hints at such an assertion, well, I gotta stay polite and just say I at least try to see your side, but dang, you still gotta let me fufill my requirements right? To say I should browbeat my users because they're not manually typing in tens of thousands of records with all the intricate information involved is flatly ridiculous. I'm happy if the OP can be successful with or without it, but to pretend that representing and using a weekday value means nothing and isn't useful, well this was well, lets just say "news to me."
For the record, the were no flames on my part until you compared me to Celko and I explained that.
It's funny... I'm not sure what the heck set you off because you're doing it much the way GUI would need to do it to prevent people from having to "finger in" dates and times but, we weren't talking about the GUI. We were talking about what needed to be stored and you're doing exactly what's needed when it comes to what needs to be stored.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2018 at 4:20 pm
patrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMsgmunson - Tuesday, May 8, 2018 3:00 PM"Wow!" is an understatement. For a while there, it looked like a flame war based on utter nonsense. There is absolutely NO need to have a shipping schedule table in order to measure whether or not the ship date met the SLA. All you need is a way to calculate business days, or alternately a calendar table so that holidays can be taken into account. The idea that you need a schedule table is flawed from the beginning. There's absolutely zero reason to compare days of the week for that purpose, and it can and would lead to a potential problem given the OP's actual situation, as an order that shipped so late that a week went by would NOT be identified as not meeting SLA, when it should have been identified as such.As to creating a schedule based on a recurring weekday event, that's trivial, and can certainly have dates computed from only holding on to a weekday name, ... but what happens when you need a temporary exception? What happens when a Monday is a holiday? These kinds of exceptions make working without dates impractical. Arguing over whether it's a programming task or a database task is largely irrelevant in comparison. The larger issue is that for Patrick's case with customer appointments, I can't imagine you could ever get away without having specific dates. Simply saying "your appointment is on Monday at 3 pm" doesn't work well if they forget on Monday, but the following Sunday they look at the card they received and it only says Monday at 3 pm. Same problem in the computer system. If the appointment only says Monday at 3 pm, then which Monday does that actually mean? How could you possibly schedule appointments more than a week out? That just doesn't work. Jeff was simply prepared to solve the WHOLE problem, and not just some small part of it, because he was willing to ask more questions and not just accept answers that don't really solve the problem.
The scheduling doesn't prevent any handling of any particular individual scheduled event. That's the beauty of the weekday thing, its ONLY job is to schedule the event, then once the event is scheduled, emailed and recorded then that's whats needed, that gets the actual date, and the weekday column paired with the event id has done its job. Whats not to like about it? When the very general weekday gets successfully compared to the weekday of the current day it matches, THATS when you generate an actual date. I've never said, I don't eventually get the actual date, I said I need to make use of the generic weekday value that specifies and selects ALL occurences of the weekday that the desired task id needs matched with.
If the records that's generated isn't needed for that Monday then whats the big deal? We'll get another one next Monday, or whatever day of the week that the event id requires. If we don't want to schedule for a holiday, well heck, lets check the holiday table, whadya say? For that matter, creating the yearly table for holidays is a pretty good idea, but creating a table in advance for all the events, well, why not just wait for the actual event that is triggered by, what else, a weekday value matching the day of the week that is returned by datepart(dw,getdate()) I mean how awesome is that?
Heck, if you want me to yearly roll out the list of events, yeah I can do that too, but wouldn't I still consult the "event id","weekday" list to do it? Right? Wouldn't this still require interpreting a weekday value? I don't care that its spelled out, an integer, individual bit setting whatever. It could be a datetime, but then I'd still toss out everything but the day of week, and additional documentation would be needed that its not really a datetime or a date, so that's sort of an argument against using the datetime type. Its not impossible to use it that way though, just undesirable for the reasons stated.
But its fine with me that OP doesn't need to use that. What messes me up is that I'm somehow wrong using that, and that I need to somehow browbeat MY people into doing all of that manually. That to me is where my mind starts to boggle.
Sheesh this is like the third time I spec'ed my criteria and its crazy that there are no programmers who can tell what I need, well of course except for Scott as he knew from the get go what I was talking about, he and the OP know what a weekday value unambiguously specifies.
I do agree, its a flame war based on nonsense. The idea that there is no such thing as a weekday or any method to represent it unambiguously is entirely outside the realm of my experience, and anybody who even hints at such an assertion, well, I gotta stay polite and just say I at least try to see your side, but dang, you still gotta let me fufill my requirements right? To say I should browbeat my users because they're not manually typing in tens of thousands of records with all the intricate information involved is flatly ridiculous. I'm happy if the OP can be successful with or without it, but to pretend that representing and using a weekday value means nothing and isn't useful, well this was well, lets just say "news to me."
Except that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
May 8, 2018 at 5:03 pm
Lynn Pettis - Tuesday, May 8, 2018 4:20 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMsgmunson - Tuesday, May 8, 2018 3:00 PM"Wow!" is an understatement. For a while there, it looked like a flame war based on utter nonsense. There is absolutely NO need to have a shipping schedule table in order to measure whether or not the ship date met the SLA. All you need is a way to calculate business days, or alternately a calendar table so that holidays can be taken into account. The idea that you need a schedule table is flawed from the beginning. There's absolutely zero reason to compare days of the week for that purpose, and it can and would lead to a potential problem given the OP's actual situation, as an order that shipped so late that a week went by would NOT be identified as not meeting SLA, when it should have been identified as such.As to creating a schedule based on a recurring weekday event, that's trivial, and can certainly have dates computed from only holding on to a weekday name, ... but what happens when you need a temporary exception? What happens when a Monday is a holiday? These kinds of exceptions make working without dates impractical. Arguing over whether it's a programming task or a database task is largely irrelevant in comparison. The larger issue is that for Patrick's case with customer appointments, I can't imagine you could ever get away without having specific dates. Simply saying "your appointment is on Monday at 3 pm" doesn't work well if they forget on Monday, but the following Sunday they look at the card they received and it only says Monday at 3 pm. Same problem in the computer system. If the appointment only says Monday at 3 pm, then which Monday does that actually mean? How could you possibly schedule appointments more than a week out? That just doesn't work. Jeff was simply prepared to solve the WHOLE problem, and not just some small part of it, because he was willing to ask more questions and not just accept answers that don't really solve the problem.
The scheduling doesn't prevent any handling of any particular individual scheduled event. That's the beauty of the weekday thing, its ONLY job is to schedule the event, then once the event is scheduled, emailed and recorded then that's whats needed, that gets the actual date, and the weekday column paired with the event id has done its job. Whats not to like about it? When the very general weekday gets successfully compared to the weekday of the current day it matches, THATS when you generate an actual date. I've never said, I don't eventually get the actual date, I said I need to make use of the generic weekday value that specifies and selects ALL occurences of the weekday that the desired task id needs matched with.
If the records that's generated isn't needed for that Monday then whats the big deal? We'll get another one next Monday, or whatever day of the week that the event id requires. If we don't want to schedule for a holiday, well heck, lets check the holiday table, whadya say? For that matter, creating the yearly table for holidays is a pretty good idea, but creating a table in advance for all the events, well, why not just wait for the actual event that is triggered by, what else, a weekday value matching the day of the week that is returned by datepart(dw,getdate()) I mean how awesome is that?
Heck, if you want me to yearly roll out the list of events, yeah I can do that too, but wouldn't I still consult the "event id","weekday" list to do it? Right? Wouldn't this still require interpreting a weekday value? I don't care that its spelled out, an integer, individual bit setting whatever. It could be a datetime, but then I'd still toss out everything but the day of week, and additional documentation would be needed that its not really a datetime or a date, so that's sort of an argument against using the datetime type. Its not impossible to use it that way though, just undesirable for the reasons stated.
But its fine with me that OP doesn't need to use that. What messes me up is that I'm somehow wrong using that, and that I need to somehow browbeat MY people into doing all of that manually. That to me is where my mind starts to boggle.
Sheesh this is like the third time I spec'ed my criteria and its crazy that there are no programmers who can tell what I need, well of course except for Scott as he knew from the get go what I was talking about, he and the OP know what a weekday value unambiguously specifies.
I do agree, its a flame war based on nonsense. The idea that there is no such thing as a weekday or any method to represent it unambiguously is entirely outside the realm of my experience, and anybody who even hints at such an assertion, well, I gotta stay polite and just say I at least try to see your side, but dang, you still gotta let me fufill my requirements right? To say I should browbeat my users because they're not manually typing in tens of thousands of records with all the intricate information involved is flatly ridiculous. I'm happy if the OP can be successful with or without it, but to pretend that representing and using a weekday value means nothing and isn't useful, well this was well, lets just say "news to me."
Except that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
My posts were specifically talking about how to store and manipulate weekdays according to the original specs and a similar requirement that I've also encountered. The implication was that the OP's post contained a silent failure waiting to happen and it devolved into whether day of week was valid for his situation, and all I did was present that it worked for me.
I think we all agreed that the OP should post his use case, but I posted my use case in support of operations using weekdays being unambiguous and that was pretty much my assertion that when you say "wednesday" everyone should know exactly what you're talking about, its a fundamental unit of value and has no inherent ambiguity at all whatsoever. The ambiguity of the weekday doesn't change at any point, even when its stored and used in further calculations and folks need to be careful to ensure thats always the case according to what we all agree the term "weekday" means and the 7 possible values it can take. If your use of weekday then produces a date or you define an operation between two weekdays using a determinate function that returns an integer then its not ambiguous. Whether its appropriate for the OP's problem could not be determined until he returned to state his business case, so in his absence I presented my successful use of the weekday value.
Then Jeff said I might become his enemy if I ever compared him to a noted historical figure who also happens to be one of our peers and whose posts I get a kick out of reading and it all went downhill from there but yeah just a routine thread on SSC, nothing to worry about.
May 8, 2018 at 5:35 pm
patrickmcginnis59 10839 - Tuesday, May 8, 2018 7:35 AMThus far, from reading the topic, I've not seen a solid reason from either you or McGinnis to not use actual dates.
Jason, can you code my issue with actual dates then. I need a task to happen every day of the week and should generate records with actual dates. These tasks happen based on a weekday that is specific to a list of tasks and the weekday. These tasks and the weekday need to be somehow "remembered" so as each date occurs, the task can be generated (writing a record with the task id and the actual date will be fine). Each task id has a day of week it needs to happen on.
The representation of the weekday isn't really critical, we could specify it as an integer, spelled out day of week, etc. Storing it as a datetime would work EXCEPT its misleading because the code would really no longer be self documenting in that way the spelled out weekday or even an integer would be (although the integer isn't as self describing, at least it can't be confused with a date), and everytime I used the datetime, I'd be discarding everything but the day of week. Hardwiring the tasks and the day of week they occur count as storing, because if I have thousands of tasks and weekday's it obviously becomes a requirement to store them but really, hardwiring them is still storing them, its just dumb. Spelling out the weekday I don't think was anybody's actual requirement because translating between the spelling and another representation unambiguously is trivial.
You COULD store the first instance of the task by the first date it occurs, with the implication that it reoccurs weekly, but after the first occurrence it is now simply a substitution for the weekday. If the weekday then changed, it would actually break or otherwise become an instance of just using datetime, extracting the day of week and discarding the rest which I'd rather not do because it is misleading to anyone reading the code in the future. We do want to change weekdays when the task requires a different weekly schedule.
Looking forward to your response and please do not do the "I'm not a programmer copout" because that would be incredibly lame. Heck pseudo code would work or maybe a description that I could follow. I'm willing to accept legit answers and I have been wrong before, that's why I worked so hard with Jeff to see if he could explain my mistake.
So, as requested...
🙂
To be honest, I'd likely, essentially "clone" the Microsoft method they used for the SQL Agent scheduling. Use a byte to represent the days of the week (IE 00000001 = Sunday, 00000010 = Monday, etc) with the 8th bit (10000000) being unused. You could store this in your datatype of choice (SQL uses an int) how you represent it on the front end is up to you.
Another entry to store the time, obviously without the date (because this is for a recurring schedule,) and you're on the way.
Now, my feeling on the mistake was taking the OPs question off in the direction of a scheduler of some sort, when (and from reading the original post it was a touch vague) that wasn't what they needed.
(BTW, nope, still not a programmer, but I can probably pseudo code my way out of a paper bag :hehe: )
May 8, 2018 at 6:00 pm
jasona.work - Tuesday, May 8, 2018 5:35 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 7:35 AMThus far, from reading the topic, I've not seen a solid reason from either you or McGinnis to not use actual dates.
Jason, can you code my issue with actual dates then. I need a task to happen every day of the week and should generate records with actual dates. These tasks happen based on a weekday that is specific to a list of tasks and the weekday. These tasks and the weekday need to be somehow "remembered" so as each date occurs, the task can be generated (writing a record with the task id and the actual date will be fine). Each task id has a day of week it needs to happen on.
The representation of the weekday isn't really critical, we could specify it as an integer, spelled out day of week, etc. Storing it as a datetime would work EXCEPT its misleading because the code would really no longer be self documenting in that way the spelled out weekday or even an integer would be (although the integer isn't as self describing, at least it can't be confused with a date), and everytime I used the datetime, I'd be discarding everything but the day of week. Hardwiring the tasks and the day of week they occur count as storing, because if I have thousands of tasks and weekday's it obviously becomes a requirement to store them but really, hardwiring them is still storing them, its just dumb. Spelling out the weekday I don't think was anybody's actual requirement because translating between the spelling and another representation unambiguously is trivial.
You COULD store the first instance of the task by the first date it occurs, with the implication that it reoccurs weekly, but after the first occurrence it is now simply a substitution for the weekday. If the weekday then changed, it would actually break or otherwise become an instance of just using datetime, extracting the day of week and discarding the rest which I'd rather not do because it is misleading to anyone reading the code in the future. We do want to change weekdays when the task requires a different weekly schedule.
Looking forward to your response and please do not do the "I'm not a programmer copout" because that would be incredibly lame. Heck pseudo code would work or maybe a description that I could follow. I'm willing to accept legit answers and I have been wrong before, that's why I worked so hard with Jeff to see if he could explain my mistake.
So, as requested...
🙂
To be honest, I'd likely, essentially "clone" the Microsoft method they used for the SQL Agent scheduling. Use a byte to represent the days of the week (IE 00000001 = Sunday, 00000010 = Monday, etc) with the 8th bit (10000000) being unused. You could store this in your datatype of choice (SQL uses an int) how you represent it on the front end is up to you.
Another entry to store the time, obviously without the date (because this is for a recurring schedule,) and you're on the way.Now, my feeling on the mistake was taking the OPs question off in the direction of a scheduler of some sort, when (and from reading the original post it was a touch vague) that wasn't what they needed.
(BTW, nope, still not a programmer, but I can probably pseudo code my way out of a paper bag :hehe: )
That will work! The code actually spelled the day of week out, but how the day of week is represented didn't really matter. Earlier in the thread I think folks were misunderstanding thinking the actual date needed to be used with the case I had (not the op's, we hadn't seen his business case yet). In my case the origination of the events start with a schedule, but how the schedule definition was stored was up to the implementer, I just didn't want to use datetime.
It can certainly be said that a weekly schedule uses week days, but a calendar schedule needs dates and I can sense the confusion that would cause.
May 9, 2018 at 7:40 am
patrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PM
Except that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
The OP never mentioned an SLA that I recall. That was posited later. The full initial q was:
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday MondayI need to calculate number of days between StartDay and EndDay
For Example;
ID 1 would be 4
ID 2 would be 1
ID 3 would be 3
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 9, 2018 at 9:49 am
Lynn Pettis - Tuesday, May 8, 2018 4:20 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMExcept that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
The OP never mentioned an SLA that I recall. That was posited later. The full initial q was:
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday MondayI need to calculate number of days between StartDay and EndDay
For Example;
ID 1 would be 4
ID 2 would be 1
ID 3 would be 3
And like almost every other initial post by people asking for help, we were given just a small piece of the problem. The OP did provide more information later. That should have changed the direction of the discussion.
May 9, 2018 at 2:48 pm
ScottPletcher - Wednesday, May 9, 2018 7:40 AMLynn Pettis - Tuesday, May 8, 2018 4:20 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMExcept that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
The OP never mentioned an SLA that I recall. That was posited later. The full initial q was:
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday MondayI need to calculate number of days between StartDay and EndDay
For Example;
ID 1 would be 4
ID 2 would be 1
ID 3 would be 3
And like almost every other initial post by people asking for help, we were given just a small piece of the problem. The OP did provide more information later. That should have changed the direction of the discussion.
It didn't really:
I was tasked with figuring out if an Service level agreement on Shipping an order was met and that goes by Day of the week. Orders coming in on Monday (Any Monday) need to ship by Tuesday, Orders from Friday, Saturday or Sunday need to ship by Monday. If they ship any day beyond the set End day the Service level agreement is not met. I approached the calculation by building a table like the one in the initial post
That is not different than the initial post. You still first need to calc just the (generic) days diff from the the day the Order came in. Then you compare that to the actual shipping days based on the dates in the shipments. That in no way requires changing the original day name to give it a date; indeed, that's not really directly viable.
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 9, 2018 at 3:34 pm
Lynn Pettis - Wednesday, May 9, 2018 9:49 AMScottPletcher - Wednesday, May 9, 2018 7:40 AMLynn Pettis - Tuesday, May 8, 2018 4:20 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMExcept that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
The OP never mentioned an SLA that I recall. That was posited later. The full initial q was:
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday MondayI need to calculate number of days between StartDay and EndDay
For Example;
ID 1 would be 4
ID 2 would be 1
ID 3 would be 3And like almost every other initial post by people asking for help, we were given just a small piece of the problem. The OP did provide more information later. That should have changed the direction of the discussion.
It didn't really:
I was tasked with figuring out if an Service level agreement on Shipping an order was met and that goes by Day of the week. Orders coming in on Monday (Any Monday) need to ship by Tuesday, Orders from Friday, Saturday or Sunday need to ship by Monday. If they ship any day beyond the set End day the Service level agreement is not met. I approached the calculation by building a table like the one in the initial post
That is not different than the initial post. You still first need to calc just the (generic) days diff from the the day the Order came in. Then you compare that to the actual shipping days based on the dates in the shipments. That in no way requires changing the original day name to give it a date; indeed, that's not really directly viable.
Okay. Order comes in Monday and ships on Tuesday. Did it meet the SLA? Answer, I don't know without know what Monday and what Tuesday. You only know that if you have the actual dates.
May 9, 2018 at 3:55 pm
ScottPletcher - Wednesday, May 9, 2018 2:48 PMLynn Pettis - Wednesday, May 9, 2018 9:49 AMScottPletcher - Wednesday, May 9, 2018 7:40 AMLynn Pettis - Tuesday, May 8, 2018 4:20 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMExcept that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
The OP never mentioned an SLA that I recall. That was posited later. The full initial q was:
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday MondayI need to calculate number of days between StartDay and EndDay
For Example;
ID 1 would be 4
ID 2 would be 1
ID 3 would be 3And like almost every other initial post by people asking for help, we were given just a small piece of the problem. The OP did provide more information later. That should have changed the direction of the discussion.
It didn't really:
I was tasked with figuring out if an Service level agreement on Shipping an order was met and that goes by Day of the week. Orders coming in on Monday (Any Monday) need to ship by Tuesday, Orders from Friday, Saturday or Sunday need to ship by Monday. If they ship any day beyond the set End day the Service level agreement is not met. I approached the calculation by building a table like the one in the initial post
That is not different than the initial post. You still first need to calc just the (generic) days diff from the the day the Order came in. Then you compare that to the actual shipping days based on the dates in the shipments. That in no way requires changing the original day name to give it a date; indeed, that's not really directly viable.
Okay. Order comes in Monday and ships on Tuesday. Did it meet the SLA? Answer, I don't know without know what Monday and what Tuesday. You only know that if you have the actual dates.
It's known to be within a week, as specified. Normal shipment times don't exceed a week. Sure, there could be some exception, but not 99% of the time.
Yet again:
The general SLA is just days. The actual order and actual shipment have specific dates. I don't see why that's so difficult to accept.
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 9, 2018 at 4:23 pm
Lynn Pettis - Wednesday, May 9, 2018 3:34 PMScottPletcher - Wednesday, May 9, 2018 2:48 PMLynn Pettis - Wednesday, May 9, 2018 9:49 AMScottPletcher - Wednesday, May 9, 2018 7:40 AMLynn Pettis - Tuesday, May 8, 2018 4:20 PMpatrickmcginnis59 10839 - Tuesday, May 8, 2018 3:45 PMExcept that the requirement here was to determine if an order is shipped within the SLA. This is not a scheduling issue. Did orders received 2018-05-07 ship no later than 2018-05-08? If any shipped on 2018-05-09 (or later) those orders did not meet the SLA. Did orders received on 2018-05-11 or 2018-05-12 or 2018-05-13 ship 2018-05-14? If any shipped on 2018-05-15 (or later) those orders did not meet the SLA.
With this you also need to take into account the possibility of an order being delayed for several weeks.
The OP never mentioned an SLA that I recall. That was posited later. The full initial q was:
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday MondayI need to calculate number of days between StartDay and EndDay
For Example;
ID 1 would be 4
ID 2 would be 1
ID 3 would be 3And like almost every other initial post by people asking for help, we were given just a small piece of the problem. The OP did provide more information later. That should have changed the direction of the discussion.
It didn't really:
I was tasked with figuring out if an Service level agreement on Shipping an order was met and that goes by Day of the week. Orders coming in on Monday (Any Monday) need to ship by Tuesday, Orders from Friday, Saturday or Sunday need to ship by Monday. If they ship any day beyond the set End day the Service level agreement is not met. I approached the calculation by building a table like the one in the initial post
That is not different than the initial post. You still first need to calc just the (generic) days diff from the the day the Order came in. Then you compare that to the actual shipping days based on the dates in the shipments. That in no way requires changing the original day name to give it a date; indeed, that's not really directly viable.
Okay. Order comes in Monday and ships on Tuesday. Did it meet the SLA? Answer, I don't know without know what Monday and what Tuesday. You only know that if you have the actual dates.
It's known to be within a week, as specified. Normal shipment times don't exceed a week. Sure, there could be some exception, but not 99% of the time.
Yet again:
The general SLA is just days. The actual order and actual shipment have specific dates. I don't see why that's so difficult to accept.
Normal and exceptions go hand in hand. I have worked for a company that shipped products to customers. You have to be able to handle the exceptions since nothing always goes according to plan.
Viewing 15 posts - 76 through 90 (of 91 total)
You must be logged in to reply to this topic. Login to reply