1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
|
drop table t
create table t ( ord int primary key, Machine nvarchar(5), secondes int, Ddate datetime, Duree_Total int,Point int)
insert t(ord,Machine,secondes,Ddate) values (1,'A001',150,'10/08/2020')
insert t(ord,Machine,secondes,Ddate) values (2,'A001',140,'09/08/2020')
insert t(ord,Machine,secondes,Ddate) values (3,'A001',130,'08/08/2020')
insert t(ord,Machine,secondes,Ddate) values (4,'A001',110,'07/08/2020')
insert t(ord,Machine,secondes,Ddate) values (5,'A001',110,'06/08/2020')
insert t(ord,Machine,secondes,Ddate) values (6,'A002',110,'10/08/2020')
insert t(ord,Machine,secondes,Ddate) values (7,'A002',130,'09/08/2020')
declare @total int,@Mac nvarchar(5), @point int, @Trouve int
select @total = 0, @Mac='',@point=0 , @Trouve=0
update t set
-- A chaque changement de machine on remet le compteur à 0
@total=case when @mac <> Machine then ( secondes) else @total + secondes end,
@Mac=Machine,
-- ici on recherche le >500
@point = case when (@total>=500 and @Trouve=0) then 1 else 0 end,
@Trouve = case when @point=1 then 1 else 0 end,
-- je voudrais avoir la valeur maxi de la machine si Duree_Total < 500
-- dans ce cas ord:7 Machine:A002 Ddate 09/08/2020
------------------------
Duree_Total = @total,
Point = @Trouve
SELECT ord, Machine, Ddate, Duree_Total
FROM t
WHERE (Point = 1) |
Partager