I have a table here :
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)
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.
Can anyone help me on how to achieve this . M using sql server .
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),