Selection and Export query examples

Use the examples in this section as is or as a reference guide for creating selection and export queries.

Examples include:

Time Code by Team Report
Time Code by Employee Report
Balance Summary Report
Work Summary Report
Balance Detail Report

Time Code by Team Report

Selection Query:

SELECT EMP_ID
FROM   EMPLOYEE E,
       PAY_GROUP P
WHERE  P.PAYGRP_ID = E.PAYGRP_ID
       and PAYGRP_NAME IN ('1')

Export Query:

SELECT E.EMP_FULLNAME AS FULL_NAME,
       E.EMP_NAME AS EMPLOYEE_ID,
       E.EMP_FIRSTNAME AS FIRST_NAME,
       E.EMP_LASTNAME AS LAST_NAME,
       TC.TCODE_NAME AS TIME_CODE,
       HT.HTYPE_NAME AS HOUR_TYPE,
       WBT.WBT_NAME AS TEAM_NAME,
       WS.WRKS_WORK_DATE,
       Sum(Cast(WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60) AS HOURS
from   EMPLOYEE E
       INNER JOIN WORK_SUMMARY WS
               ON E.EMP_ID = WS.EMP_ID
       INNER JOIN WORK_DETAIL WD
               ON WS.WRKS_ID = WD.WRKS_ID
       INNER JOIN WORKBRAIN_TEAM WBT
               ON WD.WBT_ID = WBT.WBT_ID
       INNER JOIN TIME_CODE TC
               ON WD.TCODE_ID = TC.TCODE_ID
       INNER JOIN HOUR_TYPE HT
               ON WD.HTYPE_ID = HT.HTYPE_ID
WHERE  WS.WRKS_WORK_DATE <= CURRENT_DATE
       AND WS.WRKS_WORK_DATE >= '09/01/2024'
       AND E.EMP_ID IN (#selection#)
GROUP  BY E.EMP_FULLNAME,
          E.EMP_NAME,
          E.EMP_FIRSTNAME,
          E.EMP_LASTNAME,
          TC.TCODE_NAME,
          HT.HTYPE_NAME,
          WBT.WBT_NAME,
          WS.WRKS_WORK_DATE
ORDER  BY E.EMP_LASTNAME ASC

Time Code by Employee Report

Selection Query:

SELECT EMP_ID
FROM   EMPLOYEE E,
       PAY_GROUP P
WHERE  P.PAYGRP_ID = E.PAYGRP_ID
       and PAYGRP_NAME IN ('1')

Export Query

SELECT E.EMP_FULLNAME AS FULL_NAME,
       E.EMP_NAME AS EMPLOYEE_ID,
       E.EMP_FIRSTNAME AS FIRST_NAME,
       E.EMP_LASTNAME AS LAST_NAME,
       TC.TCODE_NAME AS TIME_CODE,
       HT.HTYPE_NAME AS HOUR_TYPE,
       WBT.WBT_NAME AS TEAM_NAME,
       Sum(Cast(WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60) AS HOURS
from   EMPLOYEE E
       INNER JOIN WORK_SUMMARY WS
               ON E.EMP_ID = WS.EMP_ID
       INNER JOIN WORK_DETAIL WD
               ON WS.WRKS_ID = WD.WRKS_ID
       INNER JOIN WORKBRAIN_TEAM WBT
               ON WD.WBT_ID = WBT.WBT_ID
       INNER JOIN TIME_CODE TC
               ON WD.TCODE_ID = TC.TCODE_ID
       INNER JOIN HOUR_TYPE HT
               ON WD.HTYPE_ID = HT.HTYPE_ID
WHERE  WS.WRKS_WORK_DATE <= CURRENT_DATE
       AND WS.WRKS_WORK_DATE >= '09/01/2024'
       AND E.EMP_ID IN (#selection#)
GROUP  BY E.EMP_FULLNAME,
          E.EMP_NAME,
          E.EMP_FIRSTNAME,
          E.EMP_LASTNAME,
          TC.TCODE_NAME,
          HT.HTYPE_NAME,
          WBT.WBT_NAME
ORDER  BY E.EMP_LASTNAME ASC

Balance Summary Report

Selection Query:

Select WBT_ID
FROM   WORKBRAIN_TEAM WBT
WHERE  WBT.WBT_NAME in ('IT', 'CARDIO')

Export Query

SELECT E.EMP_NAME,
       B.BAL_NAME,
       Sum(EB.EMPBAL_VALUE) AS EMPBAL_VALUE,
       WS.WRKS_WORK_DATE
FROM   EMPLOYEE E,
       BALANCE B,
       WORK_SUMMARY WS,
       EMPLOYEE_BALANCE EB
WHERE  E.EMP_ID = WS.EMP_ID
       AND EB.BAL_ID = B.BAL_ID
       AND EB.EMP_ID = E.EMP_ID
       AND WBT.WBT_ID in (#selection#)
       AND WS.WRKS_WORK_DATE = CURRENT_DATE
GROUP  BY E.EMP_NAME,
          B.BAL_NAME,
          WS.WRKS_WORK_DATE

Work Summary Report

Selection Query:

SELECT EMP_ID
FROM   EMPLOYEE E,
       CALC_GROUP C
WHERE  C.CALCGRP_ID = E.CALCGRP_ID
       and CALCGRP_NAME = 'HOURLY'

Export Query:

SELECT WBT.WBT_NAME AS TEAM_NAME,
       E.EMP_NAME AS EMPLOYEE_ID,
       E.EMP_FULLNAME AS FULL_NAME,
       E.EMP_FIRSTNAME AS FIRST_NAME,
       E.EMP_LASTNAME AS LAST_NAME,
       WS.WRKS_WORK_DATE AS WORK_DATE,
       TC.TCODE_NAME AS TIME_CODE,
       HT.HTYPE_NAME AS HOUR_TYPE,
       WS.WRKS_AUTHORIZED AS IS_DAY_AUTHORIZED,
       sp.shftpat_name as SHIFTPATTERNNAME,
       --SUM(CAST(WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60) AS HOURS,
       MIN(CASE
             WHEN WD.WRKD_TYPE = 'P' THEN TIMESTAMP '3000-01-01 00:00:00'
             ELSE WD.WRKD_START_TIME
           END) AS START_TIME,
       MAX(CASE
             WHEN WD.WRKD_TYPE = 'P' THEN TIMESTAMP '1900-01-01 00:00:00'
             ELSE WD.WRKD_END_TIME
           END) AS END_TIME,
       SUM(CASE
             WHEN HT.HTYPE_RPT_ISOT = 'N' THEN CAST(WD.WRKD_MINUTES AS DOUBLE PRECISION) /60
             ELSE 0
           END) AS Regular,
       SUM(CASE
             WHEN HT.HTYPE_RPT_ISOT = 'Y' THEN CAST(
             WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60
             ELSE 0
           END) AS Overtime,
       SUM(CASE
             WHEN HT.HTYPE_MULTIPLE <= 0.0 THEN CAST(
             WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60
             ELSE 0
           END) AS Total_Unpaid_Time,
       SUM(CASE WHEN HT.HTYPE_RPT_ISOT = 'N' THEN CAST(WD.WRKD_MINUTES AS DOUBLE
       PRECISION) / 60
       ELSE 0 
       END) + SUM(CASE WHEN HT.HTYPE_RPT_ISOT = 'Y' THEN CAST(WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60 
       ELSE 0 END) AS Total_Paid_Time
FROM   EMPLOYEE E
       INNER JOIN WORK_SUMMARY WS
               ON E.EMP_ID = WS.EMP_ID
       INNER JOIN WORK_DETAIL WD
               ON WS.WRKS_ID = WD.WRKS_ID
       INNER JOIN WORKBRAIN_TEAM WBT
               ON WD.WBT_ID = WBT.WBT_ID
       INNER JOIN TIME_CODE TC
               ON WD.TCODE_ID = TC.TCODE_ID
       INNER JOIN HOUR_TYPE HT
               ON WD.HTYPE_ID = HT.HTYPE_ID
       INNER JOIN shift_pattern sp
               ON E.SHFTPAT_ID = sp.SHFTPAT_ID
WHERE  WS.WRKS_WORK_DATE <= CURRENT_DATE
       AND WS.WRKS_WORK_DATE >= '09/01/2024'
       AND E.EMP_ID IN (#selection#)
GROUP  BY E.EMP_FULLNAME,
          E.EMP_NAME,
          E.EMP_FIRSTNAME,
          E.EMP_LASTNAME,
          TC.TCODE_NAME,
          HT.HTYPE_NAME,
          WBT.WBT_NAME,
          WS.WRKS_WORK_DATE,
          WS.WRKS_AUTHORIZED,
          SP.SHFTPAT_NAME
ORDER  BY E.EMP_LASTNAME ASC

Balance Detail Report

Selection Query:

SELECT S.EMP_ID
FROM   SEC_EMPLOYEE S
       INNER JOIN WORKBRAIN_TEAM WBT
               ON S.WBT_ID = WBT.WBT_ID
WHERE  WBT.WBT_NAME in ('IT', 'CARDIO')

Export Query:

SELECT E.EMP_NAME,
       B.BAL_NAME,
       Sum(EB.EMPBAL_VALUE) AS BALANCE_VALUE,
       WS.WRKS_WORK_DATE
FROM   EMPLOYEE E,
       BALANCE B,
       WORK_SUMMARY WS,
       EMPLOYEE_BALANCE EB
WHERE  E.EMP_ID = WS.EMP_ID
       AND EB.BAL_ID = B.BAL_ID
       AND EB.EMP_ID = E.EMP_ID
       and EB.EMP_ID IN (#selection#)
       AND WS.WRKS_WORK_DATE = CURRENT_DATE
GROUP  BY E.EMP_NAME,
          B.BAL_NAME,
          WS.WRKS_WORK_DATE