with cal as (
	select date('2024-03-01') as date, strftime('%w', '2024-03-01') as yobi union all
	select date('2024-03-02'), strftime('%w', '2024-03-02') union all
	select date('2024-03-03'), strftime('%w', '2024-03-03') union all
	select date('2024-03-04'), strftime('%w', '2024-03-04') union all
	select date('2024-03-05'), strftime('%w', '2024-03-05') union all
	select date('2024-03-06'), strftime('%w', '2024-03-06') union all
	select date('2024-03-07'), strftime('%w', '2024-03-07') union all
	select date('2024-03-08'), strftime('%w', '2024-03-08') union all
	select date('2024-03-09'), strftime('%w', '2024-03-09') union all
	select date('2024-03-10'), strftime('%w', '2024-03-10') union all
	select date('2024-03-11'), strftime('%w', '2024-03-11') union all
	select date('2024-03-12'), strftime('%w', '2024-03-12') union all
	select date('2024-03-13'), strftime('%w', '2024-03-13') union all
	select date('2024-03-14'), strftime('%w', '2024-03-14') union all
	select date('2024-03-15'), strftime('%w', '2024-03-15') union all
	select date('2024-03-16'), strftime('%w', '2024-03-16') union all
	select date('2024-03-17'), strftime('%w', '2024-03-17') union all
	select date('2024-03-18'), strftime('%w', '2024-03-18') union all
	select date('2024-03-19'), strftime('%w', '2024-03-19') union all
	select date('2024-03-20'), strftime('%w', '2024-03-20') union all
	select date('2024-03-21'), strftime('%w', '2024-03-21') union all
	select date('2024-03-22'), strftime('%w', '2024-03-22') union all
	select date('2024-03-23'), strftime('%w', '2024-03-23') union all
	select date('2024-03-24'), strftime('%w', '2024-03-24') union all
	select date('2024-03-25'), strftime('%w', '2024-03-25') union all
	select date('2024-03-26'), strftime('%w', '2024-03-26') union all
	select date('2024-03-27'), strftime('%w', '2024-03-27') union all
	select date('2024-03-28'), strftime('%w', '2024-03-28')
)
,sales1 as(
select *
from SALES
where SALES_TYPE <> 2 
and UPDATED_NO is null
)
,sales2 as (
select *
from cal
	left join sales1
	 on cal.date = sales1.SALES_DATE
)
,sales3 as (
select
	date, yobi,
	count(SALES_NO) as sal_cnt,
	ifnull(avg(SALES_AMT), 0) as sal_avg
from sales2
group by date
)
select
	case yobi
		when '0' then '日'
		when '1' then '月'
		when '2' then '火'
		when '3' then '水'
		when '4' then '木'
		when '5' then '金'
		when '6' then '土'
		else 'その他'
	end as WEEK
	,round(avg(sal_cnt), 0) as AVG_CNT
	,cast(round(avg(sal_avg), 0)  as TEXT)
	--,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 sales3
group by yobi
order by yobi