WITH TD AS(--当日数据SELECT T1.SALENO,TO_CHAR(T2.FINALTIME, 'YYYY-MM-DD') AS SJD,COUNT(DISTINCT T1.SALENO) AS DDS,--订单数SUM(ROUND( (CASE WHEN T2.recalculate_flag = 0 THEN T1.netprice ELSE T1.recnetprice_gjt END) * T1.wareqty * T1.times + T1.minqty * T1.times * (CASE WHEN T2.recalculate_flag = 0 THEN T1.minprice ELSE T1.recnetprice_gjt END), 2)) AS NETSUM, SUM(ROUND( (CASE WHEN T2.recalculate_flag = 0 THEN T1.stdprice ELSE T1.recnetprice_gjt END) * T1.wareqty * T1.times + T1.minqty * T1.times * (CASE WHEN T2.recalculate_flag = 0 THEN T1.minprice ELSE T1.recnetprice_gjt END), 2)) AS STDSUM, SUM(ROUND( (CASE WHEN T2.recalculate_flag = 0 THEN T1.stdprice ELSE T1.recnetprice_gjt END) * T1.wareqty * T1.times * T1.disrate + T1.minqty * T1.times * (CASE WHEN T2.recalculate_flag = 0 THEN T1.minprice ELSE T1.recnetprice_gjt END), 2) * T1.disrate) AS DISCOUNT, SUM(T1.WAREQTY) AS WAREQTY,--商品支付件数 COUNT( RECEIVER_DETAIL_ADDRESS) AS SHDZ --收货地址 FROM H2.T_SALE_D@DBLINK_HYDEE T1 LEFT JOIN H2.T_SALE_H@DBLINK_HYDEE T2 ON T1.SALENO = T2.SALENO LEFT JOIN H2.D_QM_SALE_H@DBLINK_HYDEE T4 ON T4.SALENO = T2.SALENO LEFT JOIN DIM_SHOP DS ON DS.SHOPCODE = T1.BUSNO LEFT JOIN H2.T_WARE@DBLINK_HYDEE TW ON TW.WAREID = T1.WAREID WHERE 1 = 1AND T1.ACCDATE=TRUNC(SYSDATE) AND TRUNC(SYSDATE)=TO_DATE('${p_enddate}','YYYY-MM-DD') --门店${IF(LEN(p_shop) = 0,"","AND DS.SHOPCODE IN ('"+p_shop+"')")}--商品${IF(LEN(p_shopname) = 0,"","AND TW.WAREID IN ('"+p_shopname+"')")} -- 查询对应平台的支付方式${IF(LEN(p_platform)=0,"AND T2.BTC_TYPE IN ('"+p_ms+"')"," AND T2.BTC_TYPE IN('"+p_ms+"') AND T2.BTC_PLACE_NAME IN ('"+p_platform+"') ")} GROUP BY T1.SALENO,TO_CHAR(T2.FINALTIME, 'YYYY-MM-DD') union all --非当日 SELECT T1.SALENO,TO_CHAR(T2.FINALTIME, 'YYYY-MM-DD') AS SJD,COUNT(DISTINCT T1.SALENO) AS DDS,--订单数SUM(ROUND( (CASE WHEN T2.recalculate_flag = 0 THEN T1.netprice ELSE T1.recnetprice_gjt END) * T1.wareqty * T1.times + T1.minqty * T1.times * (CASE WHEN T2.recalculate_flag = 0 THEN T1.minprice ELSE T1.recnetprice_gjt END), 2)) AS NETSUM, SUM(ROUND( (CASE WHEN T2.recalculate_flag = 0 THEN T1.stdprice ELSE T1.recnetprice_gjt END) * T1.wareqty * T1.times + T1.minqty * T1.times * (CASE WHEN T2.recalculate_flag = 0 THEN T1.minprice ELSE T1.recnetprice_gjt END), 2)) AS STDSUM, SUM(ROUND( (CASE WHEN T2.recalculate_flag = 0 THEN T1.stdprice ELSE T1.recnetprice_gjt END) * T1.wareqty * T1.times * T1.disrate + T1.minqty * T1.times * (CASE WHEN T2.recalculate_flag = 0 THEN T1.minprice ELSE T1.recnetprice_gjt END), 2) * T1.disrate) AS DISCOUNT, SUM(T1.WAREQTY) AS WAREQTY,--商品支付件数 COUNT( RECEIVER_DETAIL_ADDRESS) AS SHDZ --收货地址 FROM BIUSER.ODS_T_SALE_D T1 LEFT JOIN BIUSER.ODS_T_SALE_H T2 ON T1.SALENO = T2.SALENO LEFT JOIN ODS_D_QM_SALE_H T4 ON T4.SALENO = T2.SALENO LEFT JOIN DIM_SHOP DS ON DS.SHOPCODE = T1.BUSNO LEFT JOIN ODS_T_WARE TW ON TW.WAREID = T1.WAREID WHERE 1 = 1 --门店${IF(LEN(p_shop) = 0,"","AND DS.SHOPCODE IN ('"+p_shop+"')")}--商品${IF(LEN(p_shopname) = 0,"","AND TW.WAREID IN ('"+p_shopname+"')")} -- 查询对应平台的支付方式${IF(LEN(p_platform)=0,"AND T2.BTC_TYPE IN ('"+p_ms+"')"," AND T2.BTC_TYPE IN('"+p_ms+"') AND T2.BTC_PLACE_NAME IN ('"+p_platform+"') ")}${if(p_startdate = p_enddate," and T1.ACCDATE between TO_DATE('"+ p_enddate +"','YYYY-MM-DD') -30 and TO_DATE('"+ p_enddate +"','YYYY-MM-DD')"," AND T1.ACCDATE between TO_DATE('"+p_startdate+"','YYYY-MM-DD') and TO_DATE('"+p_enddate+"','YYYY-MM-DD') ")} and T1.ACCDATE != TRUNC(SYSDATE) GROUP BY T1.SALENO,TO_CHAR(T2.FINALTIME, 'YYYY-MM-DD') )SELECT SJD,SUM(DDS) AS DDS,--订单数SUM(CASE WHEN NETSUM<0 THEN 1 ELSE 0 END) AS BACK_NUMBER, --退款订单数SUM(NETSUM) AS NETSUM,--支付金额SUM(WAREQTY) AS WAREQTY,--商品支付件数SUM(NETSUM)/SUM(DDS) AS KDJ,-- 客单价1-SUM(DISCOUNT) / NULLIF(SUM(STDSUM), 0) AS ZKL,--折扣率SUM(SHDZ) AS SHDZ--收货地址FROM TDGROUP BY SJDORDER BY SJD
