May 6, 2018 at 9:52 pm
Jeff Moden - Sunday, May 6, 2018 9:12 PMDavid Burrows - Sunday, May 6, 2018 3:11 AMWow this thread went a bit sideways 🙁
It was a fun post. Patrick and I were egging each other on and it was a good bit of fun.
I enjoyed it!
May 7, 2018 at 7:58 am
When you create a weekly job schedule in SQL, you specify "Wednesday", as in every Wednesday (or specified identified Weds of the month), but you do not list all future Wed dates on which the job is to run. This is similar. I don't see what requires such over-engineering beyond that straightforward approach.
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 7, 2018 at 8:15 am
patrickmcginnis59 10839 - Sunday, May 6, 2018 9:52 PMJeff Moden - Sunday, May 6, 2018 9:12 PMDavid Burrows - Sunday, May 6, 2018 3:11 AMWow this thread went a bit sideways 🙁
It was a fun post. Patrick and I were egging each other on and it was a good bit of fun.
I enjoyed it!
Wow! This lead to a great discussion! I am sorry I was out a few days and did not respond to requests on the actual problem. Hopefully I can close the loop on why I needed the number of days between Day names and actual dates don't work for me. You might have better solutions for this but what I marked as an answer works perfectly for me. So here goes...
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
Hope this helps explain and thanks again for the passionate discussion!
May 7, 2018 at 8:16 am
ScottPletcher - Monday, May 7, 2018 7:58 AMWhen you create a weekly job schedule in SQL, you specify "Wednesday", as in every Wednesday (or specified identified Weds of the month), but you do not list all future Wed dates on which the job is to run. This is similar. I don't see what requires such over-engineering beyond that straightforward approach.
I did a job quite some time ago that specified schedules, and it had 7 flags in the record each representing a day of the week allowing scheduling multiple days. I couldn't imagine trying to code that thing with a date datatype. While it is entirely possible to define a day of week using the SQL Server datetime datatype, its bad news because then anybody else looking at the data column would have to know that the code only extracted the day of week. Heck when I worked at a college, the weekly schedule was really a thing that got hammered into you if you did any programming tasks with course schedules.
But I think the bigger shock to me about this thread is how alien the task of producing schedules is for some folks who I thought had some not insignificant programming experience. I guess I need to be thankful for my programming background and I guess I have to realize that some folks might go straight into database administration and never have to consider a relatively wide variety of programming tasks that routinely gets done with computers. I have a really new appreciation for the dba/developer divide, the struggle is real and this thread is a real eye opener for me personally and I probably shouldn't be unnecessarily razzing folks who actually might just not know better. My bad!
May 7, 2018 at 8:48 am
QuietCoder - Monday, May 7, 2018 8:15 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 9:52 PMJeff Moden - Sunday, May 6, 2018 9:12 PMDavid Burrows - Sunday, May 6, 2018 3:11 AMWow this thread went a bit sideways 🙁
It was a fun post. Patrick and I were egging each other on and it was a good bit of fun.
I enjoyed it!
Wow! This lead to a great discussion! I am sorry I was out a few days and did not respond to requests on the actual problem. Hopefully I can close the loop on why I needed the number of days between Day names and actual dates don't work for me. You might have better solutions for this but what I marked as an answer works perfectly for me. So here goes...
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
Hope this helps explain and thanks again for the passionate discussion!
You don't need to count the number of days between weekdays.
You need to figure out the threshold dispatch time for each delivery happened on a specific day of the week
Say, for OrderDate falling on Monday your ShippingDate must be not later than the end of Tuesday.
So, ShippingDateThreshold = dateadd(dd, datediff(dd, 0, OrderDate) +
CASE DATENAME(dw, OrderDate)
WHEN 'Monday' then 2
WHEN 'Friday' THEN 4
WHEN 'Saturday' THEN 3
WHEN 'Sunday' THEN 2
ELSE 1 END
, 0)
And then you can compare actual Shipping Dates with the thresholds to find out which deliveries were delayed.
WHERE ShippingDate < ShippingDateThreshold -- Good one
WHERE ShippingDate >= ShippingDateThreshold -- Late delivery
_____________
Code for TallyGenerator
May 7, 2018 at 8:53 am
patrickmcginnis59 10839 - Monday, May 7, 2018 8:16 AMScottPletcher - Monday, May 7, 2018 7:58 AMWhen you create a weekly job schedule in SQL, you specify "Wednesday", as in every Wednesday (or specified identified Weds of the month), but you do not list all future Wed dates on which the job is to run. This is similar. I don't see what requires such over-engineering beyond that straightforward approach.I did a job quite some time ago that specified schedules, and it had 7 flags in the record each representing a day of the week allowing scheduling multiple days. I couldn't imagine trying to code that thing with a date datatype. While it is entirely possible to define a day of week using the SQL Server datetime datatype, its bad news because then anybody else looking at the data column would have to know that the code only extracted the day of week. Heck when I worked at a college, the weekly schedule was really a thing that got hammered into you if you did any programming tasks with course schedules.
But I think the bigger shock to me about this thread is how alien the task of producing schedules is for some folks who I thought had some not insignificant programming experience. I guess I need to be thankful for my programming background and I guess I have to realize that some folks might go straight into database administration and never have to consider a relatively wide variety of programming tasks that routinely gets done with computers. I have a really new appreciation for the dba/developer divide, the struggle is real and this thread is a real eye opener for me personally and I probably shouldn't be unnecessarily razzing folks who actually might just not know better. My bad!
Heh... yep... you're the only one in this thread that's ever needed to write a scheduling system and, if not, you're the only one that's ever done it right.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2018 at 9:00 am
Jeff Moden - Monday, May 7, 2018 8:53 AMpatrickmcginnis59 10839 - Monday, May 7, 2018 8:16 AMScottPletcher - Monday, May 7, 2018 7:58 AMWhen you create a weekly job schedule in SQL, you specify "Wednesday", as in every Wednesday (or specified identified Weds of the month), but you do not list all future Wed dates on which the job is to run. This is similar. I don't see what requires such over-engineering beyond that straightforward approach.I did a job quite some time ago that specified schedules, and it had 7 flags in the record each representing a day of the week allowing scheduling multiple days. I couldn't imagine trying to code that thing with a date datatype. While it is entirely possible to define a day of week using the SQL Server datetime datatype, its bad news because then anybody else looking at the data column would have to know that the code only extracted the day of week. Heck when I worked at a college, the weekly schedule was really a thing that got hammered into you if you did any programming tasks with course schedules.
But I think the bigger shock to me about this thread is how alien the task of producing schedules is for some folks who I thought had some not insignificant programming experience. I guess I need to be thankful for my programming background and I guess I have to realize that some folks might go straight into database administration and never have to consider a relatively wide variety of programming tasks that routinely gets done with computers. I have a really new appreciation for the dba/developer divide, the struggle is real and this thread is a real eye opener for me personally and I probably shouldn't be unnecessarily razzing folks who actually might just not know better. My bad!
Heh... yep... you're the only one in this world that's ever needed to write a scheduling system and, if not, you're the only one that's ever done it right.
I know right? Its hard being the exclusive talent I presume myself to be! But that's why every time my paycheck gets printed, the lights dim and the HVAC system hickups. It doesn't interrupt my office mates because they know when to expect it because, er, never mind 😉
May 7, 2018 at 12:24 pm
As for schedulers, I would suspect that the internal working of the SQL Agent (or the Windows Task Scheduler) are doing a similar calculation to determine "is the current date a Monday?"
(Please note, I'm not a programmer, I can barely get a working "Hello World" in VB with an Exit button)
Perhaps the thing to remember is, I suspect Jeffs mindset is "try to cover every reasonable possibility of what someone might want, including things they may not want right now, and presume someone will enter bad data." So right now, the OPs requirement is "within 1 business day," but what about in 6 months when maybe it changes to "within 10 business days?" Now, Monday-Monday becomes very ambiguous, and correcting the code to give the correct answers potentially becomes a non-trivial task, whereas using dates for the dates, the work is already done. Change one value to indicate when they've gone out of SQL and you're done.
May 7, 2018 at 1:19 pm
jasona.work - Monday, May 7, 2018 12:24 PMThrowing in my 2cents on this, after reading through it...
I'm on Jeff's side for this. Simply using the day names gives you too much chance of a serious mix up. As the OP commented on why they're doing this, to see if SLAs for shipments are being met, what happens if an order comes in on a Monday, but doesn't ship until the following Monday? As Jeff points out, the potential answer is it took 0 days, rather than the actual 7 days, whereas if you store the order placed as a date, and the shipped as a date, it's easy enough to get back the correct answer (and, if you want, to convert those dates to datenames.)As for schedulers, I would suspect that the internal working of the SQL Agent (or the Windows Task Scheduler) are doing a similar calculation to determine "is the current date a Monday?"
(Please note, I'm not a programmer, I can barely get a working "Hello World" in VB with an Exit button)Perhaps the thing to remember is, I suspect Jeffs mindset is "try to cover every reasonable possibility of what someone might want, including things they may not want right now, and presume someone will enter bad data." So right now, the OPs requirement is "within 1 business day," but what about in 6 months when maybe it changes to "within 10 business days?" Now, Monday-Monday becomes very ambiguous, and correcting the code to give the correct answers potentially becomes a non-trivial task, whereas using dates for the dates, the work is already done. Change one value to indicate when they've gone out of SQL and you're done.
So add a tinyint column that's number of weeks to add, if you think you'd ever really need such a thing (you'd be much more likely to need a partial day rather than more than a week, as shipping times keep doing down, not up).
Calc the normal days diff + (number_of_weeks * 7) to get the final days.
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 7, 2018 at 1:38 pm
ScottPletcher - Monday, May 7, 2018 1:19 PMjasona.work - Monday, May 7, 2018 12:24 PMThrowing in my 2cents on this, after reading through it...
I'm on Jeff's side for this. Simply using the day names gives you too much chance of a serious mix up. As the OP commented on why they're doing this, to see if SLAs for shipments are being met, what happens if an order comes in on a Monday, but doesn't ship until the following Monday? As Jeff points out, the potential answer is it took 0 days, rather than the actual 7 days, whereas if you store the order placed as a date, and the shipped as a date, it's easy enough to get back the correct answer (and, if you want, to convert those dates to datenames.)As for schedulers, I would suspect that the internal working of the SQL Agent (or the Windows Task Scheduler) are doing a similar calculation to determine "is the current date a Monday?"
(Please note, I'm not a programmer, I can barely get a working "Hello World" in VB with an Exit button)Perhaps the thing to remember is, I suspect Jeffs mindset is "try to cover every reasonable possibility of what someone might want, including things they may not want right now, and presume someone will enter bad data." So right now, the OPs requirement is "within 1 business day," but what about in 6 months when maybe it changes to "within 10 business days?" Now, Monday-Monday becomes very ambiguous, and correcting the code to give the correct answers potentially becomes a non-trivial task, whereas using dates for the dates, the work is already done. Change one value to indicate when they've gone out of SQL and you're done.
So add a tinyint column that's number of weeks to add, if you think you'd ever really need such a thing (you'd be much more likely to need a partial day rather than more than a week, as shipping times keep doing down, not up).
Calc the normal days diff + (number_of_weeks * 7) to get the final days.
How would that be easier or more future proof than storing the dates of the order placement and shipment, and using DATEDIFF to get the number of days?
Using the OPs original source table, just replacing the day names with dates:
create table #dates (
ID INT
, StartDay date
, EndDay date)
insert into #dates values
(1, '2018-04-30', '2018-05-04')
,(2, '2018-05-01', '2018-05-02')
,(3, '2018-05-04', '2018-05-07')
select ID
, datediff(dd, Startday, EndDay)
from #dates
order by ID
drop table #dates
Then if you want the actual day name for each of those, add in a DATENAME line or two. Then it no longer matters HOW many days there are between order placed and shipped, you'll get a correct answer without needing to do any tracking of how many weeks might've gone by (and just *how* would you automatically track the weeks, if all you have is "Monday" and "Wednesday?" Somewhere for that to work, you'd need to know the actual *dates*)
May 7, 2018 at 1:59 pm
jasona.work - Monday, May 7, 2018 1:38 PMScottPletcher - Monday, May 7, 2018 1:19 PMjasona.work - Monday, May 7, 2018 12:24 PMThrowing in my 2cents on this, after reading through it...
I'm on Jeff's side for this. Simply using the day names gives you too much chance of a serious mix up. As the OP commented on why they're doing this, to see if SLAs for shipments are being met, what happens if an order comes in on a Monday, but doesn't ship until the following Monday? As Jeff points out, the potential answer is it took 0 days, rather than the actual 7 days, whereas if you store the order placed as a date, and the shipped as a date, it's easy enough to get back the correct answer (and, if you want, to convert those dates to datenames.)As for schedulers, I would suspect that the internal working of the SQL Agent (or the Windows Task Scheduler) are doing a similar calculation to determine "is the current date a Monday?"
(Please note, I'm not a programmer, I can barely get a working "Hello World" in VB with an Exit button)Perhaps the thing to remember is, I suspect Jeffs mindset is "try to cover every reasonable possibility of what someone might want, including things they may not want right now, and presume someone will enter bad data." So right now, the OPs requirement is "within 1 business day," but what about in 6 months when maybe it changes to "within 10 business days?" Now, Monday-Monday becomes very ambiguous, and correcting the code to give the correct answers potentially becomes a non-trivial task, whereas using dates for the dates, the work is already done. Change one value to indicate when they've gone out of SQL and you're done.
So add a tinyint column that's number of weeks to add, if you think you'd ever really need such a thing (you'd be much more likely to need a partial day rather than more than a week, as shipping times keep doing down, not up).
Calc the normal days diff + (number_of_weeks * 7) to get the final days.How would that be easier or more future proof than storing the dates of the order placement and shipment, and using DATEDIFF to get the number of days?
Using the OPs original source table, just replacing the day names with dates:
create table #dates (
ID INT
, StartDay date
, EndDay date)insert into #dates values
(1, '2018-04-30', '2018-05-04')
,(2, '2018-05-01', '2018-05-02')
,(3, '2018-05-04', '2018-05-07')select ID
, datediff(dd, Startday, EndDay)
from #dates
order by IDdrop table #dates
Then if you want the actual day name for each of those, add in a DATENAME line or two. Then it no longer matters HOW many days there are between order placed and shipped, you'll get a correct answer without needing to do any tracking of how many weeks might've gone by (and just *how* would you automatically track the weeks, if all you have is "Monday" and "Wednesday?" Somewhere for that to work, you'd need to know the actual *dates*)
Heh Scott turn back, there is no light here only darkness
May 7, 2018 at 2:10 pm
patrickmcginnis59 10839 - Monday, May 7, 2018 1:59 PMjasona.work - Monday, May 7, 2018 1:38 PMScottPletcher - Monday, May 7, 2018 1:19 PMjasona.work - Monday, May 7, 2018 12:24 PMThrowing in my 2cents on this, after reading through it...
I'm on Jeff's side for this. Simply using the day names gives you too much chance of a serious mix up. As the OP commented on why they're doing this, to see if SLAs for shipments are being met, what happens if an order comes in on a Monday, but doesn't ship until the following Monday? As Jeff points out, the potential answer is it took 0 days, rather than the actual 7 days, whereas if you store the order placed as a date, and the shipped as a date, it's easy enough to get back the correct answer (and, if you want, to convert those dates to datenames.)As for schedulers, I would suspect that the internal working of the SQL Agent (or the Windows Task Scheduler) are doing a similar calculation to determine "is the current date a Monday?"
(Please note, I'm not a programmer, I can barely get a working "Hello World" in VB with an Exit button)Perhaps the thing to remember is, I suspect Jeffs mindset is "try to cover every reasonable possibility of what someone might want, including things they may not want right now, and presume someone will enter bad data." So right now, the OPs requirement is "within 1 business day," but what about in 6 months when maybe it changes to "within 10 business days?" Now, Monday-Monday becomes very ambiguous, and correcting the code to give the correct answers potentially becomes a non-trivial task, whereas using dates for the dates, the work is already done. Change one value to indicate when they've gone out of SQL and you're done.
So add a tinyint column that's number of weeks to add, if you think you'd ever really need such a thing (you'd be much more likely to need a partial day rather than more than a week, as shipping times keep doing down, not up).
Calc the normal days diff + (number_of_weeks * 7) to get the final days.How would that be easier or more future proof than storing the dates of the order placement and shipment, and using DATEDIFF to get the number of days?
Using the OPs original source table, just replacing the day names with dates:
create table #dates (
ID INT
, StartDay date
, EndDay date)insert into #dates values
(1, '2018-04-30', '2018-05-04')
,(2, '2018-05-01', '2018-05-02')
,(3, '2018-05-04', '2018-05-07')select ID
, datediff(dd, Startday, EndDay)
from #dates
order by IDdrop table #dates
Then if you want the actual day name for each of those, add in a DATENAME line or two. Then it no longer matters HOW many days there are between order placed and shipped, you'll get a correct answer without needing to do any tracking of how many weeks might've gone by (and just *how* would you automatically track the weeks, if all you have is "Monday" and "Wednesday?" Somewhere for that to work, you'd need to know the actual *dates*)
Heh 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.
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 7, 2018 at 5:49 pm
ScottPletcher - 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...
May 7, 2018 at 5:56 pm
jasona.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.
May 8, 2018 at 7:00 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.
Not in the generic shipping day table. Only in the actual shipments table. The "intended ship day" is still not a specific date, it's a day relative to the day the order was placed.
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 - 61 through 75 (of 91 total)
You must be logged in to reply to this topic. Login to reply