[MSSQL] 두 날짜사이에 날짜별 통계 쿼리
[MSSQL] 두 날짜사이에 날짜별 통계 쿼리
GROUP BY를 이용하여 날짜별로 합(SUM)한 결과 출력
WITH A AS (
SELECT '20201125' AS STT_DT
, '20201225' AS END_DT
), B AS (
SELECT CONVERT(NVARCHAR(8), CONVERT(datetime, (SELECT STT_DT FROM A)), 112) AS [DT]
UNION ALL
SELECT CONVERT(NVARCHAR(8),DATEADD(D,1,DT),112) AS [DT]
FROM B
WHERE 1=1
AND dt >= (SELECT STT_DT FROM A)
AND dt <= (SELECT END_DT FROM A)
), C AS (
SELECT '20201125' AS [DT], 150 AS [SAL]
UNION ALL SELECT '20201125' AS [DT], 150 AS [SAL]
UNION ALL SELECT '20201125' AS [DT], 100 AS [SAL]
UNION ALL SELECT '20201126' AS [DT], 400 AS [SAL]
UNION ALL SELECT '20201126' AS [DT], 100 AS [SAL]
UNION ALL SELECT '20201128' AS [DT], 100 AS [SAL]
UNION ALL SELECT '20201128' AS [DT], 350 AS [SAL]
)
SELECT TA.DT AS DT
, ISNULL(SUM(TB.SAL), 0) AS SAL
FROM B TA
LEFT OUTER
JOIN C TB
ON TA.DT = TB.DT
GROUP
BY TA.DT
