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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| @DelailVal int
AS
BEGIN
declare @SoldeUpd float,@DtMaj datetime
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WITH
CTE_GAINS_SEPTEMBRE AS
(
SELECT Gain.RefGain,Gain.Gain,idann
FROM Gain LEFT OUTER JOIN CampagneRemunerationParam ON Gain.RefremParam = CampagneRemunerationParam.RefRemParam
WHERE (Gain.DtDate = CONVERT(CHAR(10), GETDATE() - @DelailVal, 103)) and (delaival=7) and (F = 1 or v=1))
UPDATE dbo.Gain
SET gain = 0,
gainvalid= C.gain,
gainadigaming= ((C.gain * 30 )/100),
--F = 0,
vvalid = CASE
WHEN v=1 THEN 1
else 0
END,
Fvalid= CASE
WHEN F=1 THEN 1
else 0
END,
v = CASE
WHEN v=1 THEN 0
else 0
END,
F= CASE
WHEN F=1 THEN 0
else 0
END,
--FValid = 1,
AnnVali = 1,
AnnValiDt = GETDATE(),
anncomment='Validation auto.'
FROM dbo.Gain AS G
JOIN CTE_GAINS_SEPTEMBRE AS C ON G.refgain = C.refgain
SELECT @SoldeUpd= Solde.Solde - C.gain + ((C.gain * 30 )/100) FROM Solde JOIN CTE_GAINS_SEPTEMBRE AS C ON Solde.Refuser = C.idann where Solde.Refuser = C.idann
UPDATE Solde SET Solde = @SoldeUpd, DtUpd = CONVERT(CHAR(10), GETDATE(), 103) FROM Solde JOIN CTE_GAINS_SEPTEMBRE AS C ON Solde.Refuser = C.idann where Solde.Refuser = C.idann
END |
Partager