b1nary0mega
7/7/2015 - 4:58 PM

Get all SUP Employee times, between specific date ranges, where there is at least 1 entry between Non Producitve and Productive Time codes.

Get all SUP Employee times, between specific date ranges, where there is at least 1 entry between Non Producitve and Productive Time codes.

Provide the list ordered ASC by group, last name, time working.

SELECT G.GROUP_NAME,
  P.MS_ALIAS,
  T.END_DTTM AS WORK_DATE,
  T.EMPLOYEE_ID,
  T.TIME_CODE    AS PRODUCTIVE,
  T.NP_TIME_CODE AS NONPRODUCTIVE
FROM EMPLOYEE_TIMES T
JOIN EMPLOYEE_PROFILE P
ON T.EMPLOYEE_ID       = P.EMPLOYEE_ID
AND P.UNIT_ID          = 'SUP'
AND (MSS_UNIT_TERM_DT IS NULL
OR MSS_UNIT_TERM_DT   >= TO_DATE('10-03-15 00:00:00','mm-dd-yy hh24:mi:ss') )
AND SMH_TERM_DT       IS NULL
JOIN GROUPS G
ON G.UNIT_ID          = 'SUP'
AND P.GROUP_CHOICE_ID = G.GROUP_ID
WHERE T.UNIT_ID       = 'SUP'
AND T.END_DTTM       >= TO_DATE('08-23-15 00:00:00','mm-dd-yy hh24:mi:ss')
AND T.END_DTTM       <= TO_DATE('10-03-15 00:00:00','mm-dd-yy hh24:mi:ss')
AND
  CASE
    WHEN (T.TIME_CODE  IS NULL
    AND T.NP_TIME_CODE IS NULL)
    THEN 0
    ELSE 1
  END = 1
ORDER BY P.GROUP_CHOICE_ID ASC,
  P.LAST_NAME ASC,
  P.FIRST_NAME ASC,
  T.END_DTTM ASC,
  T.EMPLOYEE_ID ASC;