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
DECLARE @return_value intEXEC @return_value = [dbo].[P_PromotionAnalysisBBB]
@BeginDate = N'2012-1-1',
@EndDate = N'2012-2-2'SELECT 'Return Value' = @return_valueGO
消息 241,级别 16,状态 1,过程 P_PromotionAnalysisBBB,第 9 行
从字符串转换日期和/或时间时,转换失败。
select isdate(PromTime)
from dbo.Sales_Order
有没有0
dbo.Sales_Order里
PromTime数据库是一个DateTime
insert into @Sales_Order
select '2010-10-01' union all
select '2012-02-01' union all
select '2012-02-30'--得到非法时间
select * from @Sales_Order where isdate(PromTime)=0
/*
PromTime
----------
2012-02-30
*/
的PromTime,--促销时间
时间格式规范