SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Number of Days between two Day Names


Number of Days between two Day Names

Author
Message
QuietCoder
QuietCoder
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 64
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
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96866 Visits: 10351
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96689 Visits: 23461
How about...

USE Sandbox;
GO
/*
All odd alignment issues courtesy of SSC's text editor Smile
*/

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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)

Group: General Forum Members
Points: 986391 Visits: 49404
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96689 Visits: 23461
Jeff Moden - Tuesday, May 1, 2018 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.


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. Smile


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)SSC Guru (986K reputation)

Group: General Forum Members
Points: 986391 Visits: 49404
Thom A - Tuesday, May 1, 2018 9:58 AM
Jeff Moden - Tuesday, May 1, 2018 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.


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. Smile


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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96689 Visits: 23461
Jeff Moden - Tuesday, May 1, 2018 10:04 AM


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.


Now that, I totally agree with. Smile


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
QuietCoder
QuietCoder
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 64
Thom A - Tuesday, May 1, 2018 9:32 AM
How about...

USE Sandbox;
GO
/*
All odd alignment issues courtesy of SSC's text editor Smile
*/

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.


Thank you Thank you!
sgmunson
sgmunson
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109165 Visits: 7531
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)
Smile Smile Smile
Health & Nutrition
David Burrows
David Burrows
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55569 Visits: 11788
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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search