开发者

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
) 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