declare @yyyymmdd  datetime

set @yyyymmdd = '2004-03-06'

select  주식명 = b.stock_name  ,
           시세    = sum(case a.in_date when @yyyymmdd then a.sise else 0 end),
           등락    = sum(case a.in_date when @yyyymmdd then a.sise
                                    when DATEADD(day, -1, @yyyymmdd)  then (a.sise * -1) end),
          특정주 = sum(case when (a.in_date between dateadd(day, 1-datepart(dw, @yyyymmdd), @yyyymmdd)
                                              and dateadd(day, 7-datepart(dw, @yyyymmdd), @yyyymmdd))
                          then  a.sise else 0 end) / datepart(dw, @yyyymmdd),
           전주   = sum(case when (a.in_date between dateadd(day, -6-datepart(dw, @yyyymmdd), @yyyymmdd)
                                              and dateadd(day, datepart(dw, @yyyymmdd), @yyyymmdd))
                          then  a.sise else 0 end) / 7
  from  table_시세   a,
        table_주식   b
 where  a.stock_idx  = b.stock_idx
   and (a.in_date between dateadd(day, -6-datepart(dw, @yyyymmdd), @yyyymmdd)
                      and dateadd(day, 7-datepart(dw, @yyyymmdd), @yyyymmdd))