May 5, 2018 at 5:00 pm
patrickmcginnis59 10839 - Saturday, May 5, 2018 4:44 PMJeff Moden - Saturday, May 5, 2018 4:21 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:45 PMJeff Moden - Saturday, May 5, 2018 3:32 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:25 PMJeff Moden - Saturday, May 5, 2018 3:22 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:49 PMJeff Moden - Saturday, May 5, 2018 2:34 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:11 PMJeff Moden - Saturday, May 5, 2018 1:38 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 11:23 AMJeff Moden - Saturday, May 5, 2018 8:35 AMpatrickmcginnis59 10839 - Saturday, May 5, 2018 6:08 AMEirikur Eiriksson - Saturday, May 5, 2018 3:15 AMQuietCoder - Tuesday, May 1, 2018 9:07 AMI have a table with a Start Day and a End Day ColumnID 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 3Thanks
Quick question, why not use proper dates, makes this task a piece of cake?
😎
Retrieving the weekday name from a date is trivial.I'd like to see the date version...
The written word sometimes leaves people wanting but I believe that what Eirikur is referring to is what I've been suggesting all along.
--===== Create and populate a test table to hold demo data.
-- This simulates a table with proper dates and is not a part of the solution.
CREATE TABLE #TestTable
(
ID INT
,StartDate DATE
,EndDate DATE
)
;
INSERT INTO #TestTable
(ID,StartDate,EndDate)
VALUES (1,'20180430','20180504') --A Monday and a Friday
,(2,'20180501','20180502') --A Tuesday and a Wednesday
,(3,'20180504','20180507') --A Friday and a Monday
;
--===== As Eirikur and I have both suggested, the use of "Proper Dates"
-- makes this an unabiguous cake walk.
SELECT *
,DaysBetweenDates = DATEDIFF(dd,StartDate,EndDate)
FROM #TestTable
;
If one doesn't have access to the original data and is left to essentially scraping report data that is absolutely guaranteed to not violate the intended limits of the problem definition, then your method will work just fine. However, if the reporting data was generated in house, I'd be on the Dev Manager to include the dates for each day to remove all ambiguity because even a person reading the report wouldn't know which week was being reported on unless there were a header stating at least the start or end date of the week. Even with such a header, I'd be pressing to see the original query that generated to the report so that it could be modified to be unambiguous for the required difference in days. If the data came from an external provider, then I'd be on them like white on rice to do the same.Where do we do the lookup for the incoming day of week pairs?
I think the OP is probably satisfied, so its really a moot point, but just out of curiousity, if I had two columns, each with a day of the week, and I wanted the number of days they were offset from each other, how does that work with your code?
For my case, we do have access to the original data. Its two columns, each containing the day of the week. That may be part of the misunderstanding, that the OP is somehow "scraping a printed report" or something. I don't know if he is, in my case the days of the week were the original data.
Don't worry if you don't want to solve it though, I posted working code so its a moot point.nd also don't worry if you can't articulate why its a bad idea, because if it were genuinely a bad idea then it would probably be at least reasonably possible to articulate the general principle it violates. And even then, the OP can probably not rewrite his entire app in any case, so he could very well be stuck with just days of the week, that was the case with my situation, and so in those sorts of cases you either code it, or hand it off to someone else who can.
I've already articulated, many times, why it's a bad idea. I also feel for anyone that has such shoddy data as only having the name of the day of the week instead of an actual date or datetime. Heh... and, no... I wouldn't attempt to write code to solve this problem. Instead, I'd be trying to solve the real problem of having shoddy data to deal with.
I think the closest to an objective reason not to do this that I can get from you is that its ambiguous, and thats why I went ahead and posted my code to show otherwise, although I'm sure the OP is long gone and probably has what he wants anyways.
I can describe my use case for the actual data item and maybe in this context you could describe what I'm doing wrong, say I want to produce a schedule, that includes instances of an event that I want to occur each week on a given day of the week, say to serve customers up something or other, like an order for materials. These customers want to keep their plant supplied on a weekly interval. Each customer has a preference for which day of the week they want their order to be instantiated. Each order might not be instantiated for any number of reasons, but we want the process to at least be initiated for each customer on their preferred day of the week, maybe to have a sales person touch base with the customer.
What data type would you use to store the weekday that is each customers preference? I'd probably pick integer, but obviously as I've revealed before, a day spelled out is certainly a possibility. What do you think?
Unless you have something else in the table that you're reading, then you code is ambiguous. Unless you have something else in the table that you're reading, you can't tell one Monday from another and the custom could actually be sending data where the actual days of the days involved are weeks apart rather than just 1 to several days apart.
If you're happy with that, then so be it. I wouldn't be unless there were something else in the table to guarantee the limits of the span of the number of days for each day pair.
So this customer appointment thing, Is this just not a job for a computer program in other words?
I'm talking about the weekly scheduled task that I would like to do with each customer on their chosen weekday. Paper and pencil then?
Heh... good lord. Of course it's a job for a computer. I'd just pick a more reliable and less ambiguous method. Especially if it was for something as important as customer appointments.
well yeah thats a good question too, how would you store the customers appointment?
Since it would be important to me, with a proper Start DATETIME and proper End DATETIME!
Ok! So, since I have to manually enter each customers appointment, how do I determine each date to type in? I guess I would use a customer table with their preferred weekday... oh yeah that's out because its ambiguous right? Can you help me out with the brand of notebook that I have to use to keep each customers preferred weekday so I can manually use the calendar to make sure I type in each customers individual appointment date on the correct weekday? You know, my many many customers that I would dearly love to not have to flip pages to find their flippin preferred weekday because apparently we can't store a flippin weekday in a flippin database?
Where do you get your "Thursdays" from, to begin with? If you're saying that the data is delivered to you without dates and times, then you need to start browbeating the people that are providing the data to provide the proper and verifiable data.
the sales guy and the customers agree on their day of week. Then of course, the sales guy then writes it down on the big customer notebook because we can't store day of week in the computer. But now you have me worried about the customer's address column. It says Main Street.
BUT WHICH MAIN STREET???????????????????????????????????
Dude. Seriously? Have a nice day.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 5:19 pm
Jeff Moden - Saturday, May 5, 2018 5:00 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 4:44 PMJeff Moden - Saturday, May 5, 2018 4:21 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:45 PMJeff Moden - Saturday, May 5, 2018 3:32 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:25 PMJeff Moden - Saturday, May 5, 2018 3:22 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:49 PMJeff Moden - Saturday, May 5, 2018 2:34 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:11 PMJeff Moden - Saturday, May 5, 2018 1:38 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 11:23 AMJeff Moden - Saturday, May 5, 2018 8:35 AMpatrickmcginnis59 10839 - Saturday, May 5, 2018 6:08 AMEirikur Eiriksson - Saturday, May 5, 2018 3:15 AMQuietCoder - Tuesday, May 1, 2018 9:07 AMI have a table with a Start Day and a End Day ColumnID 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 3Thanks
Quick question, why not use proper dates, makes this task a piece of cake?
😎
Retrieving the weekday name from a date is trivial.I'd like to see the date version...
The written word sometimes leaves people wanting but I believe that what Eirikur is referring to is what I've been suggesting all along.
--===== Create and populate a test table to hold demo data.
-- This simulates a table with proper dates and is not a part of the solution.
CREATE TABLE #TestTable
(
ID INT
,StartDate DATE
,EndDate DATE
)
;
INSERT INTO #TestTable
(ID,StartDate,EndDate)
VALUES (1,'20180430','20180504') --A Monday and a Friday
,(2,'20180501','20180502') --A Tuesday and a Wednesday
,(3,'20180504','20180507') --A Friday and a Monday
;
--===== As Eirikur and I have both suggested, the use of "Proper Dates"
-- makes this an unabiguous cake walk.
SELECT *
,DaysBetweenDates = DATEDIFF(dd,StartDate,EndDate)
FROM #TestTable
;
If one doesn't have access to the original data and is left to essentially scraping report data that is absolutely guaranteed to not violate the intended limits of the problem definition, then your method will work just fine. However, if the reporting data was generated in house, I'd be on the Dev Manager to include the dates for each day to remove all ambiguity because even a person reading the report wouldn't know which week was being reported on unless there were a header stating at least the start or end date of the week. Even with such a header, I'd be pressing to see the original query that generated to the report so that it could be modified to be unambiguous for the required difference in days. If the data came from an external provider, then I'd be on them like white on rice to do the same.Where do we do the lookup for the incoming day of week pairs?
I think the OP is probably satisfied, so its really a moot point, but just out of curiousity, if I had two columns, each with a day of the week, and I wanted the number of days they were offset from each other, how does that work with your code?
For my case, we do have access to the original data. Its two columns, each containing the day of the week. That may be part of the misunderstanding, that the OP is somehow "scraping a printed report" or something. I don't know if he is, in my case the days of the week were the original data.
Don't worry if you don't want to solve it though, I posted working code so its a moot point.nd also don't worry if you can't articulate why its a bad idea, because if it were genuinely a bad idea then it would probably be at least reasonably possible to articulate the general principle it violates. And even then, the OP can probably not rewrite his entire app in any case, so he could very well be stuck with just days of the week, that was the case with my situation, and so in those sorts of cases you either code it, or hand it off to someone else who can.
I've already articulated, many times, why it's a bad idea. I also feel for anyone that has such shoddy data as only having the name of the day of the week instead of an actual date or datetime. Heh... and, no... I wouldn't attempt to write code to solve this problem. Instead, I'd be trying to solve the real problem of having shoddy data to deal with.
I think the closest to an objective reason not to do this that I can get from you is that its ambiguous, and thats why I went ahead and posted my code to show otherwise, although I'm sure the OP is long gone and probably has what he wants anyways.
I can describe my use case for the actual data item and maybe in this context you could describe what I'm doing wrong, say I want to produce a schedule, that includes instances of an event that I want to occur each week on a given day of the week, say to serve customers up something or other, like an order for materials. These customers want to keep their plant supplied on a weekly interval. Each customer has a preference for which day of the week they want their order to be instantiated. Each order might not be instantiated for any number of reasons, but we want the process to at least be initiated for each customer on their preferred day of the week, maybe to have a sales person touch base with the customer.
What data type would you use to store the weekday that is each customers preference? I'd probably pick integer, but obviously as I've revealed before, a day spelled out is certainly a possibility. What do you think?
Unless you have something else in the table that you're reading, then you code is ambiguous. Unless you have something else in the table that you're reading, you can't tell one Monday from another and the custom could actually be sending data where the actual days of the days involved are weeks apart rather than just 1 to several days apart.
If you're happy with that, then so be it. I wouldn't be unless there were something else in the table to guarantee the limits of the span of the number of days for each day pair.
So this customer appointment thing, Is this just not a job for a computer program in other words?
I'm talking about the weekly scheduled task that I would like to do with each customer on their chosen weekday. Paper and pencil then?
Heh... good lord. Of course it's a job for a computer. I'd just pick a more reliable and less ambiguous method. Especially if it was for something as important as customer appointments.
well yeah thats a good question too, how would you store the customers appointment?
Since it would be important to me, with a proper Start DATETIME and proper End DATETIME!
Ok! So, since I have to manually enter each customers appointment, how do I determine each date to type in? I guess I would use a customer table with their preferred weekday... oh yeah that's out because its ambiguous right? Can you help me out with the brand of notebook that I have to use to keep each customers preferred weekday so I can manually use the calendar to make sure I type in each customers individual appointment date on the correct weekday? You know, my many many customers that I would dearly love to not have to flip pages to find their flippin preferred weekday because apparently we can't store a flippin weekday in a flippin database?
Where do you get your "Thursdays" from, to begin with? If you're saying that the data is delivered to you without dates and times, then you need to start browbeating the people that are providing the data to provide the proper and verifiable data.
the sales guy and the customers agree on their day of week. Then of course, the sales guy then writes it down on the big customer notebook because we can't store day of week in the computer. But now you have me worried about the customer's address column. It says Main Street.
BUT WHICH MAIN STREET???????????????????????????????????
Dude. Seriously? Have a nice day.
Heh, I wonder if the next time you schedule a weekly job in SQL Agent you'll think of this thread?
May 5, 2018 at 11:09 pm
Just for the fun of it, let the user decide the "same day" behavior...
DECLARE
@_beg_day VARCHAR(10) = 'thurs',
@_end_day VARCHAR(10) = 'thursday',
@_sdow BIT = 0 --(sdow = "same day, one week") 0={same day name = same day... 0 days} 1={same day name = 1 week... 7 days}
;
SELECT
CASE
WHEN dn.f_day_number = dn.l_day_number AND @_sdow = 1 THEN 7
WHEN dn.f_day_number <= dn.l_day_number THEN dn.l_day_number - dn.f_day_number
ELSE dn.l_day_number + 7 - dn.f_day_number
END
FROM
(
SELECT
f_day_number = MAX(CASE WHEN @_beg_day IN (dw.day_name_short, dw.day_name_long) THEN dw.day_number END),
l_day_number = MAX(CASE WHEN @_end_day IN (dw.day_name_short, dw.day_name_long) THEN dw.day_number END)
FROM
( VALUES
(1, 'Sun', 'Sunday'),
(2, 'Mon', 'Monday'),
(3, 'Tues', 'Tuesday'),
(4, 'Wed', 'Wednesday'),
(5, 'Thurs', 'Thursday'),
(6, 'Fri', 'Friday'),
(7, 'Sat', 'Saturday')
) dw (day_number, day_name_short, day_name_long)
) dn;
GO
May 6, 2018 at 12:01 am
patrickmcginnis59 10839 - Saturday, May 5, 2018 5:19 PMJeff Moden - Saturday, May 5, 2018 5:00 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 4:44 PMJeff Moden - Saturday, May 5, 2018 4:21 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:45 PMJeff Moden - Saturday, May 5, 2018 3:32 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:25 PMJeff Moden - Saturday, May 5, 2018 3:22 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:49 PMJeff Moden - Saturday, May 5, 2018 2:34 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:11 PMJeff Moden - Saturday, May 5, 2018 1:38 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 11:23 AMJeff Moden - Saturday, May 5, 2018 8:35 AMpatrickmcginnis59 10839 - Saturday, May 5, 2018 6:08 AMEirikur Eiriksson - Saturday, May 5, 2018 3:15 AMQuietCoder - Tuesday, May 1, 2018 9:07 AMI have a table with a Start Day and a End Day ColumnID 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 3Thanks
Quick question, why not use proper dates, makes this task a piece of cake?
😎
Retrieving the weekday name from a date is trivial.I'd like to see the date version...
The written word sometimes leaves people wanting but I believe that what Eirikur is referring to is what I've been suggesting all along.
--===== Create and populate a test table to hold demo data.
-- This simulates a table with proper dates and is not a part of the solution.
CREATE TABLE #TestTable
(
ID INT
,StartDate DATE
,EndDate DATE
)
;
INSERT INTO #TestTable
(ID,StartDate,EndDate)
VALUES (1,'20180430','20180504') --A Monday and a Friday
,(2,'20180501','20180502') --A Tuesday and a Wednesday
,(3,'20180504','20180507') --A Friday and a Monday
;
--===== As Eirikur and I have both suggested, the use of "Proper Dates"
-- makes this an unabiguous cake walk.
SELECT *
,DaysBetweenDates = DATEDIFF(dd,StartDate,EndDate)
FROM #TestTable
;
If one doesn't have access to the original data and is left to essentially scraping report data that is absolutely guaranteed to not violate the intended limits of the problem definition, then your method will work just fine. However, if the reporting data was generated in house, I'd be on the Dev Manager to include the dates for each day to remove all ambiguity because even a person reading the report wouldn't know which week was being reported on unless there were a header stating at least the start or end date of the week. Even with such a header, I'd be pressing to see the original query that generated to the report so that it could be modified to be unambiguous for the required difference in days. If the data came from an external provider, then I'd be on them like white on rice to do the same.Where do we do the lookup for the incoming day of week pairs?
I think the OP is probably satisfied, so its really a moot point, but just out of curiousity, if I had two columns, each with a day of the week, and I wanted the number of days they were offset from each other, how does that work with your code?
For my case, we do have access to the original data. Its two columns, each containing the day of the week. That may be part of the misunderstanding, that the OP is somehow "scraping a printed report" or something. I don't know if he is, in my case the days of the week were the original data.
Don't worry if you don't want to solve it though, I posted working code so its a moot point.nd also don't worry if you can't articulate why its a bad idea, because if it were genuinely a bad idea then it would probably be at least reasonably possible to articulate the general principle it violates. And even then, the OP can probably not rewrite his entire app in any case, so he could very well be stuck with just days of the week, that was the case with my situation, and so in those sorts of cases you either code it, or hand it off to someone else who can.
I've already articulated, many times, why it's a bad idea. I also feel for anyone that has such shoddy data as only having the name of the day of the week instead of an actual date or datetime. Heh... and, no... I wouldn't attempt to write code to solve this problem. Instead, I'd be trying to solve the real problem of having shoddy data to deal with.
I think the closest to an objective reason not to do this that I can get from you is that its ambiguous, and thats why I went ahead and posted my code to show otherwise, although I'm sure the OP is long gone and probably has what he wants anyways.
I can describe my use case for the actual data item and maybe in this context you could describe what I'm doing wrong, say I want to produce a schedule, that includes instances of an event that I want to occur each week on a given day of the week, say to serve customers up something or other, like an order for materials. These customers want to keep their plant supplied on a weekly interval. Each customer has a preference for which day of the week they want their order to be instantiated. Each order might not be instantiated for any number of reasons, but we want the process to at least be initiated for each customer on their preferred day of the week, maybe to have a sales person touch base with the customer.
What data type would you use to store the weekday that is each customers preference? I'd probably pick integer, but obviously as I've revealed before, a day spelled out is certainly a possibility. What do you think?
Unless you have something else in the table that you're reading, then you code is ambiguous. Unless you have something else in the table that you're reading, you can't tell one Monday from another and the custom could actually be sending data where the actual days of the days involved are weeks apart rather than just 1 to several days apart.
If you're happy with that, then so be it. I wouldn't be unless there were something else in the table to guarantee the limits of the span of the number of days for each day pair.
So this customer appointment thing, Is this just not a job for a computer program in other words?
I'm talking about the weekly scheduled task that I would like to do with each customer on their chosen weekday. Paper and pencil then?
Heh... good lord. Of course it's a job for a computer. I'd just pick a more reliable and less ambiguous method. Especially if it was for something as important as customer appointments.
well yeah thats a good question too, how would you store the customers appointment?
Since it would be important to me, with a proper Start DATETIME and proper End DATETIME!
Ok! So, since I have to manually enter each customers appointment, how do I determine each date to type in? I guess I would use a customer table with their preferred weekday... oh yeah that's out because its ambiguous right? Can you help me out with the brand of notebook that I have to use to keep each customers preferred weekday so I can manually use the calendar to make sure I type in each customers individual appointment date on the correct weekday? You know, my many many customers that I would dearly love to not have to flip pages to find their flippin preferred weekday because apparently we can't store a flippin weekday in a flippin database?
Where do you get your "Thursdays" from, to begin with? If you're saying that the data is delivered to you without dates and times, then you need to start browbeating the people that are providing the data to provide the proper and verifiable data.
the sales guy and the customers agree on their day of week. Then of course, the sales guy then writes it down on the big customer notebook because we can't store day of week in the computer. But now you have me worried about the customer's address column. It says Main Street.
BUT WHICH MAIN STREET???????????????????????????????????
Dude. Seriously? Have a nice day.
Heh, I wonder if the next time you schedule a weekly job in SQL Agent you'll think of this thread?
patrickmcginnis59 10839 - Saturday, May 5, 2018 5:19 PMJeff Moden - Saturday, May 5, 2018 5:00 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 4:44 PMJeff Moden - Saturday, May 5, 2018 4:21 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:45 PMJeff Moden - Saturday, May 5, 2018 3:32 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 3:25 PMJeff Moden - Saturday, May 5, 2018 3:22 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:49 PMJeff Moden - Saturday, May 5, 2018 2:34 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 2:11 PMJeff Moden - Saturday, May 5, 2018 1:38 PMpatrickmcginnis59 10839 - Saturday, May 5, 2018 11:23 AMJeff Moden - Saturday, May 5, 2018 8:35 AMpatrickmcginnis59 10839 - Saturday, May 5, 2018 6:08 AMEirikur Eiriksson - Saturday, May 5, 2018 3:15 AMQuietCoder - Tuesday, May 1, 2018 9:07 AMI have a table with a Start Day and a End Day ColumnID 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 3Thanks
Quick question, why not use proper dates, makes this task a piece of cake?
😎
Retrieving the weekday name from a date is trivial.I'd like to see the date version...
The written word sometimes leaves people wanting but I believe that what Eirikur is referring to is what I've been suggesting all along.
--===== Create and populate a test table to hold demo data.
-- This simulates a table with proper dates and is not a part of the solution.
CREATE TABLE #TestTable
(
ID INT
,StartDate DATE
,EndDate DATE
)
;
INSERT INTO #TestTable
(ID,StartDate,EndDate)
VALUES (1,'20180430','20180504') --A Monday and a Friday
,(2,'20180501','20180502') --A Tuesday and a Wednesday
,(3,'20180504','20180507') --A Friday and a Monday
;
--===== As Eirikur and I have both suggested, the use of "Proper Dates"
-- makes this an unabiguous cake walk.
SELECT *
,DaysBetweenDates = DATEDIFF(dd,StartDate,EndDate)
FROM #TestTable
;
If one doesn't have access to the original data and is left to essentially scraping report data that is absolutely guaranteed to not violate the intended limits of the problem definition, then your method will work just fine. However, if the reporting data was generated in house, I'd be on the Dev Manager to include the dates for each day to remove all ambiguity because even a person reading the report wouldn't know which week was being reported on unless there were a header stating at least the start or end date of the week. Even with such a header, I'd be pressing to see the original query that generated to the report so that it could be modified to be unambiguous for the required difference in days. If the data came from an external provider, then I'd be on them like white on rice to do the same.Where do we do the lookup for the incoming day of week pairs?
I think the OP is probably satisfied, so its really a moot point, but just out of curiousity, if I had two columns, each with a day of the week, and I wanted the number of days they were offset from each other, how does that work with your code?
For my case, we do have access to the original data. Its two columns, each containing the day of the week. That may be part of the misunderstanding, that the OP is somehow "scraping a printed report" or something. I don't know if he is, in my case the days of the week were the original data.
Don't worry if you don't want to solve it though, I posted working code so its a moot point.nd also don't worry if you can't articulate why its a bad idea, because if it were genuinely a bad idea then it would probably be at least reasonably possible to articulate the general principle it violates. And even then, the OP can probably not rewrite his entire app in any case, so he could very well be stuck with just days of the week, that was the case with my situation, and so in those sorts of cases you either code it, or hand it off to someone else who can.
I've already articulated, many times, why it's a bad idea. I also feel for anyone that has such shoddy data as only having the name of the day of the week instead of an actual date or datetime. Heh... and, no... I wouldn't attempt to write code to solve this problem. Instead, I'd be trying to solve the real problem of having shoddy data to deal with.
I think the closest to an objective reason not to do this that I can get from you is that its ambiguous, and thats why I went ahead and posted my code to show otherwise, although I'm sure the OP is long gone and probably has what he wants anyways.
I can describe my use case for the actual data item and maybe in this context you could describe what I'm doing wrong, say I want to produce a schedule, that includes instances of an event that I want to occur each week on a given day of the week, say to serve customers up something or other, like an order for materials. These customers want to keep their plant supplied on a weekly interval. Each customer has a preference for which day of the week they want their order to be instantiated. Each order might not be instantiated for any number of reasons, but we want the process to at least be initiated for each customer on their preferred day of the week, maybe to have a sales person touch base with the customer.
What data type would you use to store the weekday that is each customers preference? I'd probably pick integer, but obviously as I've revealed before, a day spelled out is certainly a possibility. What do you think?
Unless you have something else in the table that you're reading, then you code is ambiguous. Unless you have something else in the table that you're reading, you can't tell one Monday from another and the custom could actually be sending data where the actual days of the days involved are weeks apart rather than just 1 to several days apart.
If you're happy with that, then so be it. I wouldn't be unless there were something else in the table to guarantee the limits of the span of the number of days for each day pair.
So this customer appointment thing, Is this just not a job for a computer program in other words?
I'm talking about the weekly scheduled task that I would like to do with each customer on their chosen weekday. Paper and pencil then?
Heh... good lord. Of course it's a job for a computer. I'd just pick a more reliable and less ambiguous method. Especially if it was for something as important as customer appointments.
well yeah thats a good question too, how would you store the customers appointment?
Since it would be important to me, with a proper Start DATETIME and proper End DATETIME!
Ok! So, since I have to manually enter each customers appointment, how do I determine each date to type in? I guess I would use a customer table with their preferred weekday... oh yeah that's out because its ambiguous right? Can you help me out with the brand of notebook that I have to use to keep each customers preferred weekday so I can manually use the calendar to make sure I type in each customers individual appointment date on the correct weekday? You know, my many many customers that I would dearly love to not have to flip pages to find their flippin preferred weekday because apparently we can't store a flippin weekday in a flippin database?
Where do you get your "Thursdays" from, to begin with? If you're saying that the data is delivered to you without dates and times, then you need to start browbeating the people that are providing the data to provide the proper and verifiable data.
the sales guy and the customers agree on their day of week. Then of course, the sales guy then writes it down on the big customer notebook because we can't store day of week in the computer. But now you have me worried about the customer's address column. It says Main Street.
BUT WHICH MAIN STREET???????????????????????????????????
Dude. Seriously? Have a nice day.
Heh, I wonder if the next time you schedule a weekly job in SQL Agent you'll think of this thread?
Heh... Nah... Everyone knows which Main Street that would be. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2018 at 3:11 am
Wow this thread went a bit sideways 🙁
When I first read the first post I naturally thought of possible solutions. I did not even think about Monday to Monday being either zero or 7, my bad. :blush:
I even posted a suggestion that excluded the same start and end day. But if it was extended to all 42 combinations it would remove any ambiguity in any day difference within the bounds of day ranges within one week span.
After reading all the posts I would seek a full justification for storing data that way and why. That is part of the problem, not being able to see the whole picture. From a historical point of view why would you want to know why something started on a Monday without knowing which specific Monday (and in this context I am with Jeff on this) if all you needed was the number of days.
I am not saying that any particular solution or viewpoint is not correct just that there just is not enough information to make a sound judgement. If there is a sound business case to store data that way then so be it but pointing out possible future problems or possible improvements is not wrong either. 🙂
Far away is close at hand in the images of elsewhere.
Anon.
May 6, 2018 at 7:19 am
After reading all the posts I would seek a full justification for storing data that way and why. That is part of the problem, not being able to see the whole picture. From a historical point of view why would you want to know why something started on a Monday without knowing which specific Monday (and in this context I am with Jeff on this) if all you needed was the number of days.
I can understand that, if you're going to use a computer to process data, you need to understand what its going to do in general.
However, what I'm getting from some posters is that specifying the day of week is somehow ambiguous, and now I wonder how these same folks expect things like the SQL Agent to do its job.
When I use a storage location to symbolically specify a day of the week, thats all it is meant to specify. I would literally not be able to use this piece of information if it had to specify one particular instance of a monday because it would not work for my purposes, in particular generating schedules in my particular case as discussed.
edit: well actually I could, I could use datepart to extract the information and discard the rest. However that makes it even more confusing, if we think that the piece of data that names the day of the week is literally unuseable, why did microsoft allow us to extract it? Especially if from what I gather this is actually considered harmful by some experts here?
I am certainly left without answers on this one, and I'm wondering where the source of this confusion lies. Is this a part of relational theory that you cannot use a computer to schedule events on a periodic basis? I would really like to know more on this, I admit I'm relatively new to databases compared to many and I could be missing something here.
May 6, 2018 at 7:23 am
also I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?
May 6, 2018 at 7:50 am
patrickmcginnis59 10839 - Sunday, May 6, 2018 7:19 AMHowever, what I'm getting from some posters is that specifying the day of week is somehow ambiguous
I far as I am concerned a single day name is not ambiguous, however used in a StartDay, EndDay range without qualification.is.
From the data in the first post all that can be assumed is that the op wanted the number of days between the StartDay and the EndDay, no mention was made if this one single week or a maximum span of a number of days, how a week (if it was a week) was structured, what the result should be if the start and end days were the same. The question itself is ambiguous as any answer would be too, I think this is what Jeff was alluding to.
Far away is close at hand in the images of elsewhere.
Anon.
May 6, 2018 at 7:54 am
patrickmcginnis59 10839 - Sunday, May 6, 2018 7:23 AMalso I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?
My question is still the same, why not use proper and deterministic dates, schedule intervals have nothing to do with the OP?
😎
Any SQL Server Agent job I schedule is on the basis of number of seconds, minutes, hours, days, not the weekday name as that is both limiting and ambiguous as normal scheduling normally cannot be confined withing the count of 7 days. As an example, is the Monday to Monday zero, seven, fourteen or even 365 days?
I've noted that the OP hasn't made any input to the thread, my guess is that he has realised how bad of an idea this is.😉
May 6, 2018 at 8:05 am
Eirikur Eiriksson - Sunday, May 6, 2018 7:54 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 7:23 AMalso I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?My question is still the same, why not use proper and deterministic dates, schedule intervals have nothing to do with the OP?
😎
Any SQL Server Agent job I schedule is on the basis of number of seconds, minutes, hours, days, not the weekday name as that is both limiting and ambiguous as normal scheduling normally cannot be confined withing the count of 7 days. As an example, is the Monday to Monday zero, seven, fourteen or even 365 days?I've noted that the OP hasn't made any input to the thread, my guess is that he has realised how bad of an idea this is.😉
The OP's data column specified the day of week. The very use of day of week means that the OP didn't need any particular instance of this.
If you are going to speak of the day of the week, it can only mean day of the week.
If I was going to store a specific date as a repesentation of the day of the week, it would be a huge mistake as developers further on down the road would think the data stored indicated a particular day, and then further analysis would depend on this.
The same goes if I specify a month.. If my accounting department said that their fiscal year was from July to June, we would be on the same page as I understand periods. This fiscal year happens every year, unless or until they decided to change their fiscal year.
May 6, 2018 at 8:33 am
patrickmcginnis59 10839 - Sunday, May 6, 2018 8:05 AMEirikur Eiriksson - Sunday, May 6, 2018 7:54 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 7:23 AMalso I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?My question is still the same, why not use proper and deterministic dates, schedule intervals have nothing to do with the OP?
😎
Any SQL Server Agent job I schedule is on the basis of number of seconds, minutes, hours, days, not the weekday name as that is both limiting and ambiguous as normal scheduling normally cannot be confined withing the count of 7 days. As an example, is the Monday to Monday zero, seven, fourteen or even 365 days?I've noted that the OP hasn't made any input to the thread, my guess is that he has realised how bad of an idea this is.😉
The OP's data column specified the day of week. The very use of day of week means that the OP didn't need any particular instance of this.
If you are going to speak of the day of the week, it can only mean day of the week.
If I was going to store a specific date as a repesentation of the day of the week, it would be a huge mistake as developers further on down the road would think the data stored indicated a particular day, and then further analysis would depend on this.
The same goes if I specify a month.. If my accounting department said that their fiscal year was from July to June, we would be on the same page as I understand periods. This fiscal year happens every year, unless or until they decided to change their fiscal year.
Looking at the OP, what you are stating are quite some assumptions, just wondering why you are persisting on an inferior and ambiguous solution?
😎
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
May 6, 2018 at 8:43 am
Eirikur Eiriksson - Sunday, May 6, 2018 8:33 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 8:05 AMEirikur Eiriksson - Sunday, May 6, 2018 7:54 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 7:23 AMalso I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?My question is still the same, why not use proper and deterministic dates, schedule intervals have nothing to do with the OP?
😎
Any SQL Server Agent job I schedule is on the basis of number of seconds, minutes, hours, days, not the weekday name as that is both limiting and ambiguous as normal scheduling normally cannot be confined withing the count of 7 days. As an example, is the Monday to Monday zero, seven, fourteen or even 365 days?I've noted that the OP hasn't made any input to the thread, my guess is that he has realised how bad of an idea this is.😉
The OP's data column specified the day of week. The very use of day of week means that the OP didn't need any particular instance of this.
If you are going to speak of the day of the week, it can only mean day of the week.
If I was going to store a specific date as a repesentation of the day of the week, it would be a huge mistake as developers further on down the road would think the data stored indicated a particular day, and then further analysis would depend on this.
The same goes if I specify a month.. If my accounting department said that their fiscal year was from July to June, we would be on the same page as I understand periods. This fiscal year happens every year, unless or until they decided to change their fiscal year.
Looking at the OP, what you are stating are quite some assumptions, just wondering why you are persisting on an inferior and ambiguous solution?
😎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
I have a question for you!
Say my payroll department says I get paid on thursdays, and a pretty specific amount at that. What further question would I need to ask my payroll department to determine how much money I receive and when?
May 6, 2018 at 8:52 am
patrickmcginnis59 10839 - Sunday, May 6, 2018 8:43 AMEirikur Eiriksson - Sunday, May 6, 2018 8:33 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 8:05 AMEirikur Eiriksson - Sunday, May 6, 2018 7:54 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 7:23 AMalso I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?My question is still the same, why not use proper and deterministic dates, schedule intervals have nothing to do with the OP?
😎
Any SQL Server Agent job I schedule is on the basis of number of seconds, minutes, hours, days, not the weekday name as that is both limiting and ambiguous as normal scheduling normally cannot be confined withing the count of 7 days. As an example, is the Monday to Monday zero, seven, fourteen or even 365 days?I've noted that the OP hasn't made any input to the thread, my guess is that he has realised how bad of an idea this is.😉
The OP's data column specified the day of week. The very use of day of week means that the OP didn't need any particular instance of this.
If you are going to speak of the day of the week, it can only mean day of the week.
If I was going to store a specific date as a repesentation of the day of the week, it would be a huge mistake as developers further on down the road would think the data stored indicated a particular day, and then further analysis would depend on this.
The same goes if I specify a month.. If my accounting department said that their fiscal year was from July to June, we would be on the same page as I understand periods. This fiscal year happens every year, unless or until they decided to change their fiscal year.
Looking at the OP, what you are stating are quite some assumptions, just wondering why you are persisting on an inferior and ambiguous solution?
😎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 3I have a question for you!
Say my payroll department says I get paid on thursdays, and a pretty specific amount at that. What further question would I need to ask my payroll department to determine how much money I receive and when?
None if you use a proper date rather than the Weekday name of Thursday, since the proper date granularity is deterministic, one can then aggregate directly to any level without having to make a guess or correlate to a calendar table.
😎
May 6, 2018 at 9:12 am
Eirikur Eiriksson - Sunday, May 6, 2018 8:52 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 8:43 AMEirikur Eiriksson - Sunday, May 6, 2018 8:33 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 8:05 AMEirikur Eiriksson - Sunday, May 6, 2018 7:54 AMpatrickmcginnis59 10839 - Sunday, May 6, 2018 7:23 AMalso I would like to hear from the folks objecting to day of week data if they schedule jobs on their computers to run weekly or not and what their justification for using this but not that is?My question is still the same, why not use proper and deterministic dates, schedule intervals have nothing to do with the OP?
😎
Any SQL Server Agent job I schedule is on the basis of number of seconds, minutes, hours, days, not the weekday name as that is both limiting and ambiguous as normal scheduling normally cannot be confined withing the count of 7 days. As an example, is the Monday to Monday zero, seven, fourteen or even 365 days?I've noted that the OP hasn't made any input to the thread, my guess is that he has realised how bad of an idea this is.😉
The OP's data column specified the day of week. The very use of day of week means that the OP didn't need any particular instance of this.
If you are going to speak of the day of the week, it can only mean day of the week.
If I was going to store a specific date as a repesentation of the day of the week, it would be a huge mistake as developers further on down the road would think the data stored indicated a particular day, and then further analysis would depend on this.
The same goes if I specify a month.. If my accounting department said that their fiscal year was from July to June, we would be on the same page as I understand periods. This fiscal year happens every year, unless or until they decided to change their fiscal year.
Looking at the OP, what you are stating are quite some assumptions, just wondering why you are persisting on an inferior and ambiguous solution?
😎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 3I have a question for you!
Say my payroll department says I get paid on thursdays, and a pretty specific amount at that. What further question would I need to ask my payroll department to determine how much money I receive and when?
None if you use a proper date rather than the Weekday name of Thursday, since the proper date granularity is deterministic, one can then aggregate directly to any level without having to make a guess or correlate to a calendar table.
😎
Heh I'd hate to have to type in the calender table manually each time by consulting a calendar to determine which date each thursday falls on.
But I suspect thats not how it works in the real world, and as long as calendars really do get created with the assistance of automation, I suspect we'll be ok.
Now let me get out of the way here, I've posted plenty enough, I hope OP can fill us in if he can find his way clear!
May 6, 2018 at 9:12 pm
David 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 60 (of 91 total)
You must be logged in to reply to this topic. Login to reply