ALTER  procedure [dbo].[P_PromotionAnalysisBBB]--促销分析Promotion analysis
    (
      @BeginDate datetime,--开始日期
      @EndDate datetime --结束日期
    )
AS 
    begin
select 
               PromTime,--促销时间
               ShopName,--店铺名称
               PromotionName,--活动名称
               GiftItemName,--促销礼品
               sum(Cost),--成本价格
               sum(OriginalPrice),--原价
               sum(SalesPrice),--销售价格
               sum(DiscountFee),--折扣费用
               sum(Qty)--促销数量
               
from          (select  
                       Convert(date,PromTime) as PromTime,
                       null as ShopName,
                       null as PromotionName,
                       null as GiftItemName,
                       0 as cost,
                       0 as OriginalPrice,
                       0 as SalesPrice,
                       0 as DiscountFee,
                       0 as Qty
                       from dbo.Sales_Order--订单销售表
               where    
                    PromTime > CONVERT(DATE, @BeginDate)and PromTime < CONVERT(DATE, DATEADD(day, 1, @EndDate))
               group by CONVERT(DATE, PromTime)
               union all
               select  
                       0  as ShopId,
                       null as ShopName,
                       GETDATE() as PromTime,
                       PromotionName as PromotionName,
                       GiftItemName  as GiftItemName,
                       0  as cost,
                       0  as OriginalPrice,
                       0  as SalesPrice,
                       DiscountFee   as DiscountFee,
                       0  as Qty
              from Top_PromotionDetail--促销表
              group by GETDATE(),PromotionName
               union all
               select  0   as ShopId,
                       null  as ShopName,
                       GETDATE()  as PromTime,
                       null  as PromotionName,
                       null  as GiftItemName,
                       sum(cost)  as cost,
                       sum(OriginalPrice) as OriginalPrice,
                       sum(SalesPrice)    as SalesPrice,
                       0                  as DiscountFee,
                       count(Qty)         as Qty
               from Sales_OrderItem--订单销售详细表
               
               union all
               select  
                       ShopName  as ShopName,
                       GETDATE()  as PromTime,
                       null  as PromotionName,
                       null  as GiftItemName,
                       0    as cost,
                       0    as OriginalPrice,
                       0    as SalesPrice,
                       0    as DiscountFee,
                       0        as Qty
               from Top_Shop--店铺表
               )a
             group by CONVERT(DATE, PromTime),ShopName ,PromotionName,GiftItemName  
  end