DB

[MSSQL] 시작과 종료 일자의 날짜와 DATA 가져오기

조셉LEE 2024. 2. 18. 14:00
728x90
반응형

DECLARE @변수1 VARCHAR(10)
DECLARE @시작날짜 VARCHAR(100)
DECLARE @종료날짜 VARCHAR(100)

DECLARE @TMP_TBL TABLE
(
 [컬럼1] VARCHAR(10),
 [컬럼2] VARCHAR(10),
 [컬럼3] VARCHAR(2)
)

-- CURSOR 문 시작
DECLARE CURSOR1 CURSOR FOR
 SELECT [컬럼1], [컬럼2]  FORM USER
OPEN CURSOR1

FETCH NEXT FROM CURSOR1 INTO @변수1, @시작날짜 , @종료날짜
WHILE(@@FETCH_STATUS = 0)     
BEGIN 
 INSERT INTO @TMP_TBL
 select @변수1,
        convert(char(10),dateadd(d,number,@@시작날짜),120) as REGDATE,
        '1' as Value
    from master..spt_values with(nolock)
    where type = 'P'
    and convert(char(10),dateadd(d,number,@@시작날짜),120) < DateAdd("m", 0, @종료날짜)    
 
 FETCH NEXT FROM CURSOR1 INTO @변수1, @시작날짜 , @종료날짜
END

CLOSE CURSOR1;     
                   
DEALLOCATE CURSOR1;      

-- CURSOR 문 종료

--일반검색

SELECT DISTINCT  [컬럼1], SUBSTRING(REGDATE,6,2),VALUE FROM @TMP_TBL 
WHERE REGDATE LIKE '2015%'

--피벗을 이용한 검색

SELECT [컬럼1], ISNULL([01], 0) AS '1월', ISNULL([02], 0) AS '2월', ISNULL([03], 0) AS '3월', ISNULL([04], 0) AS '4월',
    ISNULL([05], 0) AS '5월', ISNULL([06], 0) AS '6월', ISNULL([07], 0) AS '7월', ISNULL([08], 0) AS '8월',
    ISNULL([09], 0) AS '9월', ISNULL([10], 0) AS '10월',ISNULL([11], 0) AS '11월', ISNULL([12], 0) AS '12월'
  FROM (SELECT [컬럼1], SUBSTRING(REGDATE,6,2) AS REGDATE, VALUE FROM @TMP_TBL  
   WHERE REGDATE LIKE '2015%'

반응형
LIST