orcale 数据 同比 环比

日同比

select 
	aa.total 今年销售金额,
	aa.sale 今年销售金额进货,
	aa.re 今年销售金额退货,
	bb.total 去年销售金额,
	bb.sale 去年销售金额进货,
	bb.re 去年销售金额退货
from 
(
select 
	 1 num,	
	 nvl(SUM(L_y.ntotalorigmny),0) total,
	 nvl(sum(case when sign(L_y.ntotalorigmny) > 0 then L_y.ntotalorigmny end),0) sale,
	 nvl(sum(case when sign(L_y.ntotalorigmny) < 0 then L_y.ntotalorigmny end ),0) RE
from(
	SELECT

		m1.taudittime AuditOf,
		m1.ntotalorigmny,
		m1.nreceivedmny
	FROM NCC10.SO_SALEORDER m1
	left join NCC10.bd_customer on m1.ccustomerid=NCC10.bd_customer.pk_customer
	left join NCC10.bd_defdoc on NCC10.bd_customer.def7 = NCC10.bd_defdoc.pk_defdoc
	left join NCC10.bd_customer c10 on c10.pk_customer=bd_customer.DEF3
	WHERE
		--m1.taudittime > '2022-03' and m1.taudittime <'2022-04'
		trunc(to_date(m1.taudittime, 'yyyy-MM-dd HH24:mi:ss'))=trunc(sysdate-1)   --去年同天
		AND m1.DR = 0
		
) L_y
) aa left join 



(
select 
	 1 num,	
	 nvl(SUM(L_x.ntotalorigmny),0) total,
	 nvl(sum(case when sign(L_x.ntotalorigmny) > 0 then L_x.ntotalorigmny end),0) sale,
	 nvl(sum(case when sign(L_x.ntotalorigmny) < 0 then L_x.ntotalorigmny end ),0) RE
from(
	SELECT 
		m2.taudittime AuditOf,
		m2.ntotalorigmny,
		m2.nreceivedmny
	FROM NCC10.SO_SALEORDER m2
	left join NCC10.bd_customer on m2.ccustomerid=NCC10.bd_customer.pk_customer
	left join NCC10.bd_defdoc on NCC10.bd_customer.def7 = NCC10.bd_defdoc.pk_defdoc
	left join NCC10.bd_customer c10 on c10.pk_customer=bd_customer.DEF3
	WHERE
		--m1.taudittime > '2022-03' and m1.taudittime <'2022-04'
		trunc(to_date(m2.taudittime, 'yyyy-MM-dd HH24:mi:ss'))=trunc(sysdate-365)   --去年同天
		AND m2.DR = 0
) L_x

) bb on bb.num = aa.num ;

月同比

select 
	aa.total 今年月销售金额,
	aa.sale 今年月销售金额进货,
	aa.re 今年月销售金额退货,
	bb.total 去年月销售金额,
	bb.sale 去年月销售金额进货,
	bb.re 去年月销售金额退货
from (
-- 去年同月销售
select 
	1 num,
	nvl(SUM(L_y.ntotalorigmny),0) total,
	nvl(sum(case when sign(L_y.ntotalorigmny) > 0 then L_y.ntotalorigmny end),0) sale,
	nvl(sum(case when sign(L_y.ntotalorigmny) < 0 then L_y.ntotalorigmny end ),0) RE
from( 
	SELECT 
		m1.taudittime AuditOf, 
		m1.ntotalorigmny, 
		m1.nreceivedmny 
	FROM NCC10.SO_SALEORDER m1 
	WHERE 
		-- taudittime='2023-01'
		TO_CHAR(trunc(to_date(m1.taudittime, 'yyyy-MM-dd HH24:mi:ss')),'yyyy-MM')= TO_CHAR(ADD_MONTHS(sysdate, -12),'yyyy-MM')
		AND m1.DR = 0 
)L_y

) aa left join 
(
-- 本月销售
select 
	1 num,
	nvl(SUM(L_x.ntotalorigmny),0) total,
	nvl(sum(case when sign(L_x.ntotalorigmny) > 0 then L_x.ntotalorigmny end),0) sale,
	nvl(sum(case when sign(L_x.ntotalorigmny) < 0 then L_x.ntotalorigmny end ),0) RE
from( 
	SELECT 
		m1.taudittime AuditOf, 
		m1.ntotalorigmny, 
		m1.nreceivedmny 
	FROM NCC10.SO_SALEORDER m1 
	WHERE 
		-- taudittime='2023-01'
		TO_CHAR(trunc(to_date(m1.taudittime, 'yyyy-MM-dd HH24:mi:ss')),'yyyy-MM')= TO_CHAR(ADD_MONTHS(sysdate, -0),'yyyy-MM')
		AND m1.DR = 0 
)L_x
) bb on aa.num = bb.num;

月环比

SELECT
	MD.num,
	MD.ererym,
	MD.sale_mon_num,
	MD.LAST_mon_num1,
	TO_CHAR(NVL(ROUND(decode(MD.sale_mon_num,0,null,(MD.sale_mon_num - MD.LAST_mon_num1)/DECODE(MD.LAST_mon_num1,0,1,MD.LAST_mon_num1)*100),2), 0), 'fm9999999990.00') 合计数量环比,
	MD.total_sale_mon_num,
	MD.total_sale_mon_num1,
	TO_CHAR(NVL(ROUND(decode(MD.total_sale_mon_num,0,null,(MD.total_sale_mon_num - MD.total_sale_mon_num1)/DECODE(MD.total_sale_mon_num1,0,1,MD.LAST_mon_num1)*100),2), 0), 'fm9999999990.00') 销售数量环比,
	MD.RE_mon_num,
	MD.RE_mon_num1,
	TO_CHAR(NVL(ROUND(decode(MD.re_mon_num,0,null,(MD.re_mon_num - MD.re_mon_num1)/DECODE(MD.re_mon_num1,0,1,MD.re_mon_num1)*100),2), 0), 'fm9999999990.00') 退货数量环比
from(
	select 
		rownum num,
		DD.ererym,
		DD.sale_mon_num,
		lag(DD.sale_mon_num,1,0) over (ORDER BY DD.ererym) AS LAST_mon_num1,
		DD.total_sale_mon_num,
		lag(DD.total_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS total_sale_mon_num1,
		DD.RE_mon_num,
		lag(DD.re_mon_num,1,0) over (ORDER BY DD.ererym) AS re_mon_num1
	from(
		select 
			daytoday.ererym,
			nvl(daytoday.sale_num,0) sale_mon_num,
			nvl(daytoday.totalsale_num,0) total_sale_mon_num,
			nvl(daytoday.re_num,0) re_mon_num
		from(
			select 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM') ererym,
				nvl(SUM(sd.nnum),0) sale_num，
				nvl(sum(case when sign(m1.ntotalorigmny) > 0 then sd.nnum end),0) totalsale_num，
				nvl(sum(case when sign(m1.ntotalorigmny) < 0 then sd.nnum end),0) re_num
			from NCC10.SO_SALEORDER m1
			left join NCC10.bd_customer on m1.ccustomerid=NCC10.bd_customer.pk_customer
			left join NCC10.bd_defdoc on NCC10.bd_customer.def7 = NCC10.bd_defdoc.pk_defdoc
			left join NCC10.bd_customer c10 on c10.pk_customer=bd_customer.DEF3
			left join NCC10.so_saleorder_b sd ON m1.csaleorderid = sd.CSALEORDERID
			left join NCC10.bd_material bm on sd.cmaterialvid=bm.pk_material
			left join NCC10.bd_marbasclass cl on cl.pk_marbasclass=bm.pk_marbasclass
			left join ncc10.bd_billtype otype on otype.pk_billtypecode = m1.vtrantypecode
			where 
				m1.DR = 0
				and m1.taudittime >= '2022-04-01'
			GROUP BY 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM')
			ORDER BY 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM')
		) daytoday
		RIGHT JOIN (
		-- 取前两个月
		SELECT TO_CHAR(ADD_MONTHS(TO_DATE(to_char(SYSDATE,'yyyy-mm'),'YYYY-MM'), ROWNUM - 12),'YYYY-MM') AS MH
		FROM DUAL
		CONNECT BY ROWNUM <= 12   -- 取最近2个月
		) lagsale on daytoday.ererym=lagsale.mh
		ORDER BY lagsale.mh
	) DD
) MD  where NUM=12 ORDER BY MD.ererym

日环比

SELECT
	DD.num,
	DD.dates,
	DD.sale_day_all_num,
	DD.BFyesterday_num,
	TO_CHAR(NVL(ROUND(decode(DD.sale_day_all_num,0,null,(DD.sale_day_all_num - DD.BFyesterday_num)/DECODE(DD.BFyesterday_num,0,1, DD.BFyesterday_num)),2), 0), 'fm9999999990.00') 销售数量汇总增长率,
	DD.sale_num,
	DD.BFyesterday_sale_num,
	TO_CHAR(NVL(ROUND(decode(DD.sale_num,0,null,(DD.sale_num - DD.BFyesterday_sale_num)/DECODE(DD.BFyesterday_sale_num,0,1, DD.BFyesterday_sale_num)),2), 0), 'fm9999999990.00') 销售数量环比增长率,
	DD.re_num,
	DD.BFyesterday_re_num,
	TO_CHAR(NVL(ROUND(decode(DD.re_num,0,null,(DD.re_num - DD.BFyesterday_re_num)/DECODE(DD.BFyesterday_re_num,0,1, DD.BFyesterday_re_num)),2), 0), 'fm9999999990.00') 退货数量环比增长率
from(

	select 
		rownum num,
		lagsale.dates,
		lagsale.sale_day_all_num,
		lag(lagsale.sale_day_all_num,1,0) over (ORDER BY lagsale.dates) AS BFyesterday_num，
		lagsale.sale_num,
		lag(lagsale.sale_num,1,0) over (ORDER BY lagsale.dates) AS BFyesterday_sale_num，
		lagsale.re_num，
		lag(lagsale.re_num,1,0) over (ORDER BY lagsale.dates) AS BFyesterday_re_num
	from(
		select 
			day.dates,
			nvl(daytoday.totalsale_num,0) sale_day_all_num,
			daytoday.sale_num,
			daytoday.re_num
		from(
			select 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM-DD') ereryday,
				nvl(SUM(sd.nnum),0) totalsale_num,
				nvl(sum(case when sign(ntotalorigmny) > 0 then sd.nnum end),0) sale_num,
				nvl(sum(case when sign(ntotalorigmny) < 0 then sd.nnum end ),0) re_num
			from 
				NCC10.SO_SALEORDER m1
				left join NCC10.bd_customer on m1.ccustomerid=NCC10.bd_customer.pk_customer
				left join NCC10.bd_defdoc on NCC10.bd_customer.def7 = NCC10.bd_defdoc.pk_defdoc
				left join NCC10.bd_customer c10 on c10.pk_customer=bd_customer.DEF3
				left join NCC10.so_saleorder_b sd ON m1.csaleorderid = sd.CSALEORDERID
				left join NCC10.bd_material bm on sd.cmaterialvid=bm.pk_material
				left join NCC10.bd_marbasclass cl on cl.pk_marbasclass=bm.pk_marbasclass
				left join ncc10.bd_billtype otype on otype.pk_billtypecode = m1.vtrantypecode
			where 
			      m1.DR = 0
				and m1.taudittime >= '2022-04-01'
			GROUP BY 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM-DD')
			ORDER BY 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM-DD')
		) daytoday
		RIGHT JOIN (
			SELECT TO_CHAR((sysdate-2)+ ROWNUM-1, 'yyyy-mm-dd') AS dates FROM DUAL CONNECT BY ROWNUM <= 2
		) day on daytoday.ereryday=day.dates
	) lagsale
	ORDER BY lagsale.dates

) DD
WHERE num =2

销售单价环比

SELECT
	MD.num,
	MD.ererym,
	MD.biaozhun_sale_mon_num, -- 标准产品-合计销售数量（进货+退货）
	MD.dadan_sale_mon_num, -- 大单定制-合计销售数量（进货+退货）
	MD.changgui_sale_mon_num, -- 常规定制-合计销售数量（进货+退货）
	
	MD.last_biaozhun_sale_mon_num, -- 上月标准产品-合计销售数量（进货+退货）
	MD.last_dadan_sale_mon_num, -- 上月大单定制-合计销售数量（进货+退货）
	MD.last_changgui_sale_mon_num, -- 上月常规定制-合计销售数量（进货+退货）
	
	
	MD.biaozhun_total_sale_mon_num, -- 标准产品-合计销售数量（进货）
	MD.dadan_total_sale_mon_num, -- 大单定制-合计销售数量（进货）
	MD.changgui_total_sale_mon_num, -- 常规定制-合计销售数量（进货）
	
	MD.last_biaozhun_total_sale_mon_num, -- 上月标准产品-合计销售数量（进货）
	MD.last_dadan_total_sale_mon_num, -- 上月大单定制-合计销售数量（进货）
	MD.last_changgui_total_sale_mon_num, -- 上月常规定制-合计销售数量（进货）
	
	MD.biaozhun_sale_mon, -- 标准产品-合计销售金额（进货+退货）
	MD.dadan_sale_mon, -- 大单定制-合计销售金额（进货+退货）
	MD.changgui_sale_mon, -- 常规定制-合计销售金额（进货+退货）
	
	MD.last_biaozhun_sale_mon, -- 上月标准产品-合计销售金额（进货+退货）
	MD.last_dadan_sale_mon, -- 上月大单定制-合计销售金额（进货+退货）
	MD.last_changgui_sale_mon, -- 上月常规定制-合计销售金额（进货+退货）
	
	MD.biaozhun_total_sale_mon, -- 标准产品-合计销售金额（进货）
	MD.dadan_total_sale_mon, -- 大单定制-合计销售金额（进货）
	MD.changgui_total_sale_mon, -- 常规定制-合计销售金额（进货）
	
	MD.last_biaozhun_total_sale_mon, -- 上月标准产品-合计销售金额（进货）
	MD.last_dadan_total_sale_mon, -- 上月大单定制-合计销售金额（进货）
	MD.last_changgui_total_sale_mon, -- 上月常规定制-合计销售金额（进货）
	
	
	TO_CHAR(NVL(ROUND(DECODE(MD.biaozhun_sale_mon,0,NULL,MD.biaozhun_sale_mon)/DECODE(MD.biaozhun_sale_mon_num,0,1,MD.biaozhun_sale_mon_num),2),0), 'fm9999999990.00') 标准产品进货退货单价,
	TO_CHAR(NVL(ROUND(DECODE(MD.dadan_sale_mon,0,NULL,MD.dadan_sale_mon)/DECODE(MD.dadan_sale_mon_num,0,1,MD.dadan_sale_mon_num),2),0), 'fm9999999990.00') 大单定制进货退货单价,
	TO_CHAR(NVL(ROUND(DECODE(MD.changgui_sale_mon,0,NULL,MD.changgui_sale_mon)/DECODE(MD.changgui_sale_mon_num,0,1,MD.changgui_sale_mon_num),2),0), 'fm9999999990.00') 常规定制进货退货单价,

	TO_CHAR(NVL(ROUND(DECODE(MD.biaozhun_total_sale_mon,0,NULL,MD.biaozhun_total_sale_mon)/DECODE(MD.biaozhun_total_sale_mon_num,0,1,MD.biaozhun_total_sale_mon_num),2),0), 'fm9999999990.00') 标准产品进货单价,
	TO_CHAR(NVL(ROUND(DECODE(MD.dadan_total_sale_mon,0,NULL,MD.dadan_total_sale_mon)/DECODE(MD.dadan_total_sale_mon_num,0,1,MD.dadan_total_sale_mon_num),2),0), 'fm9999999990.00') 大单定制进货单价,
	TO_CHAR(NVL(ROUND(DECODE(MD.changgui_total_sale_mon,0,NULL,MD.changgui_total_sale_mon)/DECODE(MD.changgui_total_sale_mon_num,0,1,MD.changgui_total_sale_mon_num),2),0), 'fm9999999990.00') 常规定制进货单价,
	
	
	TO_CHAR(NVL(ROUND((DECODE(MD.biaozhun_sale_mon,0,NULL,MD.biaozhun_sale_mon)/DECODE(MD.biaozhun_sale_mon_num,0,1,MD.biaozhun_sale_mon_num) - DECODE(MD.last_biaozhun_sale_mon,0,NULL,MD.last_biaozhun_sale_mon)/DECODE(MD.last_biaozhun_sale_mon_num,0,1,MD.last_biaozhun_sale_mon_num))/(	DECODE(MD.last_biaozhun_sale_mon,0,NULL,MD.last_biaozhun_sale_mon)/DECODE(MD.last_biaozhun_sale_mon_num,0,1,MD.last_biaozhun_sale_mon_num))*100,2),0), 'fm9999999990.00') 标准产品进货退货环比,
	TO_CHAR(NVL(ROUND((	DECODE(MD.dadan_sale_mon,0,NULL,MD.dadan_sale_mon)/DECODE(MD.dadan_sale_mon_num,0,1,MD.dadan_sale_mon_num) - DECODE(MD.last_dadan_sale_mon,0,NULL,MD.last_dadan_sale_mon)/DECODE(MD.last_dadan_sale_mon_num,0,1,MD.last_dadan_sale_mon_num))/(DECODE(MD.last_dadan_sale_mon,0,NULL,MD.last_dadan_sale_mon)/DECODE(MD.last_dadan_sale_mon_num,0,1,MD.last_dadan_sale_mon_num))*100,2),0), 'fm9999999990.00') 大单定制进货退货环比,
	TO_CHAR(NVL(ROUND((DECODE(MD.changgui_sale_mon,0,NULL,MD.changgui_sale_mon)/DECODE(MD.changgui_sale_mon_num,0,1,MD.changgui_sale_mon_num) - DECODE(MD.last_changgui_sale_mon,0,NULL,MD.last_changgui_sale_mon)/DECODE(MD.last_changgui_sale_mon_num,0,1,MD.last_changgui_sale_mon_num))/(DECODE(MD.last_changgui_sale_mon,0,NULL,MD.last_changgui_sale_mon)/DECODE(MD.last_changgui_sale_mon_num,0,1,MD.last_changgui_sale_mon_num))*100,2),0), 'fm9999999990.00') 常规定制进货退货环比,
	
	
	TO_CHAR(NVL(ROUND((DECODE(MD.biaozhun_total_sale_mon,0,NULL,MD.biaozhun_total_sale_mon)/DECODE(MD.biaozhun_total_sale_mon_num,0,1,MD.biaozhun_total_sale_mon_num) - DECODE(MD.last_biaozhun_total_sale_mon,0,NULL,MD.last_biaozhun_total_sale_mon)/DECODE(MD.last_biaozhun_total_sale_mon_num,0,1,MD.last_biaozhun_total_sale_mon_num))/(DECODE(MD.last_biaozhun_total_sale_mon,0,NULL,MD.last_biaozhun_total_sale_mon)/DECODE(MD.last_biaozhun_total_sale_mon_num,0,1,MD.last_biaozhun_total_sale_mon_num))*100,2),0), 'fm9999999990.00') 标准产品进货环比,
	TO_CHAR(NVL(ROUND((DECODE(MD.dadan_total_sale_mon,0,NULL,MD.dadan_total_sale_mon)/DECODE(MD.dadan_total_sale_mon_num,0,1,MD.dadan_total_sale_mon_num) - DECODE(MD.last_dadan_total_sale_mon,0,NULL,MD.last_dadan_total_sale_mon)/DECODE(MD.last_dadan_total_sale_mon_num,0,1,MD.last_dadan_total_sale_mon_num))/(DECODE(MD.last_dadan_total_sale_mon,0,NULL,MD.last_dadan_total_sale_mon)/DECODE(MD.last_dadan_total_sale_mon_num,0,1,MD.last_dadan_total_sale_mon_num))*100,2),0), 'fm9999999990.00') 大单定制进货环比,
	TO_CHAR(NVL(ROUND((DECODE(MD.changgui_total_sale_mon,0,NULL,MD.changgui_total_sale_mon)/DECODE(MD.changgui_total_sale_mon_num,0,1,MD.changgui_total_sale_mon_num) - DECODE(MD.last_changgui_total_sale_mon,0,NULL,MD.last_changgui_total_sale_mon)/DECODE(MD.last_changgui_total_sale_mon_num,0,1,MD.last_changgui_total_sale_mon_num))/(DECODE(MD.last_changgui_total_sale_mon,0,NULL,MD.last_changgui_total_sale_mon)/DECODE(MD.last_changgui_total_sale_mon_num,0,1,MD.last_changgui_total_sale_mon_num))*100,2),0), 'fm9999999990.00') 常规定制进货环比

from(
	select 
		rownum num,
		DD.ererym,
		-- 合计销售数量（进货+退货）
		DD.biaozhun_sale_mon_num,
		DD.dadan_sale_mon_num,
		DD.changgui_sale_mon_num,
		-- 合计销售数量（进货+退货）
		lag(DD.biaozhun_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS last_biaozhun_sale_mon_num,
		lag(DD.dadan_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS last_dadan_sale_mon_num,
		lag(DD.changgui_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS last_changgui_sale_mon_num,
		-- 合计销售数量（进货）
		DD.biaozhun_total_sale_mon_num,
		DD.dadan_total_sale_mon_num,
		DD.changgui_total_sale_mon_num,
		-- 上月合计销售数量（进货）
		lag(DD.biaozhun_total_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS last_biaozhun_total_sale_mon_num,
		lag(DD.dadan_total_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS last_dadan_total_sale_mon_num,
		lag(DD.changgui_total_sale_mon_num,1,0) over (ORDER BY DD.ererym) AS last_changgui_total_sale_mon_num,
		-- 合计销售金额（进货+退货）
		DD.biaozhun_sale_mon,
		DD.dadan_sale_mon,
		DD.changgui_sale_mon,
		-- 上月合计销售金额（进货+退货）
		lag(DD.biaozhun_sale_mon,1,0) over (ORDER BY DD.ererym) AS last_biaozhun_sale_mon,
		lag(DD.dadan_sale_mon,1,0) over (ORDER BY DD.ererym) AS last_dadan_sale_mon,
		lag(DD.changgui_sale_mon,1,0) over (ORDER BY DD.ererym) AS last_changgui_sale_mon,
		-- 合计销售金额（进货）
		DD.biaozhun_total_sale_mon,
		DD.dadan_total_sale_mon,
		DD.changgui_total_sale_mon,
		-- 上月合计销售金额（进货）
		lag(DD.biaozhun_total_sale_mon,1,0) over (ORDER BY DD.ererym) AS last_biaozhun_total_sale_mon,
		lag(DD.dadan_total_sale_mon,1,0) over (ORDER BY DD.ererym) AS last_dadan_total_sale_mon,
		lag(DD.changgui_total_sale_mon,1,0) over (ORDER BY DD.ererym) AS last_changgui_total_sale_mon

	from(
		select 
			nvl(daytoday.ererym, lagsale.mh) ererym,
			nvl(daytoday.biaozhun_sale_num,0) biaozhun_sale_mon_num,
			nvl(daytoday.dadan_sale_num,0) dadan_sale_mon_num,
			nvl(daytoday.changgui_sale_num,0) changgui_sale_mon_num,
			
			nvl(daytoday.biaozhun_totalsale_num,0) biaozhun_total_sale_mon_num,
			nvl(daytoday.dadan_totalsale_num,0) dadan_total_sale_mon_num,
			nvl(daytoday.changgui_totalsale_num,0) changgui_total_sale_mon_num,

			nvl(daytoday.biaozhun_sale,0) biaozhun_sale_mon,
			nvl(daytoday.dadan_sale,0) dadan_sale_mon,
			nvl(daytoday.changgui_sale,0) changgui_sale_mon,
			
			nvl(daytoday.biaozhun_totalsale,0) biaozhun_total_sale_mon,
			nvl(daytoday.dadan_totalsale,0) dadan_total_sale_mon,
			nvl(daytoday.changgui_totalsale,0) changgui_total_sale_mon

		from(
			select 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM') ererym,
-- 				nvl(SUM(sd.nnum),0) sale_num，
				sum(case when bm.code like '030101%' then 
				-- 单独品做数量  
				-- 巨肆玖
					case when bm.code in ('030101007000001','030101099000139', '0301013') then nvl(sd.nnum,0) * 49 else 
					  --  生肖
						case when bm.code like '030101006%' then nvl(sd.nnum,0) * 2 else nvl(sd.nnum,0) end	
					end
				else 0 end) biaozhun_sale_num,
				
				sum(case when bm.code like '030103%' then nvl(sd.nnum,0) else 0 end) dadan_sale_num,
				sum(case when bm.code like '030104%' then nvl(sd.nnum,0) else 0 end) changgui_sale_num,
-- 				nvl(sum(case when sign(m1.ntotalorigmny) > 0 then sd.nnum end),0) totalsale_num，

				sum(case when bm.code like '030101%' and sign(m1.ntotalorigmny) > 0 then 
				-- 单独品做数量
				-- 巨肆玖
					case when bm.code in ('030101007000001','030101099000139', '0301013') then nvl(sd.nnum,0) * 49 else 
					  --  生肖
						case when bm.code like '030101006%' then nvl(sd.nnum,0) * 2 else nvl(sd.nnum,0) end	
					end
				else 0 end) biaozhun_totalsale_num,
				sum(case when bm.code like '030103%' and sign(m1.ntotalorigmny) > 0 then nvl(sd.nnum,0) else 0 end) dadan_totalsale_num,
				sum(case when bm.code like '030104%' and sign(m1.ntotalorigmny) > 0 then nvl(sd.nnum,0) else 0 end) changgui_totalsale_num,
				
-- 				nvl(sum(case when sign(m1.ntotalorigmny) < 0 then sd.nnum end),0) re_num,
-- 
-- 				nvl(SUM(m1.ntotalorigmny),0) sale,
				sum(case when bm.code like '030101%' then nvl(m1.ntotalorigmny,0) else 0 end) biaozhun_sale,
				sum(case when bm.code like '030103%' then nvl(m1.ntotalorigmny,0) else 0 end) dadan_sale,
				sum(case when bm.code like '030104%' then nvl(m1.ntotalorigmny,0) else 0 end) changgui_sale,

-- 				nvl(sum(case when sign(m1.ntotalorigmny) > 0 then m1.ntotalorigmny end),0) totalsale,
				sum(case when bm.code like '030101%' and sign(m1.ntotalorigmny) > 0 then nvl(m1.ntotalorigmny,0) else 0 end) biaozhun_totalsale,
				sum(case when bm.code like '030103%' and sign(m1.ntotalorigmny) > 0 then nvl(m1.ntotalorigmny,0) else 0 end) dadan_totalsale,
				sum(case when bm.code like '030104%' and sign(m1.ntotalorigmny) > 0 then nvl(m1.ntotalorigmny,0) else 0 end) changgui_totalsale

-- 				nvl(sum(case when sign(m1.ntotalorigmny) < 0 then m1.ntotalorigmny end),0) re

			from NCC10.SO_SALEORDER m1
			left join NCC10.bd_customer on m1.ccustomerid=NCC10.bd_customer.pk_customer
			left join NCC10.bd_defdoc on NCC10.bd_customer.def7 = NCC10.bd_defdoc.pk_defdoc
			left join NCC10.bd_customer c10 on c10.pk_customer=bd_customer.DEF3
			left join NCC10.so_saleorder_b sd ON m1.csaleorderid = sd.CSALEORDERID
			left join NCC10.bd_material bm on sd.cmaterialvid=bm.pk_material
			left join NCC10.bd_marbasclass cl on cl.pk_marbasclass=bm.pk_marbasclass
			left join ncc10.bd_billtype otype on otype.pk_billtypecode = m1.vtrantypecode
			where 
				m1.DR = 0
				and m1.taudittime >= '2022-04-01'
			GROUP BY 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM')
			ORDER BY 
				TO_CHAR(TO_DATE(m1.taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM')
		) daytoday
		RIGHT JOIN (
		-- 取前两个月
		SELECT TO_CHAR(ADD_MONTHS(TO_DATE(to_char(SYSDATE,'yyyy-mm'),'YYYY-MM'), ROWNUM - 13),'YYYY-MM') AS MH
		FROM DUAL
		CONNECT BY ROWNUM <= 12   -- 取最近2个月
		) lagsale on daytoday.ererym=lagsale.mh
		ORDER BY lagsale.mh
	) DD
) MD  where NUM=12 ORDER BY MD.ererym

销售日标时度 时间进度 双进度

-- 季度销售额完成度     当日销售金额/计划销售额 / 当前日/ 总日





-- 计算两个日期间的天数    
-- select to_date('2023-01-31','yyyy-MM-dd') - to_date('2023-01-01','yyyy-MM-dd') + 1 from dual;

-- 数累上个天的金额
-- sum(nvl(day.num,0)) over( order by day.dates)

-- 三个变量
-- 开始时间 '2023-01-01'
-- 结束时间 '2023-01-31'
-- 计划金额 15864651

select 
-- 日期
	dates,
-- 当日销售金额（进货退货）
	totalsale,
-- 当日销售金额（进货）
	sale,
-- 当日销售金额（退货）
	re,
-- 累计至当前日销售金额（进货退货）
	total_totalsale,
-- 累计至当前日销售金额（进货）
	total_sale,
-- 累计至当前日销售金额（退货）
	total_re,
-- 累计至当前日时间进度
	total_num,
-- 当日销售金额/计划金额（进货退货）
	schedule_totalsale,
-- 当日销售金额/计划金额（进货）
	schedule_sale,
-- 当日销售金额/计划金额（退货）
	schedule_re,
-- 当日时间进度/当前总时间天数
	schedule_num,
-- 双进度
	round((schedule_sale-schedule_num)/schedule_num*100,2) schedule_sale_num
	from (
		select 
				day.num,
				day.dates,
				nvl(daytoday.totalsale,0) totalsale,
				nvl(daytoday.sale,0) sale,
				nvl(daytoday.re,0) re,
				sum(nvl(daytoday.totalsale,0)) over( order by day.dates) total_totalsale,
				sum(nvl(daytoday.sale,0)) over( order by day.dates) total_sale,
				sum(nvl(daytoday.re,0)) over( order by day.dates) total_re,
				sum(nvl(day.num,0)) over( order by day.dates) total_num,
				
				round(sum(nvl(daytoday.totalsale,0)) over( order by day.dates)/236000000*100, 2) schedule_totalsale,
				round(sum(nvl(daytoday.sale,0)) over( order by day.dates)/236000000*100, 2) schedule_sale,
				round(sum(nvl(daytoday.re,0)) over( order by day.dates)/236000000*100, 2) schedule_re,
				round(sum(nvl(day.num,0)) over( order by day.dates)/(to_date('2023-03-31','yyyy-MM-dd') - to_date('2023-01-01','yyyy-MM-dd') + 1)*100, 2) schedule_num
				
			from(
				select 
					TO_CHAR(TO_DATE(taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM-DD') ereryday,
					nvl(SUM(ntotalorigmny),0) totalsale,
					nvl(sum(case when sign(ntotalorigmny) > 0 then ntotalorigmny end),0) sale,
					nvl(sum(case when sign(ntotalorigmny) < 0 then ntotalorigmny end ),0) RE
				from 
					NCC10.SO_SALEORDER 
				where 
					SO_SALEORDER.DR = 0
					AND DR = 0 
					and taudittime <='2023-03-31'
					and taudittime >='2023-01-01'
				GROUP BY 
					TO_CHAR(TO_DATE(taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM-DD')
				ORDER BY 
					TO_CHAR(TO_DATE(taudittime,'yyyy-MM-dd hh24:mi:ss'),'YYYY-MM-DD')
			) daytoday
			RIGHT JOIN (
				SELECT TO_CHAR(to_date('2023-01-01','yyyy-MM-dd')+ ROWNUM-1, 'yyyy-mm-dd') AS dates, 1 num FROM DUAL CONNECT BY ROWNUM <= (to_date('2023-03-31','yyyy-MM-dd') - to_date('2023-01-01','yyyy-MM-dd') + 1)
			) day on daytoday.ereryday=day.dates order by day.dates
		) L_y