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
Detailed 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

Detailed Time Code by Employee Report

Selection Query:

SELECT EMP_ID FROM EMPLOYEE E,PAY_GROUP P
WHERE P.PAYGRP_ID=E.PAYGRP_ID and P.PAYGRP_ID=10005

Export Query:

SELECT     
    Employee_ID AS Employee_ID, 
    Full_Name AS Full_Name, 
    Work_Date AS Work_Date, 
    Time_Code AS Time_Code, 
    Hour_Type AS Hour_Type, 
    Team_Name AS Team_Name,
    Hours AS Hours, 
    Cost AS Cost
FROM
    (
    select
    	E.EMP_NAME as Employee_ID,
    	E.EMP_FULLNAME AS Full_Name,
    	WBT.WBT_NAME AS Team_Name,
        WD.WRKD_WORK_DATE AS Work_Date, 
        TC.TCODE_NAME AS Time_Code, 
        HT.HTYPE_NAME AS Hour_Type, 
        (CAST(WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60) * WD.WRKD_RATE AS Cost, 
        CAST(WD.WRKD_MINUTES AS DOUBLE PRECISION) / 60 AS Hours, 
        WD.WRKD_ID AS WRKD_ID, 
        WD.WBT_ID AS WBT_ID, 
        WD.WRKS_ID AS WRKS_ID
    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 PAY_GROUP PG 
    		ON PG.PAYGRP_ID = WS.PAYGRP_ID 
    		INNER JOIN CALC_GROUP CG
    		ON CG.CALCGRP_ID = WS.PAYGRP_ID 
WHERE E.EMP_ID IN (#selection#)
    	)DTE
     
WHERE
	DTE.Work_Date >= '2025-07-28 00:00:00.000'
		AND DTE.Work_Date <= '2025-08-10 00:00:00.000'	      
GROUP BY 
    DTE.Hours, 
    DTE.Cost, 
    DTE.Full_Name, 
    DTE.Employee_ID, 
    DTE.Work_Date, 
    DTE.Time_Code, 
    DTE.Hour_Type, 
    DTE.Team_Name, 
    DTE.WRKD_ID

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