Case Statement
-
Can I use a case statement in the Where clause of a select statement?
Thanks,
Ninel
-
Yes
-
Can you please give me an example?
-
SELECT 'I have a CASE in my WHERE'
WHERE (
CASE
WHEN 'Apples' = 'Oranges' Then 0
ELSE 1
END ) = 1
-
I need to do the following: Can a case statement help me here?
I have a table TABLE1. My company has 2 sites. This table contains employees with the amount of hours they worked on which project at which sites.
CREATE TABLE #TABLE1 (
Calldate varchar(10) NULL,
Employee varchar(10) NULL,
Project varchar(10) NULL,
Hours decimal(10,4) NULL,
Site varchar(1) NULL)
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '246', 'EACQ5', 3, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ5', 2, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 5, '2')
[/code]
I need to figure out the following:
I need the total hours of employees from both sites ONLY if they worked on a project that ended in a 5. If employees worked on projects that did not end in 5 I need the totals for their site only. A parameter of site will be passed to the stored procedure.
So for example: If site parameter of 1 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 1.5
If site parameter of 2 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5
Thanks,
Ninel
-
Your sample output does not match your stated requirements:
If site parameter of 2 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5
How can a project ending in '6' be in the resultset ? Projects that don't end in '5' get "rolled up" to the site level, therefore 'EACQ6' ending in 6 can't be in the resultset.
-
Declare @Site char(1)
set @Site = '1'
select Calldate,Project, sum(hours) as TotalHours
from #Table1
where (right(project,1) = '5'
or
site = @Site)
group by Calldate,Project
set @Site = '2'
select Calldate,Project, sum(hours) as TotalHours
from #Table1
where (right(project,1) = '5'
or
site = @Site)
group by Calldate,Project
Calldate,Project,TotalHours
20060217,EACQ5,5.0000
20060217,EACQ6,1.5000
20060217,EAUD5,2.5000
(3 row(s) affected)
Calldate,Project,TotalHours
20060217,EACQ5,5.0000
20060217,EACQ6,5.0000
20060217,EAUD5,2.5000
(3 row(s) affected)
-
I think maybe I miscommunicated.
In my sample there are 2 employees who have project EACQ6. One is at site 1 with 1.5 hours and the other is at site 2 with 5 hours. If I pass site 2 as a parameter I want totals from site 2 plus if project ends in 5 I want the totals from both sites.
Does that make sense?
-
Right, but this highlights my comment on requirements statement not matching sample data:
>>group by Calldate,Project
If you GROUP BY Project, and there is more than 1 Project at a site that doesn't end in 5, you end up with too many resultset rows.
We need an answer as to whether requirements statement is wrong, or posted sample output data is wrong.
-
hope this helps.....
declare @site varchar(3)
set @site='2'
select Calldate,Project,
totalhours=
case
when (right(project,1)) = '5' then sum(site1hours)+sum(site2hours)
when @site=2 then sum(site2hours)
when @site=1 then sum(site1hours)
end
from
(
select Calldate,Project,
site1hours= case when site=1 then sum(hours) else 0 end ,
site2hours= case when site=2 then sum(hours) else 0 end
from Table1
group by Calldate,Project,site
) as a
group by Calldate,Project
-Krishnan
-
I don't think it needs to be that complex:
Select Calldate, Project,
Sum(
Case
-- Add hours if Project ends in 5 or if at requested @Site
When (Project like '%5') Or (Site = @Site) Then Hours
Else 0
End
) As TotalHours
From YourTable
Group By Calldate, Project
Order By Calldate, Project
-
This works and allows the set of rows to be included to be reduced by the values in the where clause. Using some sort of case statment will force all the rows to have to be evaluated.
select calldate, project, sum(hours)
from @Table1
where Site = @Site or Project like '%5'
group by calldate, project
-
kewl
-Krishnan
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply