-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathATR_DIST_Calcul.sql
61 lines (55 loc) · 4.52 KB
/
ATR_DIST_Calcul.sql
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
SELECT
SELECT_TPA_distrib_Tarif_PROX.CodePCE,
SELECT_TPA_distrib_Tarif_PROX.[Nom du client],
SELECT_TPA_distrib_Tarif_PROX.MoisSaisie,
SELECT_TPA_distrib_Tarif_PROX.[CJA ou CJN (MWh/h)],
CDbl(IIf(IsNumeric(SELECT_TPA_distrib_Tarif_PROX.[CJA ou CJN (MWh/h)]),0,0)) AS TFT,
CDbl(CDbl(IIf([SELECT_TPA_distrib_Tarif_PROX].[BAL Zone]="TIGF",[tbl_TRANSPORT_TCR].[TIGF],[tbl_TRANSPORT_TCR].[GDF]))*CLng([SELECT_TPA_distrib_Tarif_PROX].[NTR]))+CDbl(CDbl(IIf([SELECT_TPA_distrib_Tarif_PROX].[BAL Zone]="TIGF",[tbl_DISTRIBUTION_TCL].[TIGF],[tbl_DISTRIBUTION_TCL].[GDF])))+CDbl(CDbl(IIf([SELECT_TPA_distrib_Tarif_PROX].[BAL Zone]="TIGF",[tbl_TRANSPORT_TCS].[TIGF],[tbl_TRANSPORT_TCS].[GDF]))) AS TCRT,
CDbl([TQ_PEG]) AS TQRT,
CDbl(
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T1",
tbl_COEFF_GRD_DISTRIBUTION.ABO_ANNUEL_EUR_T1,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T2",
tbl_COEFF_GRD_DISTRIBUTION.ABO_ANNUEL_EUR_T2,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T3",
tbl_COEFF_GRD_DISTRIBUTION.ABO_ANNUEL_EUR_T3,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T4",
tbl_COEFF_GRD_DISTRIBUTION.ABO_ANNUEL_EUR_T4,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="TP",
CDbl(tbl_COEFF_GRD_DISTRIBUTION.TARIFPROX_ABONNEMENT_EUR)+(CDbl(tbl_COEFF_GRD_DISTRIBUTION.TARIFPROX_TANNUEL_EURMWHPARMETRE)*CDbl(SELECT_TPA_distrib_Tarif_PROX.DISTANCE_m))*(
IIf(CDbl(SELECT_TPA_distrib_Tarif_PROX.DENSITE_POPULATION_hab_km2)>4000,3,
IIf(CDbl(SELECT_TPA_distrib_Tarif_PROX.DENSITE_POPULATION_hab_km2)<400,1,1.75))))))))) AS TFD,
CDbl(IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T4",
tbl_COEFF_GRD_DISTRIBUTION.TERME_CAPACITE_EURMWH_T4,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="TP",tbl_COEFF_GRD_DISTRIBUTION.TARIFPROX_TANNUEL_EURMWHPARJOUR,0))) AS TCD,
CDbl(IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T1",
tbl_COEFF_GRD_DISTRIBUTION.TERME_QUANTITE_EURMWH_T1,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T2",
tbl_COEFF_GRD_DISTRIBUTION.TERME_QUANTITE_EURMWH_T2,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T3",
tbl_COEFF_GRD_DISTRIBUTION.TERME_QUANTITE_EURMWH_T3,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="T4",
tbl_COEFF_GRD_DISTRIBUTION.TERME_QUANTITE_EURMWH_T4,
IIf(SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire]="TP",0,"erreur")))))) AS TQD,
(TFT+TFD)+IIf(IsNull(SELECT_TPA_distrib_Tarif_PROX.[CJA ou CJN (MWh/h)]),0,CDbl(SELECT_TPA_distrib_Tarif_PROX.[CJA ou CJN (MWh/h)]))*(TCRT+TCD)+
(CDbl(SELECT_TPA_distrib_Tarif_PROX.[CAR (kWh)])/1000)*(TQD+TQRT) AS TOTAL,
IIf(CDbl(SELECT_TPA_distrib_Tarif_PROX.[CAR (kWh)])=0,0,CDbl(TOTAL/(CDbl(SELECT_TPA_distrib_Tarif_PROX.[CAR (kWh)])/1000))) AS TOTAL_MWH,
SELECT_TPA_distrib_Tarif_PROX.[Code PITD] AS PITD,
SELECT_TPA_distrib_Tarif_PROX.[Option Tarifaire] AS TARIF,
SELECT_TPA_distrib_Tarif_PROX.Profil AS PROFIL,
SELECT_TPA_distrib_Tarif_PROX.[CAR (kWh)] AS CAR,
SELECT_TPA_distrib_Tarif_PROX.[CJA ou CJN (MWh/h)] AS CJA_FERME,
0 AS CJA_INTERRUPTIBLE,
SELECT_TPA_distrib_Tarif_PROX.[CJA ou CJN (MWh/h)] AS CJA_TOTAL,
0 AS SURCAPACITE_HORAIRE,
0 AS NOMBRE_POSTES,
SELECT_TPA_distrib_Tarif_PROX.[Date Début] AS DATE_DEBUT,
SELECT_TPA_distrib_Tarif_PROX.[Date Fin] AS DATE_FIN
FROM SELECT_TPA_distrib_Tarif_PROX, tbl_TRANSPORT_TCR, tbl_DISTRIBUTION_TCL, tbl_TRANSPORT_TCS, tbl_ECHANGE_PEG, tbl_COEFF_GRD_DISTRIBUTION, [PARAMETERS]
WHERE (((SELECT_TPA_distrib_Tarif_PROX.MoisSaisie)=[PARAMETERS].[DATE_MoisSaisie]) AND ((SELECT_TPA_distrib_Tarif_PROX.[Date Début])<=[PARAMETERS].[DATE_ANALYSE_FIN])
AND ((SELECT_TPA_distrib_Tarif_PROX.[Date Fin])>=[PARAMETERS].[DATE_ANALYSE]) AND ((tbl_COEFF_GRD_DISTRIBUTION.CLE_RESEAU)=Left([SELECT_TPA_distrib_Tarif_PROX].[Code PITD],2))
AND ((tbl_COEFF_GRD_DISTRIBUTION.DATE_DEBUT)<=[PARAMETERS].[DATE_ANALYSE_FIN]) AND ((tbl_COEFF_GRD_DISTRIBUTION.DATE_FIN)>=[PARAMETERS].[DATE_ANALYSE])
AND ((tbl_TRANSPORT_TCR.DATE_DEBUT)<=[PARAMETERS].[DATE_ANALYSE_FIN]) AND ((tbl_TRANSPORT_TCR.DATE_FIN)>=[PARAMETERS].[DATE_ANALYSE])
AND ((tbl_DISTRIBUTION_TCL.DATE_DEBUT)<=[PARAMETERS].[DATE_ANALYSE_FIN]) AND ((tbl_DISTRIBUTION_TCL.DATE_FIN)>=[PARAMETERS].[DATE_ANALYSE])
AND ((tbl_TRANSPORT_TCS.DATE_DEBUT)<=[PARAMETERS].[DATE_ANALYSE_FIN]) AND ((tbl_TRANSPORT_TCS.DATE_FIN)>=[PARAMETERS].[DATE_ANALYSE])
AND ((tbl_ECHANGE_PEG.DATE_DEBUT)<=[PARAMETERS].[DATE_ANALYSE_FIN]) AND ((tbl_ECHANGE_PEG.DATE_FIN)>=[PARAMETERS].[DATE_ANALYSE]));