Im trying to workout the amount of minutes between two dateTimes, but exlclude weekends.
I found a similar issue on StackExchange stackoverflow.com/question
count-cus and started working from that.
I built my query as:-
StartTime = CASE WHEN CAST(queueChange.entered AS DATE) = workingDays.[Date] THEN CAST(queueChange.entered AS TIME) ELSE CAST('08:30' AS TIME) END,
EndTime = CASE WHEN CAST(queueChange.exited AS DATE) = workingDays.[Date] THEN CAST(queueChange.exited AS TIME) ELSE CAST('17:00' AS TIME) END
INNER JOIN workingDays
ON workingDays.Date >= CAST(queueChange.entered AS DATE)
AND workingDays.Date <= CAST(queueChange.exited AS DATE)
which runs without error, but displays no rows.
I have uploaded my sqlite3 database here tmpWorkingHours.sql
, as you can see its quite simple 2 tables, one with the entered and exited, and the other a list of working hours (I know the SQL code above doesn't need it as has defaults in as part of the cast).
Does anyone know anything they can point me in the right direction?