测试环境：

http://10.158.158.22:8080/Manager/index.aspx

Admin047

Panda047

测试入口：

首页

近7天销售额 = 非今天 ，前7天销售额之和；  

近7天订单量 =  非今天 + 前 7天订单量 之和 ；

近7天新增采购单 = 非今天 + 前 7天采购单 之和 ；

相关SQL语句：

操作顺序：

1、时间范围

2、先执行SQL创建临时表，再去查临时表

3、每次查询，需删除临时表再去查

DROP table #order;
SELECT * from #order;
DROP table #product;
SELECT * from #product;
DROP table #procurement;
SELECT * from #procurement;
DROP table #unship;
SELECT * from #unship;
DROP table #unshipold;
SELECT * from #unshipold;
DROP table #ship;
SELECT * from #ship;
--1.总订单数、总销售额

SELECT oi.CustomerID,

       SUM ( CASE WHEN TotalPrice = 0 AND oi.DownLoadUpdateTime= '2000-01-01' THEN 0 ELSE 1 END ) AS OrderNum,

       CONVERT(VARCHAR(100), oi.AddTime, 23) dt,

   os.OrderSourceType OrderSourceType,

       (SUM ( TotalPrice ) / (CASE WHEN SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) = 0 THEN

                        1

                    ELSE

                        SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END)

       END)

       *  SUM ( CASE WHEN TotalPrice = 0 AND oi.DownLoadUpdateTime= '2000-01-01' THEN 0 ELSE 1 END ))AS SalesUSD,

       SUM ( TotalPrice ) / (CASE WHEN SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END) = 0 THEN

                        1

                    ELSE

                        SUM(CASE WHEN TotalPrice = 0 THEN 0 ELSE 1 END)

       END) AS SinglePrice,

       SUM( oi.TotalActivityMinusPrice ) as TotalActivityMinusPrice

into #order

FROM

(

SELECT oi.CustomerID,

oi.OrderSourceID,

oi.OrderCode,

oi.TotalPrice / (CASE WHEN ExchangeUSD = 0 THEN

1

ELSE

ExchangeUSD

END) as TotalPrice,

oi.DownLoadUpdateTime,

oi.ExchangeUSD,

oi.AddTime,

(CASE

WHEN oi.TotalPrice = 0 THEN

0

ELSE

SUM(ol.ActivityMinusPrice/ (CASE

WHEN ExchangeUSD = 0 THEN

1

ELSE

ExchangeUSD

END)

)

END) as TotalActivityMinusPrice

FROM RB_OrderInfo oi WITH (NOLOCK)

LEFT JOIN RB_OrderList ol WITH (NOLOCK)

ON oi.CustomerID = ol.CustomerID and oi.OrderCode = ol.OrderCode

WHERE oi.AddTime >= '2022-03-07'

        AND oi.AddTime < '2022-03-14'

    AND oi.IsStockOrder = 0

    AND IsSystem = 0

    AND

    (

(

IsPay = 1

AND IsFBAOrder = 0

)

OR IsFBAOrder = 1

    )

    AND OrderStatus <> 2

GROUP BY oi.CustomerID,

        oi.OrderSourceID,

        oi.OrderCode,

    oi.TotalPrice,

    oi.DownLoadUpdateTime,

    oi.ExchangeUSD,

    oi.AddTime

)oi

inner JOIN RB_OrderSource (NOLOCK) os

ON oi.OrderSourceID = os.ID  AND oi.CustomerID= os.CustomerID

GROUP BY oi.CustomerID, CONVERT(VARCHAR(100), oi.AddTime, 23),os.OrderSourceType;



--2.最近7天新增产品

SELECT CustomerID,CONVERT(VARCHAR(100), AddTime, 23) dt,

       ISNULL(COUNT(ID),0) AS ProductNum

   into #product

FROM dbo.RB_Product_Admin WITH (NOLOCK)

WHERE AddTime >= '2022-03-08'

      AND AddTime <= '2022-03-14'

GROUP BY CustomerID,CONVERT(VARCHAR(100), AddTime, 23);





--3.最近7天采购单数

SELECT CustomerID,CONVERT(VARCHAR(100), AddTime, 23) dt,

       ISNULL(COUNT(ID),0) AS ProcurementNum

   into #procurement

FROM dbo.RB_Procurement_List WITH (NOLOCK)

