연속된 시간(날짜)의 시작일과 마지막일 구하기

-- 연속된 시간(날짜)
-- @TodayStartDate 부터 @TomorrowStartDate 까지의 연속 날자 구하기 (현재 10분 단위)
declare @TodayStartDate datetime = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
declare @TomorrowStartDate datetime = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 1)

select @TodayStartDate, @TomorrowStartDate
select convert(nchar(20),dateadd(MINUTE,number*10,@TodayStartDate),120) as _r
    from master..spt_values with(nolock)
    where type = 'P'
        and convert(nchar(20),dateadd(MINUTE,number*10,@TodayStartDate),120) <= @TomorrowStartDate



-- 연속된 날짜의 시작일과 마지막일
select * into #test from (  
select  '20090101' dd union all
select  '20090102' dd union all
select  '20090105' dd union all
select  '20090106' dd union all
select  '20090107' dd union all
select  '20090110' dd union all
select  '20090111' dd union all
select  '20090121' dd union all
select  '20090201' dd union all
select  '20090301' dd union all
select  '20090302' dd union all
select  '20090305' dd union all
select  '20090401' dd union all
select  '20090402' dd
) as _r

select MIN(dd) std_dt, MAX(dd) end_dt, COUNT(*) cnt from
(
     select dd, ROW_NUMBER() over (order by dd) idx,
            DATEDIFF(day, dd, getdate()) as _no from #test
) r group by idx + _no order by idx + _no desc

drop table #test

댓글 없음: