CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
chris
chris

Reputation: 37440

Oracle: How can I determine if there are gaps records based on a date field?

I have an application that manages employee time sheets.

My tables look like:

TIMESHEET
  TIMESHEET_ID
  EMPLOYEE_ID
  etc

TIMESHEET_DAY:
  TIMESHEETDAY_ID
  TIMESHEET_ID
  DATE_WORKED
  HOURS_WORKED

Each time sheet covers a 14 day period, so there are 14 TIMESHEET_DAY records for each TIMESHEET record. And if someone goes on vacation, they do not need to enter a timesheet if there are no hours worked during that 14 day period.

Now, I need to determine whether or not employees have a 7 day gap in the prior 6 months. This means I have to look for either 7 consecutive TIMESHEET_DAY records with 0 hours, OR a 7 day period with a combination of no records submitted and records submitted with 0 hours worked. I need to know the DATE_WORKED of the last TIMESHEET_DAY record with hours in that case.

The application is asp.net, so I could retrieve all of the TIMESHEET_DAY records and iterate through them, but I think there must be a more efficient way to do this with SQL.

Upvotes: 0

Views: 839

Answers (2)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

SELECT t1.EMPLOYEE_ID, t1.TIMESHEETDAY_ID, t1.DATE_WORKED
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY  EMPLOYEE_ID, TIMESHEETDAY_ID ORDER BY DATE_WORKED) AS RowNumber,
            EMPLOYEE_ID, TIMESHEETDAY_ID, DATE_WORKED
      FROM (SELECT EMPLOYEE_ID, TIMESHEETDAY_ID, DATE_WORKED       
            FROM  TIMESHEET_DAY d
                  INNER JOIN TIMESHEET t ON t.TIMESHEET_ID = d.TIMESHEET_ID
            GROUP BY EMPLOYEE_ID, TIMESHEETDAY_ID, DATE_WORKED
            HAVING SUM(HOURS_WORKED) > 0) t ) t1
     INNER JOIN      
     (SELECT ROW_NUMBER() OVER(PARTITION BY  EMPLOYEE_ID, TIMESHEETDAY_ID ORDER BY DATE_WORKED) AS RowNumber,
            EMPLOYEE_ID, TIMESHEETDAY_ID, DATE_WORKED
      FROM (SELECT EMPLOYEE_ID, TIMESHEETDAY_ID, DATE_WORKED       
            FROM  TIMESHEET_DAY d
                  INNER JOIN TIMESHEET t ON t.TIMESHEET_ID = d.TIMESHEET_ID
            GROUP BY EMPLOYEE_ID, TIMESHEETDAY_ID, DATE_WORKED
            HAVING SUM(HOURS_WORKED) > 0) t ) t2 ON t1.RowNumber = t2.RowNumber + 1
WHERE t2.DATE_WORKED - t1.DATE_WORKED >= 7

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

I would do it at least partly with SQL by counting the days per timesheet and then do the rest of the logic in the program. This identifies time sheets with missing days:

SELECT * FROM
    (SELECT s.timesheet_id, SUM(CASE WHEN d.hours_worked > 0 THEN 1 ELSE 0 END) AS days_worked
    FROM
        TimeSheet s
        LEFT JOIN TimeSheet_Day d
            ON s.timesheet_id = d.timesheet_id
    GROUP BY
        s.timesheet_id
    HAVING SUM(CASE WHEN d.hours_worked > 0 THEN 1 ELSE 0 END) < 14) X
    INNER JOIN TimeSheet
        ON TimeSheet.timesheet_id = X.timesheet_id
    LEFT JOIN TimeSheet_Day
        ON TimeSheet.timesheet_id = TimeSheet_Day.timesheet_id
    ORDER BY
        TimeSheet.employee_id, TimeSheet.timesheet_id, TimeSheet_Day.date_worked

Upvotes: 0

Related Questions