Workgroup Transactions Report

Report that provides a detailed view of employee workgroup transactions within a specified date range, displaying employee information alongside workgroup details including start times, end times, and hours worked

Parameters

@min - Start date for the query (example: '6/18/2020')
@max - End date for the query (example: '7/1/2020')

Data Components

The report consists of one main section that joins employee data with their workgroup transactions and the associated workgroup hierarchies (WG1-WG7).

Output Format

The report outputs a table with employee information, event dates, workgroup names, and time details.

Column	Description
lastname	Employee's last name
firstname	Employee's first name
IDNUM	Employee's ID number
EVENTDATE	Date of the workgroup transaction
NAME (WG7)	Name of Workgroup 7
NAME (WG1)	Name of Workgroup 1
NAME (WG2)	Name of Workgroup 2
NAME (WG3)	Name of Workgroup 3
NAME (WG4)	Name of Workgroup 4
NAME (WG5)	Name of Workgroup 5
NAME (WG6)	Name of Workgroup 6
STARTTIME	Start time of the transaction
ENDTIME	End time of the transaction
HOURS	Number of hours for the transaction

Technical Implementation

The script uses:

Date range filtering with the BETWEEN operator
Multiple inner joins to connect employee data with workgroup information
Ordering by last name, first name, and event date

T-SQL

workgroup_transactions.sql

declare @min as datetime, @max as datetime


set @min = {mindate}
set @max = {maxdate}
/*
Set @min = '6/18/2020'
Set @max = '7/1/2020'
*/
Select E.lastname, e.FIRSTNAME, e.IDNUM, wt.EVENTDATE, w7.NAME, w1.NAME, w2.NAME, w3.NAME, w4.NAME, w5.NAME, w6.NAME, wt.STARTTIME, wt.ENDTIME, wt.HOURS

From EMPLOYEES e
    inner join WORKGROUPTRANS wt
    on e.FILEKEY = wt.FILEKEY
    inner join WORKGROUP1 w1
    on wt.WG1 = w1.WGNUM
    inner join WORKGROUP2 w2
    on wt.WG2 = w2.WGNUM
    inner join WORKGROUP3 w3
    on wt.WG3 = w3.WGNUM
    inner join WORKGROUP4 w4
    on wt.WG4 = w4.WGNUM
    inner join WORKGROUP5 w5
    on wt.WG5 = w5.WGNUM
    inner join WORKGROUP6 w6
    on wt.WG6 = w6.WGNUM
    inner join WORKGROUP7 w7
    on wt.WG7 = w7.WGNUM

where wt.EVENTDATE between @min and @max

order by 1,2,4

Content Inventory

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