0

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?

aohm1989
  • 401
  • 1
  • 8
  • 19
  • 1
    I'm not entirely sure of what these clock events are you speak of. – Zane Feb 21 '14 at 15:40
  • Well, the way you can think about it, is suppose you work in a day, and 2 hours you did task A, then the next 2 hours you did task B, then the next 2 hours you took lunch, then the next two hours you did task C. It would be one "Shift", but you would have four clock "Events". – aohm1989 Feb 21 '14 at 16:02

1 Answers1

2

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
Curly_Jefferson
  • 175
  • 1
  • 6
  • Thanks for the quick response! I think this is going to work for me, in particular your second response :) It's so simple now when I see it (and I was on the right track it seems). – aohm1989 Feb 21 '14 at 15:57
  • Your stipulation for the second query doesn't make sense to me, because the two queries are *absolutely* identical in terms of results they would return. I mean, `X >= A AND X <= B` is equivalent to `X BETWEEN A AND B`, and that's the only difference between your two queries. – Andriy M Feb 21 '14 at 16:45
  • Read carefully. The first query is X >= A AND Y <= B. The second query is X BETWEEN A AND B (with no stipulation on Y). – Curly_Jefferson Feb 21 '14 at 19:02