DECLARE @P NVARCHAR(128)
DECLARE @q NVARCHAR(128)
SET @P = '2025-11-01'
SET @q = '2025-11-30'
begin
WITH
净面积 as (
select
t.IntentCustomerId id ,
t1.RoomArea as 净面积
from dbo.Bud_Budget t
inner join Bud_BudgetMaster t1 on t1.BudgetId=t.BudgetId
where t1.MasterName like'%全房项目%'
group by t.IntentCustomerId,t1.RoomArea
),
面积1 as (
select
t.IntentCustomerId,
SUM(CASE WHEN t2.SpecialCode = 'mj' then ISNULL(t2.JSBudNum,0) else 0 END) as 建筑面积
from dbo.Bud_Budget t
inner join dbo.Bud_BudgetMaster t1 on t.BudgetId=t1.BudgetId
inner join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
where t2.SpecialCode = 'mj'
group by t.IntentCustomerId
),
卫生间 AS (
select
t.IntentCustomerId,
ISNULL(SUM(t2.JSCompreMoney),0) as 卫生间
from dbo.Bud_Budget t
inner join dbo.Bud_BudgetMaster t1 on t.BudgetId=t1.BudgetId
inner join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
where t2.SpecialCode='wsj'
group by t.IntentCustomerId
),
已付工程款 as (
SELECT
bill.IntentCustomerId as crm_id,
SUM(detail.Amount) as 已付工程款
FROM FI_PaymentBill as bill
LEFT JOIN FI_PaymentBillDetail as detail ON bill.PaymentBillId = detail.PaymentBillId
WHERE BillType = 0 and Status = 2 AND IsReverse = 0
GROUP BY bill.IntentCustomerId
),
已付材料款 as (
SELECT
B.IntentCustomerId,
SUM(BD.StockMoney ) as 实付金额
from [dbo].[Mm_BillMaster] B
left JOIN dbo.Mm_BillDetail BD ON B.BillMasterID = BD.BillMasterId
WHERE BD.[IsDelete] = 0 AND B.BillType IN ( 5, 6 ) AND B.SaleCustomertId IS NULL and B.BillStatus=2 --AND B.IsOff = 0
group by B.IntentCustomerId
),
结算费用 as (
select
t.IntentCustomerId,
SUM(CASE WHEN t2.SpecialCode='BC1' THEN t2.JSBudNum END) as 管理费补贴,
--SUM(CASE WHEN t2.SpecialCode='BC2' THEN t2.JSBudNum END) as 工长补贴,
SUM(CASE WHEN t2.SpecialCode like 'Z%' THEN t2.JSContractMoney END) as 结算直接费,
SUM(CASE WHEN t2.SpecialCode='h12' THEN t2.JSCompreMoney * 0.5 END) as 远程施工费发包,
SUM(CASE WHEN t2.SpecialCode like 'TH%' or t2.SpecialCode like 'C%' THEN t2.JSContractMoney END) as 主材,
SUM(CASE WHEN t2.SpecialCode like 'Z%' THEN t2.JSMaterialMoney END) as 辅材,
SUM(CASE WHEN t2.SpecialCode like 'Z%' THEN t2.JSHumanMoney END) as 人工,
SUM(CASE WHEN t2.SpecialCode like 'Z%' THEN t2.YSHumanMoney END) as 预算人工,
SUM(CASE WHEN t2.SpecialCode like 'Z%' THEN t2.JSHumanMoney*0.03 END) as 质保金,
SUM(CASE WHEN t2.SpecialCode= 'h14' THEN t2.JSCompreMoney END) as 活动基础优惠,
SUM(CASE WHEN t2.SpecialCode= 'h15' THEN t2.JSCompreMoney END) as 预算折单优惠,
SUM(CASE WHEN t2.SpecialCode= 'h18' or t2.SpecialCode= 'h17' THEN t2.JSCompreMoney END) as 自带单,
SUM(CASE WHEN t2.SpecialCode= 'h19' THEN t2.JSCompreMoney END) as 自然到店优惠,
SUM(CASE WHEN t2.SpecialCode= 'h20' THEN t2.JSCompreMoney END) as 基金池基金优惠,
SUM(CASE WHEN t2.SpecialCode= 'h21' THEN t2.JSCompreMoney END) as 特殊优惠,
SUM(CASE WHEN t2.SpecialCode= 'BC2' THEN t2.JSCompreMoney END) as 小组基金,
SUM(CASE WHEN t2.SpecialCode= 'h22' THEN t2.JSCompreMoney END) as 优惠金额,
--sum(case when t2.SpecialCode like 'C%' then t2.JSCompreMoney end) as 主材个性化,
SUM(CASE WHEN t2.SpecialCode= 'h23' THEN t2.JSCompreMoney END) as 客介优惠,
SUM(CASE WHEN t2.SpecialCode= 'h24' THEN t2.JSCompreMoney END) as 平米优惠,
SUM(CASE WHEN t2.SpecialCode= 'h45' THEN t2.JSCompreMoney END) as 活动,
SUM(CASE WHEN t2.SpecialCode= 'h7' THEN t2.JSCompreMoney END) as 税金,
SUM(CASE WHEN t2.SpecialCode='h40' THEN t2.JSBudNum END) as 合同款
--CASE WHEN t2.SpecialCode= 'h15' THEN t2.JSCompreMoney END as 折单
from dbo.Bud_Budget t
left join dbo.Bud_BudgetMaster t1 on t.BudgetId=t1.BudgetId
left join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
group by t.IntentCustomerId
),
总预算人工费 as (
select
IntentCustomerId,
isnull(sum(t2.YSHumanMoney),0) 预算费用
from dbo.Bud_Budget t
inner join dbo.Bud_BudgetMaster t1 on t.BudgetId=t1.BudgetId
inner join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
where t2.SpecialCode like 'Z%'
group by IntentCustomerId
union all
SELECT
t1.IntentCustomerId,
isnull(SUM(t3.HumanPrice*(t2.NewNum-t2.OriginalNum)),0) 预算费用
FROM Bud_BudgetChangeBillMaster t1
INNER JOIN Bud_BudgetChangeBillDetail t2 ON t1.BillId=t2.BillId
INNER JOIN dbo.Crm_IntentCustomer cic ON t1.IntentCustomerId=cic.IntentCustomerId
INNER JOIN Base_QuotaMaster t3 ON t2.SourceId=t3.QuotaId
WHERE t1.CreateUserId=cic.MarketingManagerId --只记录设计师本人的调整
AND t2.SpecialCode like 'Z%'
AND t2.DetailType=0
group by t1.IntentCustomerId
),
总总人工费 as (
select
IntentCustomerId,
sum(预算费用) as 预算费用
from
总预算人工费
group by IntentCustomerId
),
赠送 as(
select
t.IntentCustomerId ,
t1.JSCompreMoney 赠送金额
from dbo.Bud_Budget t
left join dbo.Bud_BudgetMaster t1 on t.BudgetId=t1.BudgetId
left join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
where t1.SpecialCode= 'HDZS'
group by t.IntentCustomerId,t1.JSCompreMoney
),
定金审核 as (
select
sum(detail.Amount) AS 定金,
max(bill.AuditDate) as 定金审核日期,
max(bill.remark) as 备注,
max(OffReason) as 冲销原因,
bill.IntentCustomerId as crm_id
FROM FI_ReceiptBill as bill
LEFT JOIN FI_ReceiptBillDetail as detail ON bill.ReceiptBillId = detail.ReceiptBillId
LEFT JOIN FI_ReceiptTemplateDetail as tem_detail ON tem_detail.TemplateDetailId = detail.TemplateDetailId
WHERE bill.Status = 2 and tem_detail.SpecialCode='dj' --and detail.Amount>=10000
GROUP BY bill.IntentCustomerId
),
实付材料金额 as (
select
bill.intentcustomerid as id,
sum(BD.Amount) as 采购金额
from FI_PaymentBill B
left join FI_PaymentBillDetail BD on b.PaymentBillId =BD.PaymentBillId
left join Mm_BillMaster bill on BD.PurchaseBillMasterId=bill.BillMasterID
where bill.orgcode='0010001' and bill.BillType in (5,6) and B.Status=2 --and (bill.BillStatus=1 or bill.BillStatus=2)--and bill.PlaceStatus=1
group by bill.intentcustomerid
),
变更人 as (
select
B.IntentCustomerId,
B.CreateUserName as 变更人
from Bud_BudgetChangeBillMaster B
--join Bud_BudgetChangeBillDetail BD on B.BillId = BD.BillId[
inner join (
select
d.IntentCustomerId,MIN(d.CreateDate) min
from Bud_BudgetChangeBillMaster d --流程日志表
GROUP BY d.IntentCustomerId )f on f.IntentCustomerId=b.IntentCustomerId
where b.CreateDate=f.min
),
活动金额 as (
select
a.intentcustomerid as id,
case
--2025.3.31活动政策
when o.ActivityId = '48' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 14000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 17000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 20000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 24000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 27000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 30000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 34000
when c1.建筑面积 >= 160 then 40000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
else 0 end
)
--2024.11.1-2025.1.01活动
when o.ActivityId = '47' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 20000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 23000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 30000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 33000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 37000
when c1.建筑面积 >= 160 then 43000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
else 0 end
)
--6.30活动
when o.ActivityId = '50' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 14000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 17000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 20000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 24000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 27000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 30000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 34000
when c1.建筑面积 >= 160 then 40000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
else 0 end
)
--10.31活动
when o.ActivityId = '51' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 14000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 17000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 20000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 24000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 27000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 30000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 34000
when c1.建筑面积 >= 160 then 40000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
else 0 end
)
--2025年11.5-2026年1.31活动
when o.ActivityId = '52' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 14000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 17000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 20000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 24000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 27000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 30000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 34000
when c1.建筑面积 >= 160 then 40000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 22000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 25000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 32000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 32000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 38000
when c1.建筑面积 >= 160 then 42000
else 0 end
)
else 0 end
)
--2024年8.15-10.20活动
when o.ActivityId = '46' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 20000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 23000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 30000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 33000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 37000
when c1.建筑面积 >= 160 then 43000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
else 0 end
)
--2024年4.15-6.30活动(延7.14)
when o.ActivityId = '45' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 20000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 23000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 30000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 33000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 37000
when c1.建筑面积 >= 160 then 43000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
else 0 end
)
--2024年1.2-3.15活动(延4.14)
when o.ActivityId = '44' then (
case
when g.TemplateName like '%A4%' then (
case
when c1.建筑面积 < 90 then 17000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 20000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 23000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 27000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 30000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 33000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 37000
when c1.建筑面积 >= 160 then 43000
else 0 end
)
when g.TemplateName like '%A6%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
when g.TemplateName like '%A8%' then (
case
when c1.建筑面积 < 90 then 20000
when c1.建筑面积 >= 90 and c1.建筑面积 < 100 then 25000
when c1.建筑面积 >= 100 and c1.建筑面积 < 110 then 28000
when c1.建筑面积 >= 110 and c1.建筑面积 < 130 then 30000
when c1.建筑面积 >= 130 and c1.建筑面积 < 140 then 35000
when c1.建筑面积 >= 140 and c1.建筑面积 < 150 then 35000
when c1.建筑面积 >= 150 and c1.建筑面积 < 160 then 41000
when c1.建筑面积 >= 160 then 45000
else 0 end
)
else 0 end
) else 0 end as 活动金额
from
Crm_VIntentCustomer a
left join 面积1 c1 on a.intentcustomerid=c1.intentcustomerid
left join Crm_CustomerActivity o on o.IntentCustomerId = a.IntentCustomerId
left join Bud_Budget g on a.intentcustomerid=g.intentcustomerid
where
a.IntentCustomerId > 0
),
特殊优惠类型 as (
select
a.intentcustomerid as id,
a.CustomerName 客户,
case
when t2.SpecialCode= 'h21' THEN t2.sourcename
END as 特殊优惠类型,
case
when t2.SpecialCode= 'h21' THEN t2.Remark
END as 备注
from Crm_VIntentCustomer a
left join Bud_Budget g on a.intentcustomerid=g.intentcustomerid
left join dbo.Bud_BudgetMaster t1 on g.BudgetId=t1.BudgetId
left join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
where t2.SpecialCode= 'h21'
and t2.DetailType = 2
),
管理费 as (
select
crm.IntentCustomerId as id,
(结算费用.合同款-isnull(结算费用.远程施工费发包,0)-isnull(结算费用.税金,0)
)*0.9*(
case
when crm.WorkerPrincipal like '%☆¹%' then 0.04
when crm.WorkerPrincipal like '%☆²%' then 0.05
when crm.WorkerPrincipal like '%☆³%' then 0.055
when crm.WorkerPrincipal not like '%☆%' then 0.05
end) as 管理费补贴
from Crm_VIntentCustomer crm
left join Crm_FormConstructionPlanTemplate X on crm.intentcustomerid=X.intentcustomerid
inner join Bud_Budget B on crm.IntentCustomerId =B.IntentCustomerId
inner join dbo.Bud_BudgetMaster C on B.BudgetId =C.BudgetId
left join Crm_FormProjectFinish F on crm.IntentCustomerId=F.IntentCustomerId
left join 结算费用 on 结算费用.IntentCustomerId=crm.intentcustomerid
where crm.OrgCode='0010001'
),
基金池入账 as (
select
crm.intentcustomerid as id,
crm.customerno as 客户编码,
crm.customername as 客户姓名,
(case when t.userid=s.sjs_userid then s.sjsjjc when t.userid=s.sw_userid then s.swjjc end) as 基金池
--isnull(s.sjsjjc,0)+isnull(s.swjjc,0) as 设计基金池,
from Form_Custom_qdjjc s
left join crm_vintentcustomer crm on crm.intentcustomerid=s.intentcustomerid
left join sys_user t on t.userid=s.sjs_userid or t.userid=s.sw_userid
where crm.orgcode=0010001
),
收款比例 as (
select
intentcustomerid,
CollectedAmount,
BalanceAmount
from
Crm_IntentCustomer
)
SELECT DISTINCT
a.customerNo as id,
a.CustomerName 客户 ,
定金审核.定金审核日期 as 定金审核日期,
e.RealBeginDate as 实际开工日期,
ff.planwarrantydate as 保修期,
p.name as 活动,
g.TemplateName as 套餐单价,
ee.createUser 审核人,
ee.createdate 审核日期,
case
when a.customertype!='软装客户'
and a.customertype!='精装客户'
and a.customertype!='厨卫翻新客户'
and a.customertype!='高端客户'
and a.customertype!='零售客户'
then '硬装客户'
else a.customertype
end as 客户类型,
a.BalanceAmount as 合同金额,
净面积.净面积 as 净面积,
c1.建筑面积 as 建筑面积,
-- 1-净面积.净面积/c1.建筑面积 as 公摊面积,
-- CONCAT(
-- ROUND(
-- (1 - (净面积.净面积 / NULLIF(c1.建筑面积,0)) * 100),
-- 2
-- ),
-- '%'
-- ) AS 公摊面积,
--
CONCAT(CAST(ROUND((1-净面积.净面积 / NULLIF(c1.建筑面积,0)) * 100, 2) AS DECIMAL(10,2)), '%') 公摊面积,
活动金额.活动金额,
活动金额.活动金额 - isnull(基金池入账.基金池,0) - isnull(ROUND(赠送.赠送金额, 4),0) + isnull (round(结算费用.活动, 4),0)- isnull(ROUND(结算费用.活动基础优惠, 4),0) as 优惠差异,
ROUND(ISNULL(管理费.管理费补贴, 0) + ISNULL(结算费用.结算直接费, 0)
+ ISNULL(结算费用.远程施工费发包, 0) + ISNULL(结算费用.主材, 0)+isnull(结算费用.小组基金,0), 4) as 总成本,
ROUND(a.BalanceAmount - (ISNULL(管理费.管理费补贴, 0) + ISNULL(结算费用.结算直接费, 0)
+ ISNULL(结算费用.远程施工费发包, 0) + ISNULL(结算费用.主材, 0)+isnull(结算费用.小组基金,0)), 4) as 利润,
ROUND((a.BalanceAmount - (isnull(结算费用.小组基金,0)+ISNULL(管理费.管理费补贴, 0)
+ ISNULL(结算费用.结算直接费, 0) + ISNULL(结算费用.远程施工费发包, 0) + ISNULL(结算费用.主材, 0)))/a.BalanceAmount, 4) as 利润百分比,
a.WorkerPrincipal as 工长,
t3.DeptName as 设计师部门,
DecorationFullAddress 地址,
marketingmanager as 设计师,
t4.username as 业务员,
t4.deptname as 业务员部门,
-- isnull(a.ExtN4,0) as 标准合同,
-- nullif(a.ExtN3,0) as 折单金额,
isnull(结算费用.预算折单优惠,0) as 折单金额,
isnull(ROUND(结算费用.质保金,4),0) as 质保金,
isnull(ROUND(已付材料款.实付金额, 4),0) as 已付主辅库,
isnull(je.采购金额,0) as 实付材料款,
isnull(ROUND(已付工程款.已付工程款, 4),0) as 已付工程款,
ROUND((it.CollectedAmount/it.BalanceAmount), 4) as 收款比例,
isnull(ROUND(管理费.管理费补贴, 4),0) as 工长管理费,
isnull(ROUND(结算费用.远程施工费发包, 4),0) as 远程施工费发包,
ROUND((管理费.管理费补贴 + ISNULL(结算费用.结算直接费, 0) + ISNULL(结算费用.远程施工费发包, 0)), 4) as 定额金额,
isnull(ROUND(结算费用.主材, 4),0) as 结算主材金额,
isnull(ROUND(结算费用.辅材, 4),0) as 结算辅材金额,
isnull(ROUND(总总人工费.预算费用, 4),0) as 预算人工金额,
isnull(ROUND(结算费用.人工, 4),0) as 结算人工金额,
isnull(ROUND(结算费用.活动基础优惠, 4),0) 活动基础优惠,
isnull(ROUND(结算费用.预算折单优惠, 4),0) 预算折单优惠,
isnull(结算费用.自带单,0) 自带单优惠,
isnull(ROUND(结算费用.自然到店优惠, 4),0) 自然到店优惠,
isnull(ROUND(结算费用.基金池基金优惠, 4),0) 基金池基金优惠,
isnull(ROUND(结算费用.特殊优惠, 4),0) 特殊优惠,
--case when t2.SpecialCode= 'h21' THEN t2.sourcename else null END as 特殊优惠类型,
特殊优惠类型.特殊优惠类型 特殊优惠类型,
特殊优惠类型.备注 备注,
---------------------
isnull(结算费用.小组基金,0) as 小组基金,
isnull(结算费用.客介优惠,0) as 客介优惠,
isnull(结算费用.优惠金额,0) as 优惠金额,
isnull(结算费用.平米优惠,0) as 平米优惠,
isnull(ROUND(赠送.赠送金额, 4),0) as 赠送金额,
isnull(结算费用.税金,0) as 税金,
ROUND(a.BalanceAmount - (ISNULL(已付材料款.实付金额, 0)+ISNULL(已付工程款.已付工程款, 0)+ISNULL(结算费用.质保金, 0)), 4) as 决算利润,
ROUND((a.BalanceAmount-(ISNULL(已付材料款.实付金额, 0)+ISNULL(已付工程款.已付工程款, 0)+ISNULL(结算费用.质保金, 0)))/a.BalanceAmount, 4) as 决算利润百分比,
---t.remark as 备注,
a.GZBalanceState as 是否工长结算,
-- isnull(a.ExtN1,0) as 折单产值,
a.BalanceAmount-(5*isnull(结算费用.预算折单优惠,0)) as 折单产值,
-- isnull(a.ExtN2,0) as 其它收入,
-- 5*结算费用.预算折单优惠 as 其他收入,
isnull(d.卫生间,0) as 卫生间,
-- 结算费用.主材个性化 as 主材个性化,
isnull(p.ActivityCost,0) as 活动成本,
isnull(基金池入账.基金池,0) as 基金池入账,
round(
1 - (ROUND(ISNULL(管理费.管理费补贴, 0) + ISNULL(结算费用.结算直接费, 0) + ISNULL(结算费用.远程施工费发包, 0)
+ ISNULL(结算费用.主材, 0)+isnull(结算费用.小组基金,0),4))
/(a.BalanceAmount - isnull(基金池入账.基金池,0)),4
) as 基金利润,
-- 2025年12月10日19:10:25 添加
(CASE WHEN t2.SpecialCode IS NOT NULL AND t2.SpecialCode = 'h45' THEN t2.SourceName END) AS 类型,
isnull((CASE WHEN t2.SpecialCode = 'h45' THEN t2.YSBudNum END),0) AS 金额
FROM Crm_VIntentCustomer a
left join Crm_FormConstructionContract e on a.intentcustomerid=e.intentcustomerid
left join Crm_FormDesignContract t on a.intentcustomerid=t.intentcustomerid
left join Crm_CustomerActivity y on a.intentcustomerid=y.intentcustomerid
left join 收款比例 it on a.intentcustomerid=it.intentcustomerid
left join Base_Activity p on y.activityid=p.id
left join Bud_Budget g on a.intentcustomerid=g.intentcustomerid
left join dbo.Bud_BudgetMaster t1 on g.BudgetId=t1.BudgetId
left join dbo.Bud_BudgetDetail t2 on t1.BudgetMasterId=t2.BudgetMasterId
left join 总总人工费 on 总总人工费.intentcustomerid = a.intentcustomerid
--left join 面积 c on a.intentcustomerid=c.intentcustomerid
left join 面积1 c1 on a.intentcustomerid=c1.intentcustomerid
left join 卫生间 d on a.intentcustomerid=d.intentcustomerid
--left join 开单 B ON a.intentcustomerid=B.intentcustomerid
left join 结算费用 on a.intentcustomerid = 结算费用.intentcustomerid
left join 赠送 on a.intentcustomerid = 赠送.intentcustomerid
left join 已付工程款 on a.intentcustomerid = crm_id
left join 已付材料款 on a.intentcustomerid = 已付材料款.IntentCustomerId
left join sys_user t3 on a.MarketingManagerId=t3.userid
left join sys_user t4 on t4.userid=a.Salesmanid
left join 定金审核 on 定金审核.crm_id=a.intentcustomerid
left join 净面积 on 净面积.id=a.intentcustomerid
left join 实付材料金额 je on je.id=a.intentcustomerid
left join 管理费 on 管理费.id=a.intentcustomerid
left join 基金池入账 on 基金池入账.id=a.intentcustomerid
--left join 变更人 on a.intentcustomerid = 变更人.IntentCustomerId
left join 特殊优惠类型 on 特殊优惠类型.id = a.intentcustomerid
left join 活动金额 on 活动金额.id = a.intentcustomerid
-- left join Base_Activity ba on a.id = o.ActivityId
left join Crm_FormProjectFinish ff on ff.IntentCustomerId = a.IntentCustomerId
inner join (
SELECT *
FROM ( SELECT b.IntentCustomerId ,
b.CreateUser ,
b.CreateDate ,
ROW_NUMBER() OVER ( PARTITION BY b.IntentCustomerId ORDER BY b.CreateDate ) px
FROM Crm_WorkflowLog b
INNER JOIN dbo.Crm_IntentCustomer cic ON b.IntentCustomerId = cic.IntentCustomerId
WHERE FormId = 9
AND LogType = '提交日志'
AND cic.MarketingManagerId <> b.CreateUserId --调整表单非设计师本人操作
) fb
WHERE fb.px = 1 ---成控部最早提交表单时间
) ee on a.intentcustomerid=ee.intentcustomerid
WHERE
convert(char(10),ee.createdate,120) between @p and @q -- 全部都在，审核过的，已经开工的都在
and(a.IsFormDead=0 or a.IsFormDead is null) and a.OrgCode='0010001'
and
(case
when a.customertype!='软装客户'
and a.customertype!='精装客户'
and a.customertype!='出租房客户'
and a.customertype!='厨卫翻新客户'
and a.customertype!='高端客户'
and a.customertype!='零售客户'
then '硬装客户'
else a.customertype
end)='硬装客户'
and a.intentcustomerid!='7249'
and g.TemplateName != '2024年精装房M6套餐'
--and g.TemplateName like '2024%'
end
输出的数据会有重复，主要是t2.SpecialCode=H45输出一个结果，t2.SpecialCode是NULL时候也会出现一个结果，导致重复，怎么使当t2.SpecialCode=h45时输出45的相关数据，若没有t2.SpecialCode=h45这个情况，则输出t2.SpecialCode=NULL的相关数据
最新发布
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
