May 1, 2018 at 9:07 am
I have a table with a Start Day and a End Day Column
ID StartDay EndDay
1 Monday Friday
2 Tuesday Wednesday
3 Friday Monday
I 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
Thanks
May 1, 2018 at 9:19 am
Don't have a lot of time, but this should be real close at least:
SELECT *,
CASE WHEN EndDayNumber >= StartDayNumber THEN EndDayNumber - StartDayNumber
ELSE EndDayNumber + 7 - StartDayNumber END AS DaysDiff
FROM (
VALUES(1, 'Monday', 'Friday'),(2, 'Tuesday', 'Wednesday'),(3, 'Friday', 'Monday')
) AS test_data(D, StartDay, EndDay)
CROSS APPLY (
SELECT CEILING(CHARINDEX(EndDay, 'Monday Tuesday WednesdayThursday Friday Saturday Sunday') / 9.0) AS EndDayNumber,
CEILING(CHARINDEX(StartDay, 'Monday Tuesday WednesdayThursday Friday Saturday Sunday') / 9.0) AS StartDayNumber
) AS calc1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 1, 2018 at 9:32 am
How about...
USE Sandbox;
GO
/*
All odd alignment issues courtesy of SSC's text editor :)
*/
CREATE TABLE SampleTable (ID int IDENTITY(1,1),
StartDay varchar(9),
EndDay varchar(9));
INSERT INTO SampleTable
VALUES ('Monday','Friday'),
('Tuesday','Wednesday'),
('Friday','Monday');
GO
CREATE FUNCTION DayNumber_fn (@DayName varchar(9))
RETURNS TABLE
AS RETURN
SELECT CASE @DayName WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6 END AS DayNum;
GO
SELECT ID,
(7 + (ED.DayNum - SD.DayNum) ) % 7 AS [Days]
FROM SampleTable ST
CROSS APPLY dbo.DayNumber_fn(ST.StartDay) SD
CROSS APPLY dbo.DayNumber_fn(ST.EndDay) ED;
GO
DROP TABLE SampleTable;
DROP FUNCTION DayNumber_fn;
I've used a Inline table Function here, however, you could move the CASE into the main query and use a CTE. A The use of the function just makes the final query a little more little more succinct.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 1, 2018 at 9:37 am
I'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2018 at 9:58 am
Jeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.
I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 1, 2018 at 10:04 am
Thom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2018 at 10:06 am
Jeff Moden - Tuesday, May 1, 2018 10:04 AMYep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Now that, I totally agree with. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 1, 2018 at 10:30 am
Thom A - Tuesday, May 1, 2018 9:32 AMHow about...
USE Sandbox;
GO
/*
All odd alignment issues courtesy of SSC's text editor :)
*/CREATE TABLE SampleTable (ID int IDENTITY(1,1),
StartDay varchar(9),
EndDay varchar(9));INSERT INTO SampleTable
VALUES ('Monday','Friday'),
('Tuesday','Wednesday'),
('Friday','Monday');
GOCREATE FUNCTION DayNumber_fn (@DayName varchar(9))
RETURNS TABLE
AS RETURNSELECT CASE @DayName WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6 END AS DayNum;GO
SELECT ID,
(7 + (ED.DayNum - SD.DayNum) ) % 7 AS [Days]
FROM SampleTable ST
CROSS APPLY dbo.DayNumber_fn(ST.StartDay) SD
CROSS APPLY dbo.DayNumber_fn(ST.EndDay) ED;GO
DROP TABLE SampleTable;
DROP FUNCTION DayNumber_fn;I've used a Inline table Function here, however, you could move the CASE into the main query and use a CTE. A The use of the function just makes the final query a little more little more succinct.
Thank you Thank you!
May 1, 2018 at 12:04 pm
This seems to work as well:CREATE TABLE #SampleData (
DayOne varchar(9) NOT NULL,
DayTwo varchar(9) NOT NULL
);
CREATE CLUSTERED INDEX NDX_TEMP_SampleData_DayTwo_DayOne_ ON #SampleData
(
DayTwo ASC,
DayOne ASC
);
INSERT INTO #SampleData (DayOne, DayTwo)
SELECT X.DayOne, X.DayTwo
FROM (
VALUES ('Monday', 'Friday'),
('Tuesday', 'Wednesday'),
('Friday', 'Monday')
) AS X (DayOne, DayTwo);
CREATE TABLE #TwoWeeks (
ID int UNIQUE NOT NULL,
TheDate date NOT NULL,
WeekDayName varchar(9) NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX UCDX_TEMP_TwoWeeks_WeekDayName_TheDate ON #TwoWeeks
(
WeekDayName ASC,
TheDate ASC
);
SET NOCOUNT ON;
PRINT CONVERT(varchar(30), SYSDATETIME()) + ' - Query Start';
INSERT INTO #TwoWeeks (ID, TheDate, WeekDayName)
SELECT X.ID, X.TheDate, X.WeekDayName
FROM (
VALUES ( 1, '1900-01-01', 'Monday'),
( 2, '1900-01-02', 'Tuesday'),
( 3, '1900-01-03', 'Wednesday'),
( 4, '1900-01-04', 'Thursday'),
( 5, '1900-01-05', 'Friday'),
( 6, '1900-01-06', 'Saturday'),
( 7, '1900-01-07', 'Sunday'),
( 8, '1900-01-08', 'Monday'),
( 9, '1900-01-09', 'Tuesday'),
(10, '1900-01-10', 'Wednesday'),
(11, '1900-01-11', 'Thursday'),
(12, '1900-01-12', 'Friday'),
(13, '1900-01-13', 'Saturday'),
(14, '1900-01-14', 'Sunday')
) AS X (ID, TheDate, WeekDayName);
SELECT SD.DayOne, SD.DayTwo, D2.ID - D1.ID AS DaysBetween
FROM #SampleData AS SD
CROSS APPLY (
SELECT TOP (1) TW.ID, TW.TheDate
FROM #TwoWeeks AS TW
WHERE TW.WeekDayName = SD.DayOne
ORDER BY TW.TheDate
) AS D1
CROSS APPLY (
SELECT TOP (1) TW.ID
FROM #TwoWeeks AS TW
WHERE TW.WeekDayName = SD.DayTwo
AND TW.TheDate > D1.TheDate
ORDER BY TW.TheDate
) AS D2
PRINT CONVERT(varchar(39), SYSDATETIME()) + ' - Query Complete';
DROP TABLE #SampleData;
DROP TABLE #TwoWeeks;
The query portion runs in roughly 15.6 ms, and consistently. Your mileage may vary.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 2, 2018 at 2:41 am
Why not just use a cartesian of possible combinations of start and end day (42 excluding same start and end day) together with the number of days and then do a simple lookup
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2018 at 2:56 pm
Jeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
May 2, 2018 at 3:42 pm
My two cents would be to add a distance column on your table and populate.
If you want to figure out on the fly the distance between two days I would do the following to get all possible combinations and then pull the value you need in your scenario:
IF OBJECT_ID ('tempdb..#days') IS NOT NULL
DROP TABLE #days
CREATE TABLE #days (myDay VARCHAR (10), distance INT)
INSERT INTO #days ( myDay
,distance )
VALUES
( 'MONDAY',1)
,( 'TUESDAY',2)
,( 'WEDNESDAY',3)
,( 'THURSDAY',4)
,( 'FRIDAY',5)
,( 'SATURDAY',6)
,( 'SUNDAY',7)
SELECT [day1] = d1.myDay
,[day2] = d2.myDay
,[distance] = CASE WHEN d2.distance - d1.distance < 0
THEN d2.distance - d1.distance + 7
ELSE d2.distance - d1.distance
END
FROM #days d1
CROSS JOIN #days d2
May 2, 2018 at 4:43 pm
patrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 12:09 am
Jeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
What about non-working days - weekends and bank holidays?
May 3, 2018 at 5:01 am
Jeff Moden - Wednesday, May 2, 2018 4:43 PMpatrickmcginnis59 10839 - Wednesday, May 2, 2018 2:56 PMJeff Moden - Tuesday, May 1, 2018 10:04 AMThom A - Tuesday, May 1, 2018 9:58 AMJeff Moden - Tuesday, May 1, 2018 9:37 AMI'll be interesting as hell to see what happens during a "wrap around" when the start day is a Thursday and the end day is a Monday. And what of a possible multi-week span? I think this whole thing could be a real problem in the future.I think the OP caters for that already, Jeff. They have a Start Day of Friday and End Day of Monday, resulting in a value of 3. 🙂
Yep... still a wicked bad idea. They should use dates, instead. If they don't have them, they need to change their data collection method because using DOWs is a silent failure accident waiting to happen.
Not really in all cases. For instance, my work schedule is Monday through Friday and its worked out ok. Its very predictable that I won't show up on Saturday most times for instance, and if I am actually needed, the day of the week is enough of a cue that I'll probably receive notice that I should be on call or at work on that day. On the off chance that it intercepts with a holiday for instance, the holiday takes precedence.
Its been a seven day week for quite a while actually! We should not expect the number of days between monday and wednesday to change anytime soon, so OP is probably safe, and if the week ever changes to fewer or more than 7 days, or the order of days be changed, I'm thinking OP should have enough time to change their code.
reference: https://en.wikipedia.org/wiki/Names_of_the_days_of_the_week
2 cents!
Understood on the names of days of the week not changing but there's also 52+ weeks in a year. While there's certainly only 7 days (or 5) days in your work week, even the OP's original example skips out of the norm by measuring from Friday to Monday. Since there's that exception, one has to ask how many other exceptions will there be that are currently unknown and will silently provide incorrect answers in the future? There is no way to validate such things, which means that the data fails integrity checks and should be unacceptable to anyone that believes in data quality.
Friday through Monday is unambiguous also. Saturday and Sunday unambiguously lie between Friday and Monday.
Lets try talking about this a bit differently. If a business process uses days of the week, are you saying the IT department should be forbidden to automate any process in support of this?
I've coded for days of the week before and it never occurred to me to object to it.
Viewing 15 posts - 1 through 15 (of 91 total)
You must be logged in to reply to this topic. Login to reply