WHERE AddTime >= '2022-03-06'

      AND AddTime <= '2022-03-14'

GROUP BY CustomerID,CONVERT(VARCHAR(100), AddTime, 23);





--4.最近7天 (客户/备库订单)本地仓待发

SELECT p.CustomerID,CONVERT(VARCHAR(100), DistributionTime, 23) dt,

       ISNULL( COUNT(CASE o.IsStockOrder WHEN 0 THEN  p.ID ELSE NULL end),0) orderPackageUnShipNum,

    ISNULL(COUNT(CASE o.IsStockOrder WHEN 1 THEN  p.ID ELSE NULL end),0) stockPackageUnShipNum

INTO #unshipold

FROM RB_Procurement_Order_Package p WITH (NOLOCK)

    LEFT JOIN RB_OrderInfo o WITH (NOLOCK)

        ON p.CustomerID = o.CustomerID

           AND p.MergerPackageNo = o.OrderCode

WHERE p.DistributionTime <= '2022-03-12'

      AND p.PackageState IN ( 1, 2 )

GROUP BY p.CustomerID,CONVERT(VARCHAR(100), DistributionTime, 23);



--待发数量包含当天以前总和

SELECT

a.CustomerID,a.dt

,ISNULL(SUM(CASE WHEN b.dt<=a.dt THEN b.orderPackageUnShipNum ELSE 0 END ),0) orderPackageUnShipNum

,ISNULL(SUM(CASE WHEN b.dt<=a.dt THEN b.stockPackageUnShipNum ELSE 0 END ),0) stockPackageUnShipNum

INTO #unship

FROM #unshipold a

LEFT JOIN #unshipold b ON b.CustomerID = a.CustomerID

GROUP BY a.CustomerID,a.dt





--5.最近7天 (客户/备库订单)本地仓已发

SELECT p.CustomerID,CONVERT(VARCHAR(100), p.ShipTime, 23) dt,

   COUNT(CASE o.IsStockOrder WHEN 0 THEN  p.ID ELSE NULL end) orderPackageShipNum,

    COUNT(CASE o.IsStockOrder WHEN 1 THEN  p.ID ELSE NULL end) stockPackageShipNum

INTO #ship

FROM RB_Procurement_Order_Package p WITH (NOLOCK)

    LEFT JOIN RB_OrderInfo o WITH (NOLOCK)

        ON p.CustomerID = o.CustomerID

           AND p.MergerPackageNo = o.OrderCode

WHERE p.ShipTime >= '2022-03-05'

      AND p.ShipTime <= '2022-03-12'

      AND p.PackageState = 3

GROUP BY p.CustomerID,CONVERT(VARCHAR(100), p.ShipTime, 23);






SELECT a.CustomerID,CAST(a.OrderSourceType AS INT) OrderSourceType,

       CAST(a.dt AS DATETIME) dt,

       CAST(ISNULL(a.OrderNum,0) AS INT) OrderNum,

       CAST(ISNULL(a.SalesUSD,0) AS DECIMAL(18, 4)) SalesUSD,

       CAST(ISNULL(a.SinglePrice,0) AS DECIMAL(18, 4)) SinglePrice,

       CAST(ISNULL(b.ProductNum,0) AS INT) ProductNum,

       CAST(ISNULL(c.ProcurementNum,0) AS INT) ProcurementNum,

       CAST(ISNULL(d.orderPackageUnShipNum,0) AS INT) orderPackageUnShipNum,

       CAST(ISNULL(d.stockPackageUnShipNum, 0) AS INT) stockPackageUnShipNum,

       CAST(ISNULL(e.orderPackageShipNum, 0) AS INT) orderPackageShipNum,

       CAST(ISNULL(e.stockPackageShipNum, 0) AS INT)stockPackageShipNum,

       a.TotalActivityMinusPrice

FROM #order a

LEFT JOIN #product b ON b.dt = a.dt AND b.CustomerID=a.CustomerID

LEFT JOIN #procurement c ON c.dt = a.dt AND c.CustomerID=a.CustomerID

LEFT JOIN #unship d ON d.dt = a.dt AND d.CustomerID=a.CustomerID

LEFT JOIN #ship e ON e.dt = a.dt AND e.CustomerID=a.CustomerID

ORDER BY a.dt,a.OrderSourceType DESC;