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
| set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AffStatJour]
@dateDeb datetime,
@dateFin datetime,
@Idaff int,
@Refsite int
AS
BEGIN
WITH CTE_DATES_PERIODE AS
(
SELECT @dateDeb AS dates
UNION ALL
SELECT DATEADD(day, 1, dates)
FROM CTE_DATES_PERIODE
WHERE dates <= @dateFin
)
SELECT
@Refsite as 'Refsite' ,
CONVERT(CHAR(10), dates, 103) AS dates,
isnull(SUM(A),0) AS Affichage,
isnull(SUM(AValid),0) AS AValide,
isnull(SUM(C),0) AS Clic,
isnull(SUM(CValid),0) AS CValide,
isnull(SUM(ARefuse),0) AS CRefuse,
isnull(SUM(DC),0) AS [Double-clic],
isnull(SUM(DCValid),0) AS DCValide,
isnull(SUM(DCRefuse),0) AS DCRefuse,
isnull(SUM(F),0) AS Formulaire,
isnull(SUM(FValid),0) AS FValide,
isnull(SUM(FRefuse),0) AS FRefuse,
isnull(SUM(V),0) AS Vente,
isnull(SUM(VValid),0) AS VValide,
isnull(SUM(VRefuse),0) AS VRefuse,
isnull(SUM(Gain),0) AS [Total en attente],
isnull(SUM(GainValid),0) AS [Total valide]
FROM CTE_DATES_PERIODE
LEFT OUTER JOIN Gain ON dates = Gain.DtDate and Gain.idaff=@idaff and Gain.refsite=@refSite
WHERE (dates <= @dateFin)
GROUP BY Gain.IdAff,dates,Gain.Refsite
ORDER BY dates
OPTION (MAXRECURSION 366)
END |
Partager