CODEWITHSUNDEEP
sqlsql-serverstored-proceduresbetweendate-range
aohm1989
aohm1989

Reputation: 401

SQL to Find records where result is in range between two columns, but check multiple rows

I'm working with two datasets, one is a more detailed view of clock punch activity, and another is a summation of entire "shifts" that these clock punches make up. What we are trying to accomplish, is to return a record where the PAYCODEID is equal to '7' or '8' (these are meal break codes), and if it falls within the range of the last two columns in the entire shift table just below:

ClockEventShifts:

ShiftID EMPID       Clockin                 MealRangeMax
1       00001280687 2014-02-16 08:00:00.000 2014-02-16 14:00:00.000
6       00001280687 2014-02-17 16:00:00.000 2014-02-17 22:00:00.000

There are a few key possibilities for the more detailed view of clock punches:

ClockEvent A:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
228     00001280687 2014-02-16 08:00:00.000 2014-02-16 12:00:00.000 20
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 8
235     00001280687 2014-02-16 13:00:00.000 2014-02-16 16:00:00.000 20
237     00001280687 2014-02-16 16:00:00.000 2014-02-16 17:01:00.000 21
238     00001280687 2014-02-16 17:01:00.000 2014-02-16 18:00:00.000 20
236     00001280687 2014-02-17 16:00:00.000 2014-02-17 17:00:00.000 20

ClockEvent B:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
228     00001280687 2014-02-16 08:00:00.000 2014-02-16 12:00:00.000 20
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 21
235     00001280687 2014-02-16 13:00:00.000 2014-02-16 16:00:00.000 20
237     00001280687 2014-02-16 16:00:00.000 2014-02-16 17:01:00.000 8
238     00001280687 2014-02-16 17:01:00.000 2014-02-16 18:00:00.000 20
236     00001280687 2014-02-17 16:00:00.000 2014-02-17 17:00:00.000 20

ClockEvent C:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
228     00001280687 2014-02-16 08:00:00.000 2014-02-16 12:00:00.000 20
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 21
235     00001280687 2014-02-16 13:00:00.000 2014-02-16 16:00:00.000 20
237     00001280687 2014-02-16 16:00:00.000 2014-02-16 17:01:00.000 21
238     00001280687 2014-02-16 17:01:00.000 2014-02-16 18:00:00.000 20
236     00001280687 2014-02-17 16:00:00.000 2014-02-17 17:00:00.000 20

ClockEvent D:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
228     00001280687 2014-02-16 08:00:00.000 2014-02-16 12:00:00.000 20
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 8
235     00001280687 2014-02-16 13:00:00.000 2014-02-16 16:00:00.000 20
237     00001280687 2014-02-16 16:00:00.000 2014-02-16 17:01:00.000 8
238     00001280687 2014-02-16 17:01:00.000 2014-02-16 18:00:00.000 20
236     00001280687 2014-02-17 16:00:00.000 2014-02-17 17:00:00.000 20

ClockEvent E:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
228     00001280687 2014-02-16 08:00:00.000 2014-02-16 12:00:00.000 8
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 21
235     00001280687 2014-02-16 13:00:00.000 2014-02-16 16:00:00.000 8
237     00001280687 2014-02-16 16:00:00.000 2014-02-16 17:01:00.000 21
238     00001280687 2014-02-16 17:01:00.000 2014-02-16 18:00:00.000 20
236     00001280687 2014-02-17 16:00:00.000 2014-02-17 17:00:00.000 20

Ideally, a perfect query/sproc could return the following in each scenario, checking for punches 360 minutes out:

A:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 8

B:

Nada!

C:

Nada!

D:

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
234     00001280687 2014-02-16 12:00:00.000 2014-02-16 13:00:00.000 8

E (it could return multiples too, but I really only care about the existence of at least one meal before 6 hours):

EVENTID EMPID       CLOCKIN                 CLOCKOUT                PAYCODEID
228     00001280687 2014-02-16 08:00:00.000 2014-02-16 12:00:00.000 8

It could also do something like this, ignoring that MealRangeMax piece and just using Clockin from ClockEventShifts:

A:

MINUTE_DIFFERENCE
240

B:

MINUTE_DIFFERENCE
600

C:

MINUTE_DIFFERENCE
NULL

D:

MINUTE_DIFFERENCE
240
600 (optional)

E:

MINUTE_DIFFERENCE
0
300 (optional)

I would tend to use something like IN or BETWEEN for such things, but IN can't check the values between things though to my knowledge, and BETWEEN can only check against an X and Y value to my knowledge, but I need to continue those BETWEEN checks for the rest of the result set from ClockEventShifts. I'm still searching for some solutions, but this seems to be slightly more complex.

Does anyone have any advice or ideas in approaching this problem?

Upvotes: 0

Views: 151

Answers (1)

Curly_Jefferson
Curly_Jefferson

Reputation: 185

This sql statement will give you records from CLOCKEVENT where the paycode is 7 or 8 and the event occurs within ClockEventShifts clockin time and mealrangemax.

SELECT   ce.*
  FROM ClockEvent ce
  , ClockEventShifts ces
  WHERE ce.PAYCODEID IN(7, 8)
    AND ce.EMPID     = ces.EMPID
    AND ce.CLOCKIN  >= ces.CLOCKIN
    AND ce.CLOCKOUT <= ces.MealRangeMax

If you only care about the ClockIn time being within the ClockEventShift range of time, you could use this:

SELECT   ce.*
  FROM ClockEvent ce
  , ClockEventShifts ces
  WHERE ce.PAYCODEID IN(7, 8)
    AND ce.EMPID     = ces.EMPID
    AND ce.CLOCKIN   BETWEEN ces.CLOCKIN AND ces.MealRangeMax

Upvotes: 2

Related Questions