Sqler에 있는 질문에 대해 풀어봤다.

세번째 풀이

sqler를 들려보니 각 일별로 분을 만든게 떠있네? 음...

그래서 다시 만들어봤다. 1시간 소요 ㅠㅠ... 만들어 놓은걸 안보고 해봤음. ( 보고 싶은 유혹이... )

역시나 아직 내가 놓치는 부분들이 있네.

 

 

;with ttb as
(
  select DATEADD( HOUR, -8, convert( datetime, st )) as st, DATEADD( HOUR, -8, convert( datetime, ed)) as ed    
  from
  (
    values
     ( '2016-05-09 09:00:00', '2016-05-09 09:30:00' ),
      ( '2016-05-09 23:00:00', '2016-05-10 09:00:00' ),
      ( '2016-05-10 22:20:00', '2016-05-11 05:00:00' ),  -- 테스트를 위해 추가.
     ( '2016-05-12 22:20:00', '2016-05-15 05:00:00' )  -- 테스트를 위해 추가.
  ) as tb ( st, ed)
 /* 문제 :
  이와같은 데이터가 있을때 합산하는 시점은 오전 08시로 기준을 잡고
  2016-05-09 : 570분    (9:00 ~ 09:30 = 30분 + 23:00 ~ 익일 08:00 = 540분)
  2016-05-10 : 60분     (기준시간 08:00~ 09:00 = 60분)
  이렇게 SELECT 를 하고싶은데요.. 너무헷갈립니다 고수분들 조언부탁 드립니다.
 */
), otb ( st, ed )
as
(
 select DATEADD( hour, -8, convert( datetime, st )) as st,
        DATEADD( hour, -8, convert( datetime, ed )) as ed
   from ttb 
)
, rtb ( diff, dt )
as
(
 select DATEDIFF( day, min( st ), max( ed ) ) as diff, convert( datetime, convert( varchar(10), min( st ), 121 )) as dt
   from otb
)
, jtb ( dt1 , dt2)
as
(
 select DATEADD( Day, number, dt ) as dt1, dateadd( MILLISECOND, -2, DATEADD( Day, number + 1, dt )) as dt2
 from rtb, (
    select number
    from master.dbo.spt_values
    where name is null ) as ntb
 where ntb.number <= rtb.diff
)
select dt1, dt2, case when st is null then 0
      else
           case when dt1 <= st then st else dt1 end
    end as st
    , case when ed is null then 0
         else case when ed < dt2 then ed else dt2 end
       end as ed,
    datediff( MINUTE,
    case when st is null then 0
      else
           case when dt1 <= st then st else dt1 end
    end,
    case when ed is null then 0
         else case when ed < dt2 then ed else dt2 end
       end )
     as diff
    
  from jtb
  left
 outer join otb
    on ( otb.ed > jtb.dt1 and otb.st < jtb.dt2 ) 
  

 

 

/###############################################################################/

 

아래가 문제인데...

08시를 기준으로 시작시간, 종료시간 분(시간) 구하기.

우선 급한데로 풀이를 한다면... 아래처럼 구해보겠다. 테스트 까지 1시간 조금 안걸린듯....

 

첫번째 풀이

;with ttb as
(
 select convert( datetime, st ) as st, convert( datetime, ed) as ed    
 from
 (
  values
   ( '2016-05-09 09:00:00', '2016-05-09 09:30:00' ),
    ( '2016-05-09 23:00:00', '2016-05-10 09:00:00' ),
   ( '2016-05-10 22:20:00', '2016-05-11 05:00:00' ) -- 테스트를 위해 추가.
 ) as tb ( st, ed)
/* 문제 :
 이와같은 데이터가 있을때 합산하는 시점은 오전 08시로 기준을 잡고
 2016-05-09 : 570분    (9:00 ~ 09:30 = 30분 + 23:00 ~ 익일 08:00 = 540분)
 2016-05-10 : 60분     (기준시간 08:00~ 09:00 = 60분)
 이렇게 SELECT 를 하고싶은데요.. 너무헷갈립니다 고수분들 조언부탁 드립니다.
*/
)

select dt, sum( case when m > 0 then m else 0 end) as sum_m
from
(
 SELECT  convert( varchar(10), st, 121) as dt,
   case when DATEDIFF( DAY, st, ed ) > 0 and CONVERT( datetime, CONVERT( varchar(10), ed , 112) + ' 08:00:00' ) < ed then DATEDIFF( MINUTE, st, CONVERT( datetime, CONVERT( varchar(10), ed , 112) + ' 08:00:00' ) )
     else DATEDIFF( MINUTE, st, ed)
   end as m
   FROM  ttb

 UNION ALL

 SELECT  convert( varchar(10), ed, 121) as dt,
   DATEDIFF( MINUTE, CONVERT( datetime, CONVERT( varchar(10), ed , 112) + ' 08:00:00' ), ed ) as m
   FROM  ttb
 WHERE DATEDIFF( DAY, st, ed ) > 0 and CONVERT( datetime, CONVERT( varchar(10), ed , 112) + ' 08:00:00' ) < ed
) s
group by dt
 

 

두번재 풀이 ( 10분만에 썼음. )

 

위 식에서 다음날 08시 기준이니까 -8시간 하면 00시 정각이 되니 이것으로 시간을 구함.

 

;with ttb as
(
 select DATEADD( HOUR, -8, convert( datetime, st )) as st, DATEADD( HOUR, -8, convert( datetime, ed)) as ed    
 from
 (
  values
   ( '2016-05-09 09:00:00', '2016-05-09 09:30:00' ),
    ( '2016-05-09 23:00:00', '2016-05-10 09:00:00' )
--   ( '2016-05-10 22:20:00', '2016-05-11 05:00:00' )  -- 테스트를 위해 추가.
 ) as tb ( st, ed)
/* 문제 :
 이와같은 데이터가 있을때 합산하는 시점은 오전 08시로 기준을 잡고
 2016-05-09 : 570분    (9:00 ~ 09:30 = 30분 + 23:00 ~ 익일 08:00 = 540분)
 2016-05-10 : 60분     (기준시간 08:00~ 09:00 = 60분)
 이렇게 SELECT 를 하고싶은데요.. 너무헷갈립니다 고수분들 조언부탁 드립니다.
*/
)

select dt, sum( case when m > 0 then m else 0 end) as sum_m
from
(
 select convert( varchar(10), st, 121) as dt, DATEDIFF( MINUTE, st, ed ) - case when DATEDIFF( DAY, st, ed ) >= 1 then DATEDIFF( MINUTE, CONVERT( datetime, CONVERT( varchar(10), ed , 112) + ' 00:00:00' ), ed ) else 0 end as m
   from ttb
 union all
 select convert( varchar(10), ed, 121) as dt, DATEDIFF( MINUTE, CONVERT( datetime, CONVERT( varchar(10), ed , 112) + ' 00:00:00' ), ed )
   from ttb
  where DATEDIFF( DAY, st, ed ) > 0

) s
group by dt