SQL Server - 달력 만들기

 

 

202001 이렇게 6글자의 조건을 입력 받으면 그 달이 표시되는 쿼리문.

 

CTE(Common Table Expression)로 우선 

이런 결과가 나오게 한다음 CTE를 재가공하여 

 

이러한 달력이 나오게 한다.

 

DECLARE @INPUT_DATE VARCHAR(6) , @WK VARCHAR(2)
SET @INPUT_DATE = '202101'
DECLARE @FORMATED_INPUT_DATE VARCHAR(8) = @INPUT_DATE + '01';

WITH CTE_DATE AS
(
SELECT
	DATEDIFF(WK, @FORMATED_INPUT_DATE, DATEADD(D, CP.NUM-1, @FORMATED_INPUT_DATE)) [WEEK],
	DATEPART(MM, @FORMATED_INPUT_DATE) [MONTH],
	CP.NUM DAY_NUM,
	DATEPART(DW, CONVERT(DATE, DATEADD(D, CP.NUM-1,@FORMATED_INPUT_DATE))) DW_NUM,
	DATENAME(DW, CONVERT(DATE,  DATEADD(D, CP.NUM-1,@FORMATED_INPUT_DATE))) DW_NAME
FROM COPY_T CP
WHERE CP.NUM BETWEEN 1 AND DATEDIFF(D,@FORMATED_INPUT_DATE, DATEADD(M,1,@FORMATED_INPUT_DATE))
)
SELECT 
	'Su' = MAX(CASE DW_NUM WHEN '1' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END),
	'Mo' = MAX(CASE DW_NUM WHEN '2' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END),
	'Tu' = MAX(CASE DW_NUM WHEN '3' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END),
	'We' = MAX(CASE DW_NUM WHEN '4' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END),
	'Th' = MAX(CASE DW_NUM WHEN '5' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END),
	'Fr' = MAX(CASE DW_NUM WHEN '6' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END),
	'Sa' = MAX(CASE DW_NUM WHEN '7' THEN CONVERT(VARCHAR, DAY_NUM) ELSE '' END)
  FROM CTE_DATE A
  JOIN COPY_T B
	ON A.WEEK = B.NUM-1
GROUP BY A.WEEK

 

정답지를 보면서 해도 어렵다.

정답지에는 ms sql내장에 있는 master ..sql value 테이블을 활용하는데 나는 1부터 1000까지 입력되어 있는 copy_t라는 테이블을 활용하여 처리하였다.

 

 

참고 : blog.naver.com/PostView.nhn?blogId=javaking75&logNo=220168845898&categoryNo=85&parentCategoryNo=0&viewDate=&currentPage=1&postListTopCurrentPage=1&from=postView

 

 

 

CTE 안쓰고 sub query 로 한 것. (들여쓰기가 개판.)

DECLARE @INPUT_DATE VARCHAR(6) = '202112'
DECLARE @CALENDER VARCHAR(8) = @INPUT_DATE + '01'

SELECT 
	WK,
	max(CASE DW_NUM WHEN '1' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS Su,
	max(CASE DW_NUM WHEN '2' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS Mo,
	max(CASE DW_NUM WHEN '3' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS Tu,
	max(CASE DW_NUM WHEN '4' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS We,
	max(CASE DW_NUM WHEN '5' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS Tr,
	max(CASE DW_NUM WHEN '6' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS Fr,
	max(CASE DW_NUM WHEN '7' THEN CONVERT(VARCHAR, D_DAY) ELSE '' END) AS Sa
FROM
(
SELECT 
	CP.NUM D_DAY,
	DATEPART(
    	WK, 
		CONVERT(DATETIME, 
        	CASE LEN(CP.NUM) 
            WHEN '1' THEN @INPUT_DATE+'0'+CONVERT(VARCHAR(2), CP.NUM) 
			ELSE @INPUT_DATE+CONVERT(VARCHAR(2), CP.NUM)END
            ))-1 AS WK,
	DATEPART(
    	DW, 
		CONVERT(DATETIME, 
        	CASE LEN(CP.NUM) 
            WHEN '1' THEN @INPUT_DATE+'0'+CONVERT(VARCHAR(2), CP.NUM) 
			ELSE @INPUT_DATE+CONVERT(VARCHAR(2), CP.NUM)END
            )) AS [DW_NUM],
	LEFT(
    	DATENAME(
        	DW, 
			CONVERT(DATETIME, 
            	CASE LEN(CP.NUM) WHEN '1' THEN @INPUT_DATE+'0'+CONVERT(VARCHAR(2), CP.NUM) 
				ELSE @INPUT_DATE+CONVERT(VARCHAR(2), CP.NUM)END)),2
         ) AS [DW_NAME]
  FROM COPY_T CP
 WHERE NUM BETWEEN 1 AND DATEDIFF(D,CONVERT(DATETIME, @CALENDER),DATEADD(M,1,CONVERT(DATETIME, @CALENDER)))
 ) X
 group by x.wk