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 51 52 53
| DECLARE faccur CURSOR for
SELECT
favor,
place_id,
service_id,
security_id,
date,
legal_end_session
FROM temp_works..comm_data
WHERE facilitation = 1
GO
/* Pour chaque deal de facilitation, calcul des commissions (indirectes) entre date du deal + epsilon -> clôture */
DECLARE @third_id int,@place_id int, @service_id int, @security_id int, @date_deal datetime, @comm_indirect float, @legal_end_session datetime
OPEN faccur
FETCH faccur INTO @third_id,@place_id,@service_id,@security_id,@date_deal,@legal_end_session
WHILE @@sqlstatus = 0
BEGIN
-- calcul des commissions sur le même titre, après @date_deal(de facilitation) jusqu'à la clôture
SELECT
@comm_indirect = [...]
FROM
[...]
WHERE [...]
AND b.favor = @third_id
AND s.place_id = @place_id
AND b.service_id = @service_id
AND b.security_id = @security_id
-- on sauvegarde la comm indirecte calculée
UPDATE temp_works..comm_data
SET
indirect_comm = ISNULL(@comm_indirect,0)
WHERE
favor = @third_id
AND place_id = @place_id
AND date = @date_deal
AND service_id = @service_id
AND security_id = @security_id
AND legal_end_session = @legal_end_session
AND indirect_comm = -1 /* -1 : pas encore traité, NULL pour vide / erreur */
AND facilitation = 1
FETCH faccur INTO @third_id,@place_id,@service_id,@security_id,@date_deal,@legal_end_session
END
CLOSE faccur
DEALLOCATE CURSOR faccur |
Partager