[MSSQL] 달력

MSSQL 달력 SQL!

WITH TMP AS(
    SELECT CONVERT(VARCHAR(8), CONVERT(DATETIME, '201109' + dbo.Fn_LPAD(CAST(A.NUMBER + 1 AS VARCHAR ), 2, 0)),112) AS YMD
         , DATEPART(WK , CONVERT(DATETIME, '201109' + dbo.Fn_LPAD(CAST(A.NUMBER + 1 AS VARCHAR ), 2, 0))) AS WY    --WeekOfYear
         , DATEPART(DW , CONVERT(DATETIME, '201109' + dbo.Fn_LPAD(CAST(A.NUMBER + 1 AS VARCHAR ), 2, 0))) AS DW    --DayOfWeek
         , A.number AS NUM
         , '201109' AS CUR_YM
         , '2011' AS CUR_YYYY
         , '09' AS CUR_MM
      FROM master.dbo.spt_values A
     WHERE A.TYPE = 'P'
       AND A.NUMBER < dbo.Fn_GetLastDay('2011', '09')
)
SELECT X.RNUM
     , MIN(CASE X.DW WHEN 1 THEN X.YMD ELSE NULL END) AS COL_SUN
     , MIN(CASE X.DW WHEN 2 THEN X.YMD ELSE NULL END) AS COL_MON
     , MIN(CASE X.DW WHEN 3 THEN X.YMD ELSE NULL END) AS COL_THU
     , MIN(CASE X.DW WHEN 4 THEN X.YMD ELSE NULL END) AS COL_WED
     , MIN(CASE X.DW WHEN 5 THEN X.YMD ELSE NULL END) AS COL_THU
     , MIN(CASE X.DW WHEN 6 THEN X.YMD ELSE NULL END) AS COL_FRI
     , MIN(CASE X.DW WHEN 7 THEN X.YMD ELSE NULL END) AS COL_SAT
  FROM (
        SELECT CUR_YM, YMD, WY, DW
             , DENSE_RANK() OVER(ORDER BY WY) AS RNUM
          FROM TMP
       ) X
GROUP BY X.RNUM

※ 아래 사용자정의 함수는 같이 프로젝트를 수행한 동료가 작성한 것임을 밝힌다.

/*****###### USER DEFINE FUNCTION : Fn_LPAD ######*****/
USE [user id]
GO
/****** Object:  UserDefinedFunction [dbo].[Fn_LPAD]    Script Date: 09/16/2011 12:53:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Fn_LPAD]
(@I_STR varchar(200), @I_STR_LEN int, @I_FILL_STR varchar(1))
RETURNS varchar(200)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @R_STR VARCHAR(200),
    @T_DIFF_LEN INT,
    @T_STR_LEN INT,
    @O_STR_LEN INT,
    @POS INT
    SET @O_STR_LEN = LEN(@I_STR)
    SET @T_STR_LEN = 0
    SET @POS = 1
    WHILE @O_STR_LEN >= @POS
    BEGIN
        IF LEN(UNICODE(SUBSTRING(@I_STR,@POS,@POS+1))) > 3
        BEGIN
            SET @T_STR_LEN = @T_STR_LEN + 2
        END
        ELSE
        BEGIN
            SET @T_STR_LEN = @T_STR_LEN + 1       
        END
        SET @POS = @POS+1   
    END
 SET @T_DIFF_LEN = @I_STR_LEN -  @T_STR_LEN
 IF @T_DIFF_LEN >= 0 
  SET @R_STR = REPLICATE(@I_FILL_STR, @T_DIFF_LEN) + @I_STR
 ELSE
  SET @R_STR = @I_STR
     RETURN substring(@R_STR,1,@I_STR_LEN )
END
  
 
/*****###### USER DEFINE FUNCTION : Fn_GetLastDay ######*****/
USE [user id]
GO
/****** Object:  UserDefinedFunction [dbo].[Fn_GetLastDay]    Script Date: 09/16/2011 13:59:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Fn_GetLastDay]
(@YYYY varchar(4), @MM varchar(2))
RETURNS varchar(2)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @LASTDAY VARCHAR(2)
SELECT 
 @LASTDAY = DATEPART(DAY, DATEADD(MONTH, 1, @YYYY + '-' + @MM + '-01')-(DAY(@YYYY + '-' + @MM + '-01')))
RETURN @LASTDAY
END