sql-server - Table CTE And Date recursion

Question

The following query has returned Result 1 in the attached image Fichiers recus. I would also like to have Result 2 and I do not know how to go about it. Thank you for your help.

DECLARE @dateDebut DATE = 2018-07-10
DECLARE @dateFin DATE = 2018-07-14
DECLARE @cols VARCHAR(MAX)
DECLARE @cols VARCHAR(MAX)
DECLARE @query VARCHAR(MAX)
DECLARE @sum_cols VARCHAR(MAX)

WITH Dates
AS (
SELECT [Date1] = CONVERT(Date,@dateDebut)
UNION ALL
SELECT [Date1] = DATEADD(DAY,1,[Date1])
FROM Dates
WHERE DATEADD(DAY,1,[Date1]) < @dateFin
)
SELECT LEFT(CONVERT(VARCHAR,[Date1],20),10) Date
INTO #DatesSerie
FROM Dates
OPTION (MAXRECURSION 45);

SET @cols = STUFF(( SELECT distinct ',' + QUOTENAME(d.Date)
                    FROM #DatesSerie d 
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'),1,1,'')


SET @query = 'SELECT Reg, Trad, Rac, '+ @sum_cols +'
              FROM FichiersRecus
              PIVOT (
                      COUNT(FileName)
                      FOR DateFichier IN (' + @cols + ')
                    ) AS MYPIVOT
              GROUP BY Reg,Trad,Rac
              ORDER BY '+ @dateDebut +' DESC, Reg,Trad ASC ';


EXECUTE (@query)    

标签： sql-servertsql

Answer 1

Result 2 不需要动态 sql。

只需将参考表与日期交叉连接，然后将连接左连接到 FichiersRecus。

DECLARE @dateDebut DATE = '2018-07-10';
DECLARE @dateFin DATE = '2018-07-14';

WITH DATES AS (
  SELECT @dateDebut AS DateFichier
  UNION ALL
  SELECT DATEADD(DAY, 1, DateFichier)
  FROM DATES
  WHERE DateFichier < @dateFin
)
SELECT d.DateFichier, t1.Reg, t1.Trad, t2.Rac, f.FileName
FROM DATES AS d
CROSS JOIN Table1 AS t1
CROSS JOIN Table2 AS t2
LEFT JOIN FichiersRecus AS f 
  ON (f.DateFichier = d.DateFichier
  AND f.Reg = t1.Reg 
  AND f.Trad = t1.Trad 
  AND f.Rac = t2.Rac)
ORDER BY d.DateFichier, t1.Reg, t1.Trad, t2.Rac;