Priya Singh Priya Singh - 1 month ago 11
SQL Question

look for consecutive values and give points accordingly

I have a table here : 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


In the "week No"column ,
I have data for 5 consecutive weeks,every week I want to check these conditions-

1.Every week I will check the current week's points.If the points value is -10 for the current week and the previous week's points is also -10 then -40 is added in table 2(below table) in totalpoints field of that particular Employee.(plz see EmployeeName column)

2.Again we check it for current week and prevoius two week's points.If the score is -10 for three weeks consecutively the reward -100 for that person and added to total points field in table 2.(below table)

3.
Similarly for consecutive four weeks i.e current week and the previous 3 week's if points is -10 ,then add -200 to table2's total points field.

employee ID Employee Name Total points
1 Bob -50
2 Deepak 110
3 Brinda 130
4 Chriss 210


Possible solution (faulty) :

; 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


Can anyone help me on how to achieve this? I'm using sql server 2012.

Answer

SQL DEMO

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

FINAL OUTPUT

enter image description here