[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

T-SQL Query Filtering

I have two queries below. The first query does not include rate information whereas the second does. The issue I am trying to get around is that for some project codes (dbo.PJLABDIS.project and dbo.PJPROJ.project, which are the same code) there is no rate information (in other words, the dbo.PJRATE.rate value is null), however, for the project codes that do have rates associated with them (or in other words, when the dbo.PJRATE.rate value is not null), there are often multiple rates per project code.

The reason why there are multiple rates per project code is that each dbo.PJRATE.rate also has a dbo.PJRATE.effect_date and there could be multiple rates associated with the same project code, just different effective dates. I want only the most recent dbo.PJRATE.rate (hence the most recent dbo.PJRATE.effect_date) for each project code based on the given time frame, which is determined in the WHERE clause statement:  (dbo.PJLABDIS.pe_date >= @Start_Date) AND (dbo.PJLABDIS.pe_date <= @End_Date). If the dbo.PJRATE.effect_date is not filtered, there are multiple old rates that are also returned for each project code.

The problem, however, is that I want BOTH the most recent rate for those project codes that have rates AND at the same time, not filter out any project codes that do not have any rate information associated with them (or in other words, dbo.PJRATE.rate is null).

The second query manages to get the most recent rate information for those project codes whose PJRATE.rate value is not null, but filters out those project codes that do not have any rate information. How can this be corrected?
QUERY 1
 
SELECT DISTINCT 
                      dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date, dbo.PJLABDIS.worked_hrs, 
                      SUM(CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 ELSE dbo.PJLABDIS.worked_hrs END) 
                      AS Billed_Hours, dbo.PJLABDIS.project, dbo.PJPROJ.project_desc, SUBSTRING(dbo.PJPROJ.project, 1, 6) AS ProjectSubCode
FROM         dbo.PJLABDIS INNER JOIN
                      dbo.PJPROJ ON dbo.PJLABDIS.project = dbo.PJPROJ.project INNER JOIN
                      dbo.PJEMPLOY ON dbo.PJLABDIS.employee = dbo.PJEMPLOY.employee
WHERE     (dbo.PJLABDIS.pe_date >= @Start_Date) AND (dbo.PJLABDIS.pe_date <= @End_Date)
GROUP BY dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date, dbo.PJLABDIS.worked_hrs, dbo.PJLABDIS.project, 
                      dbo.PJPROJ.project_desc, dbo.PJPROJ.project
ORDER BY dbo.PJLABDIS.pe_date, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.project
 
-------------------------------------------------------------------------------------------------------------
Query 2
 
SELECT DISTINCT 
                      dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date, SUM(dbo.PJLABDIS.worked_hrs) AS Total_Hours, 
                      dbo.PJLABDIS.worked_hrs, SUM(CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) 
                      WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 ELSE dbo.PJLABDIS.worked_hrs END) AS Billed_Hours, 
                      CASE dbo.PJLABDIS.rate_source WHEN 'S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillabeHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours END
                       AS Billable_Hours, dbo.PJLABDIS.rate_source, dbo.PJLABDIS.project, dbo.PJPROJ.project_desc, 
                      CASE dbo.PJLABDIS.rate_source WHEN 'S' THEN 1 ELSE 2 END AS Billable_Hours_Number, SUM(CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) 
                      WHEN 'INSCOP' THEN 0 ELSE dbo.PJLABDIS.worked_hrs END) 
                      / SUM(CASE dbo.PJLABDIS.rate_source WHEN 'S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillabeHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours
                       END) * 100 AS Percentage, SUBSTRING(dbo.PJPROJ.project, 1, 6) AS ProjectSubCode, dbo.PJRATE.rate
FROM         dbo.PJLABDIS INNER JOIN
                      dbo.PJEMPLOY ON dbo.PJLABDIS.employee = dbo.PJEMPLOY.employee INNER JOIN
                      dbo.PJPROJ ON dbo.PJLABDIS.project = dbo.PJPROJ.project INNER JOIN
                      dbo.PJBILLABLEHOURS ON dbo.PJLABDIS.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate INNER JOIN
                      dbo.PJRATE ON dbo.PJPROJ.rate_table_id = dbo.PJRATE.rate_table_id
WHERE     (dbo.PJLABDIS.pe_date >= @Start_Date) AND (dbo.PJLABDIS.pe_date <= @End_Date) AND (dbo.PJRATE.rate =
                          (SELECT     MAX(rate) AS Expr1
                            FROM          dbo.PJRATE AS PJRATE_1
                            WHERE      (rate_key_value1 = dbo.PJLABDIS.employee) AND (rate_key_value2 = dbo.PJLABDIS.project)))
