Working Now Including Transferred Employees

Report that generates a list of employees currently working, including those who have been transferred, organized by workgroup hierarchy with employee counts per workgroup combination

Parameters

The report does not require explicit parameters. It automatically uses the system's security date from the AEINFORMATION table.

Data Components

The report consists of several Common Table Expressions (CTEs) that build upon each other:

EmployeesCurrentlyworking - Identifies employees with recent activity based on the security date
WGfromSchOrEdit - Retrieves workgroup information from schedules or edits
employeespunchedbutnotschoredit - Identifies employees who punched but have no schedule or edits
RefinedList - Combines the two sets of workgroup information
WorkingWG - Joins employee data with workgroup information
CTWGs - Provides counts of employees by workgroup combinations

Output Format

The report outputs a combined result set with employee details grouped by workgroup hierarchies.

Column	Description
FILEKEY	Employee's file key identifier (NULL for count rows)
wgCount	Count of employees in workgroup (NULL for employee rows)
EmpName	Concatenated employee name and ID
WG1	Workgroup 1 number
WG1Name	Workgroup 1 name
WG2	Workgroup 2 number
WG2Name	Workgroup 2 name
WG3	Workgroup 3 number
WG3Name	Workgroup 3 name

Technical Implementation

The script uses:

Multiple Common Table Expressions (CTEs) for a modular approach
UNION operations to combine different data sets
Conditional grouping to provide both detailed employee records and summary counts
Dynamic filtering based on system dates

T-SQL

working_now_including_transfered_emp.sql

with
    EmployeesCurrentlyworking
    as
    (
        select
            -- at.OWNERID,
            at.FILEKEY,
            -- at.DTTMSTAMP,
            at.EFFDATE
        from
            ARCTRANSACTIONS at
        where 
              dateadd(day,1,at.EFFDATE) > (select value
            from AEINFORMATION
            where name like '%securitydate%')
            and at.OWNERID in (Select at.OWNERID
            from ARCTRANSACTIONS at
            where dateadd(day,1,at.EFFDATE) > (select value
            from AEINFORMATION
            where name like '%securitydate%')
            group by at.OWNERID, at.FILEKEY
            having count(at.OWNERID) in (1,3,5))
    ),

    WGfromSchOrEdit
    as
    (
                    select
                s.FILEKEY,
                s.SCHDATE,
                s.WG1,
                s.WG2,
                s.WG3
            from
                SCHEDULES s
                inner join EmployeesCurrentlyworking  ECW
                on s.FILEKEY = ECW.FILEKEY and s.SCHDATE=ECW.EFFDATE
        Union All
            -- WGfromEdit
            select
                s.FILEKEY,
                s.EFFDATE,
                s.WG1,
                s.WG2,
                s.WG3
            from
                SUPEDITS s
            where 
              EDITTYPE=101
    ),
    employeespunchedbutnotschoredit
    as
    (
        select
            ecw.FILEKEY,
            ecw.EFFDATE,
            e.WG1,
            e.WG2,
            e.WG3
        from
            EmployeesCurrentlyworking ecw
            inner join EMPLOYEES e
            on ecw.FILEKEY=e.FILEKEY
        where  
              ecw.FILEKEY not in (select FILEKEY
        from WGfromSchOrEdit)
    ),
    RefinedList
    as
    (
                    select *
            from WGfromSchOrEdit
        Union ALL
            select *
            from employeespunchedbutnotschoredit
    ),
    WorkingWG
    as
    (

        select
            wg.FILEKEY,
            null as wgCount,
            e.LASTNAME+' ,'+ e.FIRSTNAME +'#'+e.IDNUM as EmpName,
            -- WG.SCHDATE,
            wg.WG1,
            w1.NAME as WG1Name,
            wg.WG2,
            w2.NAME as WG2Name,
            wg.WG3,
            w3.NAME as WG3Name
        from
            RefinedList  WG
            inner join WORKGROUP1 w1
            on wg.WG1=w1.WGNUM
            inner join WORKGROUP2 w2
            on wg.WG2=w2.WGNUM
            inner join WORKGROUP3 w3
            on wg.WG3=w3.WGNUM
            inner join EMPLOYEES e
            on wg.FILEKEY=e.FILEKEY
        -- order by w1.NAME, w2.NAME, w3.NAME
    ),
    CTWGs
    as
    (
        select
            null as FK,
            COUNT(wwg.filekey) as ct ,
            Null as EmpName,
            wwg.WG1,
            wwg.WG1Name,
            wwg.WG2,
            wwg.WG2Name,
            wwg.WG3,
            wwg.WG3Name
        from
            WorkingWG WWG
        group by 
              wwg.WG1,
              wwg.WG1Name,
              wwg.WG2,
              wwg.WG2Name,
              wwg.WG3,
              wwg.WG3Name
    )
    select
        *
    from
        WorkingWG
Union All
    select
        *
    from
        CTWGs
order by 
       WG1Name,
       WG2Name,
       WG3Name,
       EmpName

Content Inventory

Doc File: content/docs/reports/employee_information/working_now_including_transfered_emp.mdx
SQL Script: SQL/reports/employee_information/working_now_including_transfered_emp.sql