drop PROCEDURE EtatCA
GO
CREATE PROCEDURE EtatCA @DateDebut AS char(20), @DateFin AS char(20) AS BEGIN DELETE  FROM CA END 
BEGIN INSERT INTO CA SELECT DATENAME([month], dbo.Ventes.LaDate) AS NomMois, dbo.Ventes.TypeMouvement, dbo.Ventes.LaDate, dbo.Ventes.Utilisateur, dbo.Ventes.Nom, 
               dbo.Ventes.Prenom, dbo.DetailVente.Reference, dbo.DetailVente.Designation, dbo.DetailVente.Quantite, dbo.DetailVente.NomFamille, 
               dbo.DetailVente.NomSite, dbo.Services.NomService, dbo.ProfileQuota.Profile, dbo.DetailVente.CodeFournisseur, dbo.DetailVente.Societe, 
               dbo.DetailVente.PrixAchatUnit, dbo.DetailVente.Taille, dbo.DetailVente.RefFournisseur
FROM  dbo.Services INNER JOIN
               dbo.Utilisateurs ON dbo.Services.NoService = dbo.Utilisateurs.Service RIGHT OUTER JOIN
               dbo.Ventes INNER JOIN
               dbo.DetailVente ON dbo.Ventes.NumVente = dbo.DetailVente.NumVente ON dbo.Utilisateurs.NoBadge = dbo.Ventes.Utilisateur LEFT OUTER JOIN
               dbo.ProfileQuota ON dbo.DetailVente.Profile = dbo.ProfileQuota.NoQuota
GROUP BY dbo.Ventes.TypeMouvement, dbo.Ventes.LaDate, dbo.Ventes.Utilisateur, dbo.Ventes.Nom, dbo.Ventes.Prenom, dbo.DetailVente.Reference, 
               dbo.DetailVente.Designation, dbo.DetailVente.Quantite, dbo.DetailVente.NomFamille, dbo.DetailVente.NomSite, dbo.ProfileQuota.Profile, 
               dbo.DetailVente.CodeFournisseur, dbo.DetailVente.Societe, dbo.DetailVente.PrixAchatUnit, dbo.DetailVente.Taille, dbo.DetailVente.RefFournisseur, 
               dbo.Services.NomService
HAVING (dbo.Ventes.LaDate > CONVERT(DATETIME, @DateDebut, 103)) AND (dbo.Ventes.LaDate < CONVERT(DATETIME, @DateFin, 103))
END
GO