[MSSQL] MSSQL 일별, 주별, 월별 구하기
안녕하세요.
오늘은 지난번에 MSSQL에서 각각 일별, 주별, 월별 구하는 방법에 대해서 각각 포스팅을 해서 알려 드렸었는데요.
오늘은 각각 작성했던 일별, 주별, 월별 SQL문을 하나로 통합해서 다시 SQL문을 작성해 보았습니다.
타겟 날짜를 기준으로 일별, 주별, 월별을 구해 보도록 하겠습니다.
[SQL 문]
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 |
WITH DAY_TBL AS ( SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,0,'20200630'),23)AS 'DAY', 'DD' as DayType UNION SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,1,'20200630'),23)AS 'DAY', 'DD' as DayType UNION SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,2,'20200630'),23)AS 'DAY', 'DD' as DayType UNION SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,3,'20200630'),23)AS 'DAY', 'DD' as DayType UNION SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,4,'20200630'),23)AS 'DAY', 'DD' as DayType UNION SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,5,'20200630'),23)AS 'DAY', 'DD' as DayType UNION SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,6,'20200630'),23)AS 'DAY', 'DD' as DayType
UNION SELECT SUBSTRING('20200630', 1, 4) + '-' + CONVERT(NVARCHAR(10), DATEPART(WEEK,'20200630')) as 'DAY', 'CW' as DayType UNION SELECT SUBSTRING('20200630', 1, 4) + '-' + CONVERT(NVARCHAR(10), DATEPART(WEEK,'20200630') - 1) as 'DAY', 'CW' as DayType UNION SELECT SUBSTRING('20200630', 1, 4) + '-' + CONVERT(NVARCHAR(10), DATEPART(WEEK,'20200630') - 2) as 'DAY', 'CW' as DayType UNION SELECT SUBSTRING('20200630', 1, 4) + '-' + CONVERT(NVARCHAR(10), DATEPART(WEEK,'20200630') - 3) as 'DAY', 'CW' as DayType
UNION SELECT SUBSTRING('20200630', 1, 4) + '-' + CONVERT(NVARCHAR(10), DATEPART(MM,'20200630')) as 'DAY', 'MM' as DayType UNION SELECT SUBSTRING('20200630', 1, 4) + '-' + CONVERT(NVARCHAR(10), DATEPART(MM,'20200630') -1 ) as 'DAY', 'MM' as DayType UNION
|
