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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
| alter PROCEDURE [dbo].[Alim_Data_Ventes_hebdo_delta_rattrapage_new]
AS
BEGIN
DECLARE @fichiersource NVARCHAR(50)
DECLARE @count_vente_work VARCHAR(10)
DECLARE @tempst DATETIME
DECLARE @dateDeb DATETIME
DECLARE @dateFin DATETIME
SELECT @tempst = GETDATE();
set @dateDeb = GETDATE();
PRINT 'Debut Procédure Alim_Data_Ventes_hebdo_delta_rattrapage à ' + CONVERT (NVARCHAR, @tempst, 126) ;
--BEGIN TRANSACTION VENTE
WITH CTE_FICHIERS ( FichierSource )
AS (
SELECT DISTINCT FichierSource
FROM [DWH_VEGAS].[dbo].ventes_hebdo
)
INSERT INTO [ODS_VEGAS].[dbo].[Ventes_hebdo_Work_1]
(
codemagasin,date,[vente(1)_retour(2)],coderemise,
codetva,ean,codevendeur,Code_VendeurMagasin,
ANNULE, NumTicket,
Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI,
codeagence, codepdl,sousfamille,IMEI,
NomClient,PrenomClient, TelephoneClient,CodePostalClient,
VilleClient,AdresseClient,ComplementAdresse,FichierSource,
qte_dev,qte,DeltaQte,ca_dev,ca, DeltaCa,
mtremise_dev,mtremise,DeltaMtremise,
mttva_dev,mttva,DeltaMttva,
DateFichier,Id_date_Ean_VendeurMagasin_Numticket_remise_IMEI
)
(SELECT tmp.codemagasin, tmp.date,tmp.[vente(1)_retour(2)],
tmp.coderemise, tmp.codetva,tmp.ean, tmp.codevendeur,
tmp.Code_VendeurMagasin,tmp.ANNULE, tmp.NumTicket,
tmp.Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI,
tmp.codeagence, tmp.codepdl,tmp.sousfamille,tmp.IMEI,
tmp.NomClient,tmp.PrenomClient, tmp.TelephoneClient,
tmp.CodePostalClient, tmp.VilleClient,
tmp.AdresseClient,tmp.ComplementAdresse,
tmp.FichierSource,ISNULL(SUM(tmp.qte),0) AS qte_dev,
ISNULL(SUM(prod.qte), 0) AS qte,
ISNULL(SUM(tmp.qte),0) - ISNULL(SUM(prod.qte), 0) AS DeltaQte,
ISNULL(SUM(tmp.ca),0) AS ca_dev,ISNULL(SUM(prod.ca), 0) AS ca,
ISNULL(SUM(tmp.ca),0) - ISNULL(SUM(prod.ca), 0) AS DeltaCa,
ISNULL(SUM(tmp.mtremise),0) AS mtremise_dev,ISNULL(SUM(prod.mtremise), 0) AS mtremise,
ISNULL(SUM(tmp.mtremise),0) - ISNULL(SUM(prod.mtremise), 0) AS DeltaMtremise,
ISNULL(SUM(tmp.mttva),0) AS mttva_dev,ISNULL(SUM(prod.mttva), 0) AS mttva,
ISNULL(SUM(tmp.mttva),0) - ISNULL(SUM(prod.mttva), 0) AS DeltaMttva,
tmp.DateFichier,
(CONVERT (NVARCHAR , tmp.date ,112) + '_' +SUBSTRING
(tmp.Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI,15,
LEN(tmp.Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI))
) AS Id_date_Ean_VendeurMagasin_Numticket_remise_IMEI
FROM DWH_VEGAS.dbo.Ventes_hebdo_BIS AS prod
RIGHT OUTER JOIN DWH_VEGAS.dbo.Ventes_hebdo AS tmp
ON prod.Id_date_Ean_VendeurMagasin_Numticket_remise_IMEI = tmp.Id_date_Ean_VendeurMagasin_Numticket_remise_IMEI
JOIN CTE_FICHIERS AS FIC
ON tmp.FichierSource = FIC.FichierSource
GROUP BY tmp.codemagasin,tmp.date,tmp.[vente(1)_retour(2)],
tmp.coderemise,tmp.codetva, tmp.ean, tmp.codevendeur,
tmp.Code_VendeurMagasin,tmp.ANNULE, tmp.NumTicket,
tmp.Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI,
tmp.codeagence, tmp.codepdl,tmp.sousfamille,
tmp.IMEI,tmp.NomClient, tmp.PrenomClient,
tmp.TelephoneClient,tmp.CodePostalClient,tmp.VilleClient,
tmp.AdresseClient,tmp.ComplementAdresse,tmp.FichierSource,tmp.DateFichier);
--insertion des données
INSERT INTO DWH_VEGAS.dbo.Ventes_hebdo_BIS
([Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI]
,[Id_date_Ean_VendeurMagasin_Numticket_remise_IMEI]
,[date],[ean],[Code_VendeurMagasin],[codeagence],[codemagasin]
,[codevendeur],[codepdl] ,[vente(1)_retour(2)],[qte]
,[ca] ,[coderemise] ,[mtremise] ,[mttva] ,[codetva] ,[sousfamille]
,[IMEI] ,[ANNULE] ,[NomClient] ,[PrenomClient]
,[TelephoneClient] ,[CodePostalClient] ,[VilleClient] ,[AdresseClient]
,[ComplementAdresse] ,[NumTicket] ,[DateFichier] ,[FichierSource])
(SELECT Id_Fichier_Ean_VendeurMagasin_Numticket_remise_IMEI,
Id_date_Ean_VendeurMagasin_Numticket_remise_IMEI,
date,ean,Code_VendeurMagasin,
codeagence, codemagasin,codevendeur,codepdl,
[vente(1)_retour(2)],DeltaQte as Qte,
DeltaCa as Ca ,coderemise,DeltaMtremise as Mtremise,
DeltaMttva as Mttva,codetva,sousfamille,
IMEI,ANNULE,NomClient,PrenomClient,
TelephoneClient,CodePostalClient,VilleClient,
AdresseClient,ComplementAdresse,NumTicket,
DateFichier,FichierSource
FROM ODS_VEGAS.dbo.Ventes_hebdo_Work_1
WHERE DeltaQte + DeltaCa + DeltaMtremise+DeltaMttva <> 0);
--suppresion de la table temporaire ==> Vous ne supprimez pas la table, vous la videz
--DELETE FROM ODS_VEGAS.dbo.Ventes_hebdo_Work_1;
set @dateFin = GETDATE();
print 'durée : ' + CONVERT (NVARCHAR, DATEDIFF(millisecond, @dateDeb, @dateFin));
--COMMIT TRANSACTION VENTE
END |
[SQL SERVER 2K5] Chaine tronquée
Répondre avec citation
Partager