시간별예약현황 달력형태

select
  num1,
  num2,
  num3,
  seatno,
  isnull(max(t1 ),'') as t1 ,
  isnull(max(t2 ),'') as t2 ,
  isnull(max(t3 ),'') as t3 ,
  isnull(max(t4 ),'') as t4 ,
  isnull(max(t5 ),'') as t5 ,
  isnull(max(t6 ),'') as t6 ,
  isnull(max(t7 ),'') as t7 ,
  isnull(max(t8 ),'') as t8 ,
  isnull(max(t9 ),'') as t9 ,
  isnull(max(t10 ),'') as t10 ,
  isnull(max(t11),'') as t11,
  isnull(max(t12),'') as t12,
  isnull(max(t13),'') as t13,
  isnull(max(t14),'') as t14,
  isnull(max(t15),'') as t15,
  isnull(max(t16),'') as t16,
  isnull(max(t17),'') as t17,
  isnull(max(t18),'') as t18,
  isnull(max(t19),'') as t19,
  isnull(max(t20),'') as t20,
  isnull(max(t21),'') as t21,
  isnull(max(t22),'') as t22,
  isnull(max(t23),'') as t23,
  isnull(max(t24),'') as t24
from (
 select
 DATEPART(year, day) as num1,
 DATEPART(month, day) as num2,
 DATEPART(day, day) as num3,
 case when stime<=1
 and etime>=1 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t1,
 case when stime<=2
 and etime>=2 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t2,
 case when stime<=3
 and etime>=3 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t3,
 case when stime<=4
 and etime>=4 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t4,
 case when stime<=5
 and etime>=5 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t5,
 case when stime<=6
 and etime>=6 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t6,
 case when stime<=7
 and etime>=7 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t7,
 case when stime<=8
 and etime>=8 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t8,
 case when stime<=9
 and etime>=9 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t9,
 case when stime<=10
 and etime>=10 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t10,
 case when stime<=11
 and etime>=11 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t11,
 case when stime<=12
 and etime>=12 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t12,
 case when stime<=13
 and etime>=13 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t13,
 case when stime<=14
 and etime>=14 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t14,
 case when stime<=15
 and etime>=15 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t15,
 case when stime<=16
 and etime>=16 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t16,
 case when stime<=17
 and etime>=17 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t17,
 case when stime<=18
 and etime>=18 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t18,
 case when stime<=19
 and etime>=19 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t19,
 case when stime<=20
 and etime>=20 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t20,
 case when stime<=21
 and etime>=21 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t21,
 case when stime<=22
 and etime>=22 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t22,
 case when stime<=23
 and etime>=23 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t23,
 case when stime<=24
 and etime>=24 then seat_name+':'+schoolno+':'+user_name+':'+ bseqno+':'+status else '&nbsp;' end as t24,
 seatno
 from
 (
    select
    c.usestart as day,
    datepart(hh,c.usestart) as stime,
    datepart(hh,c.useexpire) as etime,
    c.seat_name,
    c.seatno,
    c.schoolno,
    CONVERT(varchar(2),c.status) as status,
    CONVERT(varchar(10),c.bseqno) as bseqno,
    c.user_name
    from seatbookingview c
    where c.roomno in(32)
    and c.usestart BETWEEN '2008-08-05 00:00:01' and '2008-08-15 23:59:59'
    and ( c.status = 1 or c.status = 2 or c.status = 3 )
 )
 a
) b
group  by num1,num2,num3, seatno
order by num1,num2,num3, seatno;