I am trying to write a single SQL statement that will return to me a list of
Timesheet Charge codes that match the following criteria. There are about 100,000 rows to choose from and at most I should get back
* The status (CHARGE.CHR_STATUS) must be = 'Opened'
* The charge description CHARGE.CHR_DESC cannot contain the letters '_WP_' or '_WM_'
* In the case where the project name TCPROJ.PRJ_NAME begins with 'ABC' then I only want the charge codes
that are assigned to me as per the employee doing the timesheet.
Although this was written for SQL Server, it must work in SQL Server, Oracle and MySql
Resource Unique Key TCRES.RES_KEY NUMERIC(11)
Employee Unique Key EMPLOYEE.EMP_KEY NUMERIC(11)
Employee Resource Key EMPLOYEE.EMP_RES NUMERIC(11) foreign key to TCRES.RES_KEY
TSHEADER (Timesheet Header) [This table is the active table at the time the SQL is run so we always append 'AND TSH_KEY=12345' to the WHERE clause so we get the right Employee Key
Timsheet Unique Key TSHEADER.TSH_KEY NUMERIC(11)
Timesheet Employee Key TSHEADER.TSH_EMP NUMERIC(11) foreign key to EMPLOYEE.EMP_KEY
Project Unique Key TCPROJ.PRJ_KEY NUMERIC(11)
Project Name TCPROJ.PRJ_NAME VARCHAR(30)
CHARGE (Charge Codes)
Charge Unique Key CHARGE.CHR_KEY NUMERIC(11)
Charge Code CHARGE.CHR_CODE VARCHAR(100)
Charge Description CHARGE.CHR_DESC VARCHAR(50)
Charge Status CHARGE.CHR_STATUS VARCHAR(8)
Charge Project Key CHARGE.CHR_PRJ NUMERIC(11) foreign key to TCPROJ.PRJ_KEY
CHRRES (Assigned charge codes)
Assignment Unique Key CHRRES.CRS_KEY NUMERIC(11)
Assignment Charge Key CHRRES.CRS_CHR NUMERIC(11) foreign key to CHARGE.CHR_KEY
Assignment Resource Key CHRRES.CRS_RES NUMERIC(11) foreign key to TCRES.RES_KEY
SQL to SELECT out CHR_KEY so
SELECT CHR_KEY FROM CHARGE .....
TSHEADER.TSH_KEY is always assigned a value so somewhere in the SELECT we must include 'AND TSH_KEY=12345' (note the key is not staic, but assigned by the application)
The TCPROJ.PRJ_KEY is also assigned a non static value by the applicaiton depending on the project the user has previously selected. So in the example below the key is 1. This value is also used by the CHARGE.CHR_PRJ as that is the relationship.
LEFT JOIN TCPROJ ON TCPROJ.PRJ_KEY = CHARGE.CHR_KEY
JOIN EMPLOYEE ON EMPLOYEE.EMP_KEY = TSHEADER.TSH_EMP
LEFT JOIN TCRES ON TCRES.RES_KEY = EMPLOYEE.EMP_RES
LEFT JOIN CHRRES ON (CHRRES.CRS_CHR = CHARGE.CHR_KEY AND CHRRES.CRS_RES = TCRES.RES_KEY)
We've tried several ways and the closest we've come to is this, but still delivers more rows than we'd expect primarily the "CHR_DESC NOT LIKE '%_WM_%' AND CHR_DESC NOT LIKE '%_WP_%' " is failing
SELECT CHR_KEY, CHR_CODE, CHR_DESC, CHR_PARENT, CHR_PRJ FROM CHARGE WHERE CHR_PRJ=1 AND CHR_KEY IN
(SELECT CHR_KEY FROM CHARGE WHERE CHR_STATUS<>'Closed' AND ((CHR_KEY IN (SELECT CRS_CHR FROM CHRRES, EMPLOYEE, TSHEADER WHERE CRS_RES=EMP_RES AND EMP_KEY=TSH_EMP AND TSH_KEY=1) AND(
CHR_PRJ IN (SELECT PRJ_KEY FROM TCPROJ WHERE PRJ_NAME LIKE 'ABC%')) OR (CHR_PRJ IS NULL OR (CHR_DESC NOT LIKE '%_WM_%' AND CHR_DESC NOT LIKE '%_WP_%' AND CHR_PRJ NOT IN (SELECT PRJ_KEY FROM TCPROJ WHERE PRJ_NAME LIKE 'ABC%'))))))