asked on

Complex CASE Statements

I am using the attached T-SQL query in a SQL Server 2005 Reporting Services report. The challenge is that whenever Billed_Hours = 0, I also need Billable_Hours to equal 0. What is the best way of accomplishing this? Would this best be accomplished within the query itself or somewhere in the report?

SELECT     dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date, SUM(CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) 
                      WHEN 'INSCOP' THEN 0 ELSE dbo.PJLABDIS.worked_hrs END) AS Billed_Hours, 
                      CASE dbo.PJLABDIS.rate_source WHEN 'S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillableHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours END
                       AS Billable_Hours, dbo.PJLABDIS.rate_source, dbo.PJPROJ.project
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
GROUP BY dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date, dbo.PJBILLABLEHOURS.C2CBillableHours, 
                      dbo.PJBILLABLEHOURS.W2SalaryBillableHours, dbo.PJLABDIS.pe_date, dbo.PJLABDIS.rate_source, dbo.PJPROJ.project
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date

Open in new window

Ernariash

Hello,
From your query I have found some problems with the select and the group by clause. See if the query works for you...thanks,

SELECT     dbo.PJEMPLOY.employee, 
dbo.PJEMPLOY.emp_name,
SUM(CASE WHEN SUBSTRING(dbo.PJPROJ.project, 1, 6)='INSCOP' THEN 0 ELSE dbo.PJLABDIS.worked_hrs END) AS Billed_Hours,
CASE WHEN SUM(CASE WHEN SUBSTRING(dbo.PJPROJ.project, 1, 6)='INSCOP' THEN 0 ELSE dbo.PJLABDIS.worked_hrs END) =  0 
THEN 0 ELSE SUM(CASE WHEN dbo.PJLABDIS.rate_source='S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillableHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours END) END AS Billable_Hours,
dbo.PJLABDIS.pe_date, 
dbo.PJLABDIS.rate_source,
dbo.PJPROJ.project
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
GROUP BY dbo.PJEMPLOY.employee, 
			dbo.PJEMPLOY.emp_name, 
			dbo.PJLABDIS.pe_date, 
 
			
			dbo.PJLABDIS.pe_date, 
			dbo.PJLABDIS.rate_source, 
			dbo.PJPROJ.project
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJLABDIS.pe_date

Open in new window

rdracer58

ASKER

The new query seems to produce erroenous results--occasionally doubling or tripling the correct value of the Billable_Hours column. Does this perhaps happen when the first CASE statement for Billable_Hours is false, and hence is sums the value of worked_hours? Any other insights?

Ernariash

I do not know your requirement or your data; I will need more details.
I have removed from the group by
dbo.PJBILLABLEHOURS.C2CBillableHours and dbo.PJBILLABLEHOURS.W2SalaryBillableHours
You may need to group by them. You are sum by them,
The Billable_Hours results are: When Billed_Hours is 0 then is 0, Else Sum if rate_source='S' sum the W2SalaryBillableHours else the C2CBillableHours

folderol

Couple general things, I hope they are appropriate....
First, you have no Where clause. From that, it would appear your query would be better if broken in two, Since the Employee table and the project table add nothing to the filtering, selecting, calculating, and are only there to add info columns, they don't need to be in the "work" query. The report is more legible, and the case is simplified because you can refer to the worked_hours column since it was defined in the inner nested select.

The big thing, the sum of Billable hours is zero when the sum of Billed hours is zero. If you wanted something different, then this snippet is not it.

 
SELECT 
dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, 
BillTo.pe_date, 
CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) WHEN 'INSCOP' THEN 0 ELSE Worked_hrs end as Billed_Hours,
case when SUBSTRING(dbo.PJPROJ.project, 1, 6) = 'INSCOP' THEN 0 
     when Worked_hrs = 0 then 0 
     else Billable_Hours end as BIllable_Hours,
