May 4, 2018 at 3:00 pm
ScottPletcher - Friday, May 4, 2018 11:19 AMWhy the need to over-engineer everything? Each entry is, for example, "Wednesdays" not a specific Wednesday, Simple enough. Although I do agree that "Monday", "Monday" in the same row should not be allowed, but that is easily avoidable with a CHECK constraint.It's the same over-engineering mentality that insists that rather than doing a simple date to calc to determine, say, the first Monday of a month, you need a bloated calendar table with dozens of heavily redundant columns, most of which are likely never used.
Sometimes a simple answer is best and is all that is needed.
I'm right there with you and strongly believe in simple answers usually being the best answer. However, in this case, it's the under-engineering based on the reliance on a guaranteed condition of the data that simply cannot be guaranteed that has me concerned for the OP and others that may be tasked in a similar fashion. Even the Monday/Monday example is already suffering suggested changes that varies only by opinion rather than by anything concrete.
And, please... None of that is meant as a slam against anyone. There's just no way that I'd allow something like the requirement the OP has posed.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 3:15 am
QuietCoder - 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.
May 5, 2018 at 6:08 am
Eirikur 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, but I'm thinking it would just be an extra step in my opinion when its really more of an integer looking problem as days of the week are really just the numbers 1 through 7.
Therefore I think the best data type would be an integer. I mean to go to a date type just to find an offset would work, but you're doing date math when an integer would do the trick.
Its easy to detect the case when column 1 is a Friday and column 2 is a Monday for instance. If we start the day of week as a Sunday, we can make Sunday a 1, Monday a 2, etc just like datepart would do.
So, starting with the case of the second column is after the first column, it would be
select column2 - column1
Then to cover the case that the second column is before the first column, you'd test for a negative result, something like:
select case when column2 - column1 < 0 then column2 + 7 - column1 else column2 - column1 end
so then the last thing to do would be to convert OP's source into integers and then do the integer math.
with
schedules as (
select 'monday' columnday1, 'tuesday' columnday2 union
select 'tuesday' columnday1, 'monday' columnday2 union
select 'saturday' columnday1, 'sunday' columnday2 union
select 'wednesday' columnday1, 'wednesday' columnday2
),
scheduledays as (select columnday1, columnday2,
case
when columnday1 = 'Sunday' then 1
when columnday1 = 'Monday' then 2
when columnday1 = 'Tuesday' then 3
when columnday1 = 'Wednesday' then 4
when columnday1 = 'Thursday' then 5
when columnday1 = 'Friday' then 6
when columnday1 = 'Saturday' then 7
end column1,
case
when columnday2 = 'Sunday' then 1
when columnday2 = 'Monday' then 2
when columnday2 = 'Tuesday' then 3
when columnday2 = 'Wednesday' then 4
when columnday2 = 'Thursday' then 5
when columnday2 = 'Friday' then 6
when columnday2 = 'Saturday' then 7
end column2
from schedules
)
select columnday1, columnday2, case when column2 - column1 < 0 then column2 + 7 - column1 else column2 - column1 end dateoffset from scheduledays
edit: looks like sgmunson has already coded with a datetype, so op has plenty of options to pick from!
May 5, 2018 at 8:35 am
patrickmcginnis59 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 11:23 am
Jeff 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.
May 5, 2018 at 1:38 pm
patrickmcginnis59 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 2:11 pm
Jeff 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?
May 5, 2018 at 2:34 pm
patrickmcginnis59 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 2:49 pm
Jeff 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?
May 5, 2018 at 3:22 pm
patrickmcginnis59 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 3:25 pm
Jeff 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?
May 5, 2018 at 3:32 pm
patrickmcginnis59 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!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 3:45 pm
Jeff 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?
May 5, 2018 at 4:21 pm
patrickmcginnis59 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 4:44 pm
Jeff 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???????????????????????????????????
Viewing 15 posts - 31 through 45 (of 91 total)
You must be logged in to reply to this topic. Login to reply