GROUP BY dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date, dbo.PJBILLABLEHOURS.C2CBillableHours, 
                      dbo.PJBILLABLEHOURS.W2SalaryBillabeHours, dbo.PJLABDIS.pe_date, dbo.PJLABDIS.rate_source, dbo.PJLABDIS.project, dbo.PJPROJ.project_desc, 
                      dbo.PJLABDIS.worked_hrs, dbo.PJPROJ.project, dbo.PJRATE.rate
ORDER BY dbo.PJLABDIS.pe_date, dbo.PJEMPLOY.emp_name

Open in new window

0
rdracer58
Asked:
rdracer58
  • 4
  • 4
1 Solution
 
reb73Commented:
Try -

The check for MAX(Rate) has been altered to either to use an OR clause which should display records for which there is no rate information
SELECT DISTINCT 
	 PE.employee
	,PE.emp_name
	,PL.pe_date
	,SUM(PL.worked_hrs) AS Total_Hours
	,PL.worked_hrs
	,SUM(CASE SUBSTRING(PP.project, 1, 6) WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 ELSE PL.worked_hrs END) AS Billed_Hours 
	,CASE PL.rate_source WHEN 'S' THEN PB.W2SalaryBillabeHours ELSE PB.C2CBillableHours END AS Billable_Hours
	,PL.rate_source
	,PL.project
	,PP.project_desc 
	,CASE PL.rate_source WHEN 'S' THEN 1 ELSE 2 END AS Billable_Hours_Number
	,SUM(CASE SUBSTRING(PP.project, 1, 6) WHEN 'INSCOP' THEN 0 ELSE PL.worked_hrs END) / SUM(CASE PL.rate_source WHEN 'S' THEN PB.W2SalaryBillabeHours ELSE PB.C2CBillableHours END) * 100 AS Percentage
	,SUBSTRING(PP.project, 1, 6) AS ProjectSubCode
	,PR.rate
FROM
         dbo.PJLABDIS PL
INNER JOIN
	dbo.PJEMPLOY PE ON PL.employee = PE.employee
INNER JOIN
	dbo.PJPROJ PP ON PL.project = PP.project
INNER JOIN
	dbo.PJBILLABLEHOURS PB ON PL.pe_date = PB.TimePeriodDate
INNER JOIN
	dbo.PJRATE PR ON PP.rate_table_id = PR.rate_table_id
WHERE     	PL.pe_date 	>= @Start_Date
	AND 	PL.pe_date 	<= @End_Date
	AND (	PR.rate 	IS NULL 
	    OR  PR.rate = (SELECT  MAX(rate)
                           FROM    dbo.PJRATE
                           WHERE   rate_key_value1 = PL.employee
			   AND	   rate_key_value2 = PL.project
			  )
	    )
GROUP BY
	PE.employee, PE.emp_name, PL.pe_date, PB.C2CBillableHours, PB.W2SalaryBillabeHours, PL.pe_date, 
	PL.rate_source, PL.project, PP.project_desc, PL.worked_hrs, PP.project, PR.rate
ORDER BY
	PL.pe_date, PE.emp_name

Open in new window

0
 