rate_source, 
dbo.PJPROJ.project
From
(
Select 
  Sum(dbo.PJLABDIS.worked_hrs) as worked_hrs,
  SUM(CASE WHEN dbo.PJLABDIS.rate_source='S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillableHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours END) END AS Billable_Hours,
  dbo.dbo.PJLABDIS.pe_date,
  dbo.PJLABDIS.employee,
  dbo.PJLABDIS.rate_source
 
FROM
  dbo.PJLABDIS 
  INNER JOIN dbo.PJBILLABLEHOURS ON dbo.PJLABDIS.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
Group By
  dbo.PJLABDIS.pe_date,
  dbo.PJLABDIS.employee,
  dbo.PJLABDIS.project,
  dbo.PJLABDIS.rate_source
) 
as BillTo
INNER JOIN dbo.PJEMPLOY ON BillTo.employee = dbo.PJEMPLOY.employee 
INNER JOIN dbo.PJPROJ ON Billto.project = dbo.PJPROJ.project 
Order By 
dbo.PJEMPLOY.emp_name, BillTo.pe_date

Open in new window

rdracer58

ASKER

Whenever I try to run folderol's query, I receive an error stating "Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'END'."

folderol

Typo on line 14, two consecutive ENDS.....

SELECT 
dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, 
BillTo.pe_date, 
CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) WHEN 'INSCOP' THEN 0 ELSE Worked_hrs end as Billed_Hours,
case when SUBSTRING(dbo.PJPROJ.project, 1, 6) = 'INSCOP' THEN 0 
     when Worked_hrs = 0 then 0 
     else Billable_Hours end as BIllable_Hours,
rate_source, 
dbo.PJPROJ.project
From
(
Select 
  Sum(dbo.PJLABDIS.worked_hrs) as worked_hrs,
  SUM(CASE WHEN dbo.PJLABDIS.rate_source='S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillableHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours END) AS Billable_Hours,
  dbo.dbo.PJLABDIS.pe_date,
  dbo.PJLABDIS.employee,
  dbo.PJLABDIS.rate_source
 
FROM
  dbo.PJLABDIS 
  INNER JOIN dbo.PJBILLABLEHOURS ON dbo.PJLABDIS.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
Group By
  dbo.PJLABDIS.pe_date,
  dbo.PJLABDIS.employee,
  dbo.PJLABDIS.project,
  dbo.PJLABDIS.rate_source
) 
as BillTo
INNER JOIN dbo.PJEMPLOY ON BillTo.employee = dbo.PJEMPLOY.employee 
INNER JOIN dbo.PJPROJ ON Billto.project = dbo.PJPROJ.project 
Order By 
dbo.PJEMPLOY.emp_name, BillTo.pe_date

Open in new window

rdracer58

ASKER

I had tried making the same edit earlier, but now get an error message stating "Msg 107, Level 16, State 2, Line 1 The column prefix 'dbo.dbo.PJLABDIS' does not match with a table name or alias name used in the query."

folderol

dbo.dbo.PJLABDIS.pe_date,

That's on line 15, this is an error,

so change to
dbo.PJLABDIS.pe_date,

rdracer58

ASKER

Now another interesting error: Msg 207, Level 16, State 3, Line 1 Invalid column name 'project'.

There isn't a column name 'project' in line 1, and all the other references to "project" at least seem to be valid.

SELECT 
dbo.PJEMPLOY.employee, dbo.PJEMPLOY.emp_name, 
BillTo.pe_date, 
CASE SUBSTRING(dbo.PJPROJ.project, 1, 6) WHEN 'INSCOP' THEN 0 ELSE Worked_hrs END AS Billed_Hours,
case when SUBSTRING(dbo.PJPROJ.project, 1, 6) = 'INSCOP' THEN 0 
     when Worked_hrs = 0 then 0 
     else Billable_Hours end as BIllable_Hours,
rate_source, 
dbo.PJPROJ.project
From
(
Select 
  Sum(dbo.PJLABDIS.worked_hrs) as worked_hrs,
  SUM(CASE WHEN dbo.PJLABDIS.rate_source='S' THEN dbo.PJBILLABLEHOURS.W2SalaryBillableHours ELSE dbo.PJBILLABLEHOURS.C2CBillableHours END) AS Billable_Hours,
  dbo.PJLABDIS.pe_date,
  dbo.PJLABDIS.employee,
  dbo.PJLABDIS.rate_source
 
FROM
  dbo.PJLABDIS 
  INNER JOIN dbo.PJBILLABLEHOURS ON dbo.PJLABDIS.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
Group By
  dbo.PJLABDIS.pe_date,
  dbo.PJLABDIS.employee,
  dbo.PJLABDIS.project,
  dbo.PJLABDIS.rate_source
) 
as BillTo
INNER JOIN dbo.PJEMPLOY ON BillTo.employee = dbo.PJEMPLOY.employee 
INNER JOIN dbo.PJPROJ ON Billto.project = dbo.PJPROJ.project 
Order By 
dbo.PJEMPLOY.emp_name, BillTo.pe_date

Open in new window

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial