How to select rows from db where the given date range falls?

0
votes

The table structure:

ID   VersionID   ShiftType   Duration(Hr)   FromDate       ToDate
**********************************************************************
1    1           WORKING     12             20150702        20170528
2    1           WORKING     12             20150702        20170528
3    2           NOTWORKING  06             20170529        20170531
4    2           WORKING     06             20170529        20170531
5    2           NOTWORKING  06             20170529        20170531
6    2           WORKING     06             20170529        20170531
7    3           WORKING     08             20170601        0
8    3           NOTWORKING  08             20170601        0
9    3           WORKING     08             20170601        0

Here you can see I have 3 version's(1,2,3) of shift(like day-shifts for 10 days and night-shifts for next 20 days etc).

The present followed shift will not have the end time(Row 7,8 and 9).

For example from 20150702 to 20170528 I followed version 1 which is of total 24 hours(1 day) working with two shifts and from 20170529 to 20170531 followed version 2 with four shifts two working and two nonworking of total 24 hours(1 day).

Now if user provides date range I should get the shift followed on that date range.

Provided date range: From 20170528 To 20170614.

Expected result: All Rows.

Working with below query:

SELECT * FROM SHIFTS
WHERE (FFROMDATE >= 20170528 AND FFROMDATE <= 20170614) OR (FTODATE >= 20170528 AND FTODATE <= 20170614)

But doesn't work for: From 20170502 To 20170514.

Expected result: Rows 1,2(But none selected).

But doesn't work for: From 20170604 To 20170714.

Expected result: Rows 7,8,9(But none selected).

Thank you.

3
sqloracle
Are you using MySQL, MS SQL Server or Oracle? Don't tag products not involved. (All those have different date/time handling...) - jarlh
are these date fields actual DateTime fields, or just strings (urgh)? - ADyson
what database is this ? sql server needs the date values quoted so i guess its another database - GuidoG
@jarlh, GuidoG Why does here what database matters?...I use mysql, oracle and python all three depending on application I build and other reasons. The solution could help all three db users. - Nithin B
"The solution could help all three db users". Not necessarily, because as mentioned they all handle dates differently. And really you should store dates in DateTime fields (that's what they're for!) rather than as numbers or strings. Much easier to make proper and accurate comparisons between the dates if you do that. - ADyson

3 Answers

1
votes

We have 3 distinct things we're looking for:

"Find all shifts that are entirely encompassed within my date range":

(FFROMDATE <= @StartDate AND FTODATE >= @EndDate)

Find all shifts that overlap with the start

(FFROMDATE >= @StartDate AND FFROMDATE <= @EndDate)

Find all shifts that overlap with the end

(FTODATE >= @StartDate AND FTODATE <= @EndDate)

Then combine them all together into one query.

SELECT * FROM SHIFTS
WHERE
(FFROMDATE < @StartDate AND FTODATE > @EndDate) OR
(FFROMDATE >= @StartDate AND FFROMDATE <= @EndDate) OR
(FTODATE >= @StartDate AND FTODATE <= @EndDate)

Make sure you test this with boundary values and remove = to suit your exact requirements.

1
votes

This works for all three cases:

select * 
  from shifts
  where fromdate <= range_end 
    and range_start <= case when todate = 0 then 99991231 else todate end

Test:

with shifts (id, fromdate, todate) as (
    select 1, 20150702, 20170528 from dual union all
    select 2, 20150702, 20170528 from dual union all
    select 3, 20170529, 20170531 from dual union all
    select 4, 20170529, 20170531 from dual union all
    select 5, 20170529, 20170531 from dual union all
    select 6, 20170529, 20170531 from dual union all
    select 7, 20170601,        0 from dual union all
    select 8, 20170601,        0 from dual union all
    select 9, 20170601,        0 from dual ),
ranges (range_id, range_start, range_end) as (
    select 1, 20170528, 20170614 from dual union all
    select 2, 20170502, 20170514 from dual union all
    select 3, 20170604, 20170714 from dual )
select * 
  from shifts cross join ranges
  where fromdate <= range_end 
    and range_start <= case when todate = 0 then 99991231 else todate end
  order by range_id, id
0
votes

Solution for oracle:

SELECT * FROM SHIFTS
WHERE FFROMDATE<=20170614 AND (FTODATE>=20170528 OR NVL(FTODATE,0)=0)

For SQL server:

Instead of NVL you can use ISNULL or CASE. I didn't try on sql server because I don't have it.