## Number of Days between two Day Names

 Author Message QuietCoder SSC-Addicted Group: General Forum Members Points: 441 Visits: 64 I have a table with a Start Day and a End Day ColumnID StartDay EndDay1 Monday Friday2 Tuesday Wednesday3 Friday MondayI need to calculate number of days between StartDay and EndDayFor Example;ID 1 would be 4 ID 2 would be 1ID 3 would be 3Thanks ScottPletcher SSC Guru 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 DaysDiffFROM ( 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 SSC Guru Group: General Forum Members Points: 96689 Visits: 23461 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 SampleTableVALUES ('Monday','Friday'), ('Tuesday','Wednesday'), ('Friday','Monday');GOCREATE FUNCTION DayNumber_fn (@DayName varchar(9))RETURNS TABLEAS 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;GOSELECT 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;GODROP 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 :-PPlease 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 SSC Guru 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 ModenRBAR 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. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Thom A SSC Guru Group: General Forum Members Points: 96689 Visits: 23461 +xJeff 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 :-PPlease 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 SSC Guru Group: General Forum Members Points: 986391 Visits: 49404 +xThom A - Tuesday, May 1, 2018 9:58 AM+xJeff 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 ModenRBAR 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. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Thom A SSC Guru Group: General Forum Members Points: 96689 Visits: 23461 +xJeff 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 :-PPlease 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 SSC-Addicted Group: General Forum Members Points: 441 Visits: 64 +xThom 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 SampleTableVALUES ('Monday','Friday'), ('Tuesday','Wednesday'), ('Friday','Monday');GOCREATE FUNCTION DayNumber_fn (@DayName varchar(9))RETURNS TABLEAS 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;GOSELECT 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;GODROP 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 SSC Guru 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.DayTwoFROM ( 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.WeekDayNameFROM ( 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 DaysBetweenFROM #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 D2PRINT 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)‌ ‌Health & Nutrition David Burrows SSC Guru 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.