连接和 case 语句有帮助

发布于 2024-10-18 13:16:06 字数 692 浏览 2 评论 0原文

我正在尝试创建一个案例语句，该语句将选择系统中每个用户的级别..白金、黄金、白银、青铜.....此查询将进入 SSRS 报告，其中将从中选择设施代码一个下拉列表...我一直在研究案例陈述并了解如何创建一个案例陈述的非常简单的示例，但我无法让它与我的具有连接的查询一起工作...这是我的第一个查询青铜级。

SELECT XEI.EmployeeId as id, SUM(Credits) , 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

原文

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(Credits) , 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

东风软 2024-10-25 13:16:06

尝试将其包裹在另一个投影中，就像这样

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
)

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 原文

~没有更多了~