Function to return a range of dats - 날짜 범위 펑션

http://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/

By: Albert Hetzel   정리

 

SQL SERVER 2005 이상

 

일자별  sum, max 등의 집계 쿼리를 할 때 일자의 범위가 모두 포함되기어 표현되어지기를 원할 때가 있다.

그러나 실제 데이터는 일자 범위에 데이터가 없을 수도 있고 있을 수도 있다. 이럴때 어떻게 해야 할까??

 

물론, row_number 함수를 사용하여 corss join을 사용하면 가능하다. 그러나 이런 쿼리는 복잡하고 원하는 결과를 나타내지 않을 수도 있다.

 

여기서는 테이블 함수를 사용하는 방법으로 해결 합니다.

이 함수는 CTE를 사용하여 반복적으로 날짜를 반환 해 준다. (@Increment 옵션에 따라서)

 

 

DROP FUNCTION [DateRange]
GO
CREATE FUNCTION [dbo].[DateRange]
(     
      @Increment              CHAR(1),   --  일/주/달 여부
      @StartDate              DATETIME,  -- 시작 날짜
      @EndDate                DATETIME   -- 종료 날짜
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)
AS
BEGIN
	 ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)
          
      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END
GO

실행을 해 보면 아래와 같다.

SELECT IndividualDate FROM DateRange('d', '2012-11-01', '2012-11-10')
SELECT IndividualDate FROM DateRange('w', '2012-11-01', '2012-11-10')
SELECT IndividualDate FROM DateRange('m', '2012-11-01', '2013-01-10')

 

 

 

 

자 그럼 이제 집계하고자 하는 데이터와 조인해서 사용해 보자

임시 테이블을 사용해서 해당 날짜의 제품이 판매된 정보를 입력 하였다. 아래 데이터에서 봤을 경우 11/1 ~ 11/10 사이에

매출은 이틀만 발생 하였다.

 

CREATE TABLE #temp (orderDate DATETIME, orderInfo VARCHAR(50))
INSERT INTO #temp VALUES ('2012-11-01','2 copies of SQL Server 2008')
INSERT INTO #temp VALUES ('2012-11-05','6 copies of SQL Server 2008 R2')
INSERT INTO #temp VALUES ('2012-11-05','10 copies of SQL Server 2012')

SELECT a.IndividualDate , b.orderDate, b.orderInfo
FROM DateRange('d', '2012-11-01', '2012-11-10') as a
LEFT JOIN #temp as b on a.IndividualDate = b.orderDate

결과에서 알 수 있듯이, 매출이 발생하지 않은 일자도 나열 되어 표시된다. 이럴 경우 매출이 없는 일자를 찾기도 쉽다.