查找连续的值并相应地给出点
查找连续的值并相应地给出点
提问于 2016-11-02 14:01:51
我这里有张桌子: table1
employee ID Reporting Manager EmployeeName Week No. Points
1 Mr. A Bob week1 -10
2 Mr. A Deepak week1 50
3 Mr. B Brinda week1 60
4 Mr. B Chriss week1 -10
1 Mr. A Bob week2 -10
2 Mr. A Deepak week2 40
3 Mr. B Brinda week2 20
4 Mr. B Chriss week2 90
1 Mr. A Bob week3 -10
2 Mr. A Deepak week3 30
3 Mr. B Brinda week3 40
4 Mr. B Chriss week3 50
1 Mr. A Bob week4 -10
2 Mr. A Deepak week4 -10
3 Mr. B Brinda week4 -10
4 Mr. B Chriss week4 30
1 Mr. A Bob week5 -10
2 Mr. A Deepak week5 -10
3 Mr. B Brinda week5 20
4 Mr. B Chriss week5 50在“无一周”栏中，我连续5个星期都有数据，每周我都要检查这些情况-
1.每周我都会检查当周的points.If值-10，前一周的积分也是-10，然后-40在表2(下表)中添加到该特定员工的总分字段中(请参阅EmployeeName列)。
2.我们再次检查当前周和前两周的points.If，得分为-10分，连续三周为奖励-100分，加到表2中的总分字段中。(下表)
- 类似地，连续四周，即当前一周和前3周的如果分数是-10，那么在表2的总分字段中加上-200。
employee ID Employee Name Total points
1 Bob -50
2 Deepak 110
3 Brinda 130
4 Chriss 210可能的解决方案(错误) :
; WITH
n as (
select [EmployeeName], CAST(SUBSTRING([Week No.],5,10) AS INT) as wk, Points as pt
from YourTable --> Change this to your table
),
sc as (
select w.*, case w.pt when -10 then (case w1.pt when -10 then (case w2.pt when -10 then (case w3.pt when -10 then -200 else -100 end) else -40 end) else 0 end) else 0 end x
from n w
left join n w1 on w.[EmployeeName] = w1.[EmployeeName] and w.wk = w1.wk+1
left join n w2 on w.[EmployeeName] = w2.[EmployeeName] and w.wk = w2.wk+2
left join n w3 on w.[EmployeeName] = w3.[EmployeeName] and w.wk = w3.wk+3
),
l as (
select *, pt+x as total
from sc
),
s as (
select [EmployeeName], sum(total) total
from l
group by [EmployeeName]
)
select *
from s有人能帮我解决这个问题吗？我正在使用sql server 2012。
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-11-02 14:37:51
SQL演示
WITH cte as (
SELECT *,
LAG([Points], 1) OVER (PARTITION BY [employee ID] ORDER BY [Week No]) as prev1_points,
LAG([Points], 2) OVER (PARTITION BY [employee ID] ORDER BY [Week No]) as prev2_points,
LAG([Points], 3) OVER (PARTITION BY [employee ID] ORDER BY [Week No]) as prev3_points
FROM employee
)
SELECT *,
CASE WHEN [Points] = -10 AND prev1_points = -10 AND prev2_points = -10 AND prev3_points = -10
THEN -200
WHEN [Points] = -10 AND prev1_points = -10 AND prev2_points = -10
THEN -100
WHEN [Points] = -10 AND prev1_points = -10
THEN -50
ELSE 0
END penalty
FROM cte最终输出
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40381873
复制相关文章
相似问题
腾讯云开发者
