Joins and case statements help
I am trying to create a case statement that will pick at what level each user in out system is.. Platinum, Gold, Silver, Bronze..... This query is going in to a SSRS report where the facilityCode will be selected from a drop down... I have been researching case statements and understand the very simplistic examples of how to create one, but i am not able to get it working w/ my querys that have joins.... This is my first query for the bronze level.
SELECT XEI.EmployeeId as id, SUM(Cr开发者_如何学JAVAedits) , XEI.IsSpouse
FROM Employees E
JOIN XREFEmployeesInvitations XEI ON E.Id = XEI.EmployeeId
JOIN Activities A ON XEI.ActivityId = A.Id
JOIN Locations l on l.Id=E.LocationId
JOIN Facilities f on f.Id=l.FacilityId
WHERE F.Code = COALESCE(@facilityCode, F.Code)
and E.Id IN (SELECT EmployeeId FROM XREFEmployeesInvitations WHERE ActivityId = 10 ANd IsCompleted = 1)
and XEI.IsCompleted=1
GROUP BY XEI.EmployeeId, XEI.IsSpouse
HAVING SUM(Credits) between 50 and 99
Try to wrap it in another projection, like this
select id,
"Status" =
case
when credits > 200 and credits < 300 then 'Silver'
when credits > 300 then 'Gold'
else 'Bronze'
end
, IsSpouse
from (
SELECT XEI.EmployeeId as id, SUM(Credits) as credits , XEI.IsSpouse as IsSpouse
FROM Employees E
JOIN XREFEmployeesInvitations XEI ON E.Id = XEI.EmployeeId
JOIN Activities A ON XEI.ActivityId = A.Id
JOIN Locations l on l.Id=E.LocationId
JOIN Facilities f on f.Id=l.FacilityId
WHERE F.Code = COALESCE(@facilityCode, F.Code)
and E.Id IN (SELECT EmployeeId FROM XREFEmployeesInvitations WHERE ActivityId = 10 ANd IsCompleted = 1)
and XEI.IsCompleted=1
GROUP BY XEI.EmployeeId, XEI.IsSpouse
HAVING SUM(Credits) between 50 and 99
)
精彩评论