Shivam Sharma Shivam Sharma - 1 month ago 11
SQL Question

Check consecutive values and update field

I have a table here :

Table1

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.

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.

Table 2

Can anyone help me on how to achieve this . M using sql server .

Answer

Here it is

; WITH 
u AS (SELECT * from (values (1,'Bob'),(2,'Deepak'),(3,'Brinda'),(4,'Chriss'),(4,'Chriss')) as d (usr,name)),
N AS (SELECT * from (values (1,1,-10),(1,2,-10),(1,3,-10),(1,4,-10),(1,5,-10),(2,1,50),(2,2,40),(2,3,30),(2,4,-10),(2,5,-10),(3,1,60),(3,2,20),(3,3,40),(3,4,-10),(3,5,20),(4,1,-10),(4,2,90),(4,3,50),(4,4,30),(4,5,50)) as d (usr,wk,pt)),
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.usr = w1.usr and w.wk = w1.wk+1
    left join n w2 on w.usr = w2.usr and w.wk = w2.wk+2
    left join n w3 on w.usr = w3.usr and w.wk = w3.wk+3
),
l as (
    select *, pt+x as total
    from sc
),
s as (
select usr, sum(total) total
from l
group by usr
)
select u.*,t.*
from s t
inner join u on u.usr = t.usr

If you want to see all details change final select in

select u.*,t.*
from l t
inner join u on u.usr = t.usr

in your database should be something like:

; WITH 
u AS (SELECT * from employees),
N AS (SELECT * from week_scores),
Comments