with tmp(REGIST_DATE,WK) as(
values('2024-03-01' , '金'),
      ('2024-03-02' , '土'),
      ('2024-03-03' , '日'),
      ('2024-03-04' , '月'),
      ('2024-03-05' , '火'),
      ('2024-03-06' , '水'),
      ('2024-03-07' , '木'),
      ('2024-03-08' , '金'),
      ('2024-03-09' , '土'),
      ('2024-03-10' , '日'),
      ('2024-03-11' , '月'),
      ('2024-03-12' , '火'),
      ('2024-03-13' , '水'),
      ('2024-03-14' , '木'),
      ('2024-03-15' , '金'),
      ('2024-03-16' , '土'),
      ('2024-03-17' , '日'),
      ('2024-03-18' , '月'),
      ('2024-03-19' , '火'),
      ('2024-03-20' , '水'),
      ('2024-03-21' , '木'),
      ('2024-03-22' , '金'),
      ('2024-03-23' , '土'),
      ('2024-03-24' , '日'),
      ('2024-03-25' , '月'),
      ('2024-03-26' , '火'),
      ('2024-03-27' , '水'),
      ('2024-03-28' , '木')),
tmp2 as(
select WK,REGIST_DATE,
       count(b.SALES_NO) as cnt,coalesce(sum(b.SALES_AMT),0) as sumVal
  from tmp a Left Join SALES b
    on a.REGIST_DATE = b.SALES_DATE
   and (case when SALES_TYPE = 2 
               or UPDATED_NO is not null
             then 1 else 0 end) = 0
group by WK,REGIST_DATE),
tmp3 as (
select WK as WEEK,
round(avg(cnt)) as AVG_CNT,
replace(round(avg(sumVal)),'.0','') as xxx
  from tmp2
group by WK)
select WEEK,AVG_CNT,
case
  when  length(xxx) > 6
     then substr(xxx,1,length(xxx)-6) || ',' || substr(xxx,length(xxx)-5,3) || ',' || substr(xxx,length(xxx)-2,3)
  when  length(xxx) > 3
     then substr(xxx,1,length(xxx)-3) || ',' || substr(xxx,length(xxx)-2,3)
  else  xxx
 end || '円' AS AVG_AMT
 from tmp3
order by case WEEK when '日' then 0
                 when '月' then 1
                 when '火' then 2
                 when '水' then 3
                 when '木' then 4
                 when '金' then 5
                 when '土' then 6 end