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
|
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
DROP FUNCTION IF EXISTS [codap_sp].[fn_get_bgc_sum]
GO
CREATE FUNCTION [xxxx].[fn_get_bgc_sum] (@iMonth INT, @iYear INT)
RETURNS @val TABLE (bgcTot FLOAT, echeance VARCHAR (4))
AS
BEGIN
DECLARE @dtStart30mn AS DATETIME
DECLARE @dtEnd30mn AS DATETIME
SELECT @dtStart30mn =
DATETIMEFROMPARTS (@iYear,
@iMonth,
1,
0,
30,
0,
0)
SELECT @dtEnd30mn =
DATETIMEFROMPARTS (@iYear,
(@iMonth + 1),
1,
0,
0,
0,
0)
INSERT INTO @val (bgcTot, echeance)
SELECT SUM (T.bgcConsoTot) AS bgcConsoTot, T.echeance
FROM (SELECT sum (crb.puissance_30mn) AS bgcConsoTot, crb.echeance
FROM [xxxxx] crb
WHERE crb.datepoint30 BETWEEN @dtStart30mn
AND @dtEnd30mn
AND crb.type_pt_id = 17
GROUP BY crb.echeance
UNION
SELECT sum (vcrb.puissance) AS bgcConsoTot, vcrb.echeance
FROM [xxxxx] vcrb
WHERE vcrb.datepoint BETWEEN @dtStart30mn
AND @dtEnd30mn
AND vcrb.type_pt_id = 17
GROUP BY vcrb.echeance) AS T
GROUP BY T.echeance
RETURN
END |
Partager