• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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

0
rdracer58
Asked:
rdracer58
  • 4
  • 4
  • 2
1 Solution
 
ErnariashCommented:
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

0
 
rdracer58Author Commented:
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?
0
 
ErnariashCommented:

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
 
0
Independent Software Vendors: 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!

 
folderolCommented:
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

0
 
rdracer58Author Commented:
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'."
0
 
folderolCommented:
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

0
 
rdracer58Author Commented:
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."
0
 
folderolCommented:
 dbo.dbo.PJLABDIS.pe_date,

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

so change to
  dbo.PJLABDIS.pe_date,



0
 
rdracer58Author Commented:
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

0
 
folderolCommented:
Change line 17 and 18 from

17   dbo.PJLABDIS.rate_source
18

     to

17  dbo.PJLABIDS.project,
18  dbo.PJLABDIS.rate_source

lines 15 - 18 were supposed to be the same as lines 23-26




0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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