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 54 55 56 57 58 59 60 61 62 63 64 65
|
CREATE FUNCTION calcule_qte (@entry_id int,@row_id int,@today_ctime datetime)
RETURNS float
AS
BEGIN
RETURNS float
AS
BEGIN
DECLARE
@trimestreencours int,
@nature_id int,
@colonne_id varchar(150),
@Annee_tmp int,
@Anneeencours int,
@Annee_precedente int,
@qte float,
@qte_temp float,
@qte_Annee float,
SET @qte = 0
SET @Anneeencours = ANNEE (@today_ctime)
SET @trimestreencours = DATEPART(trimestre, @today_ctime)
SELECT @nature_id=nature_id
FROM COLONNE
WHERE Annee=@Anneeencours AND trimestre=@trimestreencours
DECLARE colonneumn_cursor CURSOR READ_ONLY
FOR
-- ***********declaration du curseur **********
SELECT colonne_id,Annee FROM COLONNE WHERE nature_id<=@nature_id AND nature_id%5<>0 ORDER BY nature_id
-- *********************
OPEN colonne_cursor
FETCH NEXT FROM colonne_cursor INTO @colonne_id,@Annee_tmp
SET @Annee_precedente= @Annee_tmp
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@qte_tmp=
CASE @colonne_id
WHEN 'COL_0006' THEN COL_0006
WHEN 'COL_0016' THEN COL_0016
WHEN 'COL_0026' THEN COL_0026
WHEN 'COL_0036' THEN COL_0036
.
.
WHEN 'COL_0290' THEN COL_0290
ELSE 0
END
FROM Tableau_de_bord
WHERE row_id = @row_id AND entry_id3 = @entry_id
IF @Annee_precedente<> @Annee_tmp
BEGIN
SET @qte = @qte + ISNULL(@qte_Annee,0)
SET @qte_Annee = @qte_tmp
SET @Annee_precedente= @Annee_tmp
END
ELSE
SET @qte_Annee = @qte_tmp
FETCH NEXT FROM colonne_cursor INTO @colonne_id,@Annee_tmp
END
CLOSE colonne_cursor
DEALLOCATE colonne_cursor
SET @qte = @qte + ISNULL(@qte_Annee,0)
RETURN @qte |
Partager