rdracer58Author Commented:
I tried the query and have attached the first two lines of data returned. The Max rate is not being returned properly as for (1) I believe we need to compute based on the Max(effect_date) field from the PR.rate table as opposed to rate (usually it's the largest rate, however, we need to select the most recent rate number, which may or may not be in the time frame determined by the @Start_Date and @End_Date paramters) and (2) it still does not display the rows where there is no rate information. There should be a total of 4 rows returned for the employee/pe_date shown: two with a rate (which are currently shown) and two without a rate.
Data.pdf
0
 
reb73Commented:
How about this -

(I'm presuming the dbo.PJRate table has a field called effect_date which is not null unlike the PJRate.rate field)
DECLARE @Start_Date datetime, @End_Date datetime
 
SELECT DISTINCT 
	 PE.employee
	,PE.emp_name
	,PL.pe_date
	,SUM(PL.worked_hrs) AS Total_Hours
	,PL.worked_hrs
	,SUM(CASE SUBSTRING(PP.project, 1, 6) WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 ELSE PL.worked_hrs END) AS Billed_Hours 
	,CASE PL.rate_source WHEN 'S' THEN PB.W2SalaryBillabeHours ELSE PB.C2CBillableHours END AS Billable_Hours
	,PL.rate_source
	,PL.project
	,PP.project_desc 
	,CASE PL.rate_source WHEN 'S' THEN 1 ELSE 2 END AS Billable_Hours_Number
	,SUM(CASE SUBSTRING(PP.project, 1, 6) WHEN 'INSCOP' THEN 0 ELSE PL.worked_hrs END) / SUM(CASE PL.rate_source WHEN 'S' THEN PB.W2SalaryBillabeHours ELSE PB.C2CBillableHours END) * 100 AS Percentage
	,SUBSTRING(PP.project, 1, 6) AS ProjectSubCode
	,PR.rate
FROM
         dbo.PJLABDIS PL
INNER JOIN
	dbo.PJEMPLOY PE ON PL.employee = PE.employee
INNER JOIN
	dbo.PJPROJ PP ON PL.project = PP.project
INNER JOIN
	dbo.PJBILLABLEHOURS PB ON PL.pe_date = PB.TimePeriodDate
INNER JOIN
	dbo.PJRATE PR ON PP.rate_table_id = PR.rate_table_id
WHERE     	PL.pe_date 	>= @Start_Date
	AND 	PL.pe_date 	<= @End_Date
	AND	PR.effect_date	= (SELECT  MAX(effect_date)
				   FROM    dbo.PJRATE
				   WHERE   rate_key_value1 = PL.employee
				   AND	   rate_key_value2 = PL.project
				  )
GROUP BY
	PE.employee, PE.emp_name, PL.pe_date, PB.C2CBillableHours, PB.W2SalaryBillabeHours, PL.pe_date, 
	PL.rate_source, PL.project, PP.project_desc, PL.worked_hrs, PP.project, PR.rate
ORDER BY
	PL.pe_date, PE.emp_name

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rdracer58Author Commented:
Whenever PR.rate is null, PR.effect_date will be null as well, so we still need to account for that (I'm assuming in a similar fashion to your prior query?). I've attached the data your most recent query returned and it's obviously pulling multiple rates for the same pe_date and project for Alfred, which is incorrect. Could there potentially be problems in the joins? There should only be one rate per project per pe_date (generally speaking, rates increase as time goes on).
Data.pdf
0
 
reb73Commented:
What is the primary key for PJPROJ table? The join should be on this field, I can rework the query if you can provide this..
0
 
rdracer58Author Commented:
Please see the attached image. rate_key_value1 = employee and rate_key_value2 = project.
TableProperties.png
0
 
reb73Commented:
The tstamp column will do, should always return the most recent rate i guess..

Also the join on on PJPROJ should be a left join in case a rate is not defined..
SELECT	 DISTINCT 
         PE.employee
        ,PE.emp_name
        ,PL.pe_date
        ,SUM(PL.worked_hrs) AS Total_Hours
        ,PL.worked_hrs
        ,SUM(CASE SUBSTRING(PP.project, 1, 6) WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 ELSE PL.worked_hrs END) AS Billed_Hours 
        ,CASE PL.rate_source WHEN 'S' THEN PB.W2SalaryBillabeHours ELSE PB.C2CBillableHours END AS Billable_Hours
        ,PL.rate_source
        ,PL.project
        ,PP.project_desc 
        ,CASE PL.rate_source WHEN 'S' THEN 1 ELSE 2 END AS Billable_Hours_Number
        ,SUM(CASE SUBSTRING(PP.project, 1, 6) WHEN 'INSCOP' THEN 0 ELSE PL.worked_hrs END) / SUM(CASE PL.rate_source WHEN 'S' THEN PB.W2SalaryBillabeHours ELSE PB.C2CBillableHours END) * 100 AS Percentage
        ,SUBSTRING(PP.project, 1, 6) AS ProjectSubCode
        ,PR.rate
FROM
         dbo.PJLABDIS PL
INNER JOIN
        dbo.PJEMPLOY PE ON PL.employee = PE.employee
INNER JOIN
        dbo.PJPROJ PP ON PL.project = PP.project
INNER JOIN
        dbo.PJBILLABLEHOURS PB ON PL.pe_date = PB.TimePeriodDate
LEFT JOIN
        dbo.PJRATE PR ON PP.rate_table_id = PR.rate_table_id
WHERE           PL.pe_date      >= @Start_Date
        AND     PL.pe_date      <= @End_Date
        AND (   PR.tstamp 	IS NULL
	     OR PR.tstamp	= (SELECT  MAX(tstamp)
                                   FROM    dbo.PJRATE
                                   WHERE   rate_key_value1 = PL.employee
                                   AND     rate_key_value2 = PL.project
                                  )
	    )
GROUP BY
        PE.employee, PE.emp_name, PL.pe_date, PB.C2CBillableHours, PB.W2SalaryBillabeHours, PL.pe_date, 
        PL.rate_source, PL.project, PP.project_desc, PL.worked_hrs, PP.project, PR.rate
ORDER BY
        PL.pe_date, PE.emp_name

Open in new window

0
 
rdracer58Author Commented:
Excellent--that did the trick!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now