Priya Singh - 1 year ago 76
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.

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download