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
|
CREATE proc MajAllClient
AS
DECLARE UrgenceCur CURSOR FOR
SELECT C.Bloque , DATEDIFF(day, DATEADD(y,1,C1.DateSignature), GETDATE()), C.NoCli, C1.HCourantN1, C1.HCourantN2, C1.HDebutN1, C1.HDebutN2
FROM Client C,NewContrat C1
WHERE C1.NoCli = C.NoCli
AND C1.Etat=1
UPDATE Client SET Urgence=0;
DECLARE @Bloque AS BIT,@Intervalle AS INT, @NoCli AS INT, @HCourantN1 AS INT,
@HCourantN2 AS INT, @HDebutN1 AS INT, @HDebutN2 AS INT;
OPEN UrgenceCur
FETCH NEXT FROM UrgenceCur INTO @Bloque, @Intervalle, @NoCli, @HCourantN1, @HCourantN2, @HDebutN1, @HDebutN2;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@Bloque=1) OR (@Intervalle <= 10) OR (@HCourantN1<=0 AND @HDebutN1>0 ) OR (@HCourantN2<=0 AND @HDebutN2>0 )
UPDATE Client SET Urgence = 1 WHERE NoCli=@NoCli;
ELSE IF (@Bloque=0) AND ((@Intervalle BETWEEN 10 and 30) OR (@HCourantN1<0.2*@HDebutN1 AND NOT(@HDebutN1=0)) OR (@HCourantN1<0.2*@HDebutN1 AND NOT(@HDebutN1=0)))
UPDATE Client SET Urgence = 2 WHERE NoCli=@NoCli;
ELSE IF (@Bloque=0) AND (@Intervalle >= 30)
UPDATE Client SET Urgence = 3 WHERE NoCli=@NoCli;
FETCH NEXT FROM UrgenceCur INTO @Bloque, @Intervalle, @NoCli, @HCourantN1, @HCourantN2, @HDebutN1, @HDebutN2;
END
CLOSE UrgenceCur
DEALLOCATE UrgenceCur;
GO |
Partager