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;