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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· .NET10 - 预览版1新功能体验(一)