22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [PR_InsertAdCostDetail]
@tblCostDetail [AdCostDetailType] READONLY
AS
BEGIN
DECLARE @now DATETIME;
DECLARE @chToday INT;
SET @now = GETDATE();
--把时间变成8位年月日的整数形式,用于sql条件
SET @chToday = DATEPART(yyyy, @now) * 10000 + DATEPART(m, @now) * 100
+ DATEPART(d, @now);
--汇总
DECLARE @tblCost TABLE
(
AdId INT ,
Cost DECIMAL(18, 2)
);
INSERT INTO @tblCost
( AdId ,
Cost
)
SELECT AdId ,
SUM([ActualMoney] * [CostNumber])
FROM @tblCostDetail
GROUP BY AdId
--Begin Tran
--更新扣费汇总表
--更新到每天汇总表
UPDATE a
SET a.Cost = a.Cost + b.Cost ,
UpdateTime = @now
FROM AdvertCostDaily a ,
@tblCost b
WHERE a.AdId = b.AdId
AND a.CostDate = @chToday;
--插入扣费汇总表,先清除更新数据
--DELETE b FROM @tblCost b
-- INNER JOIN AdvertCostDaily a with (nolock) ON a.AdID = b.AdId
-- AND a.CostDate = @chToday;
--插入到每天汇总表
INSERT INTO AdvertCostDaily
( AdID ,
CostDate ,
Cost ,
CreateTime ,
UpdateTime
)
SELECT b.AdId ,
@chToday ,
b.Cost ,
@now ,
@now
FROM @tblCost b
LEFT JOIN AdvertCostDaily a with (nolock) ON a.AdID = b.AdId
AND a.CostDate = @chToday
WHERE a.AdID IS NULL;
--Commit Tran
--Begin Tran
--更新到总汇总表
UPDATE a
SET a.Cost = a.Cost + b.Cost ,
UpdateTime = @now
FROM AdvertCost a ,
@tblCost b
WHERE a.AdId = b.AdId
--插入总汇总表,先清除更新数据
DELETE b FROM @tblCost b
INNER JOIN AdvertCost a with (nolock) ON a.AdID = b.AdId;
--插入到总汇总表
INSERT INTO AdvertCost
( AdID ,
Cost ,
CreateTime ,
UpdateTime
)
SELECT b.AdId ,
b.Cost ,
@now ,
@now
FROM @tblCost b;
--Commit Tran
END