select
*,
'同比' as TB,
'实际占比' as SJZB,
'预算占比' as BGZB
from
(select
'it' as code,
t1.*,
case when t2.amountI = 0 then null else t1.amount / t2.amountI end as amountIT
from
(select
base.Item_Code,
base.Primary_Department_Code as department,
base.Primary_Department as departmentName,
base.Account as AccountT,
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
and base.Secondary_Department_Code = MainSheet.部门
where
base.Account in ('Research_Expense','Sell_Expense','Admini_Cost')
and 年结JE <> 'T'
and base.Primary_Department not in('八大区','服务部')
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Account,
base.Primary_Department_Code,
base.Item_Code,
account.Account_Attribute,
account.Account_AttributeName,
base.Primary_Department)t1
left join
(select
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) * base.Mark as amountI
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
where
base.Item_Code = '6001'
and account.Account_Mark = 'Inco_Main'
and 年结JE <> 'T'
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Mark)t2
on
t1.yearT = t2.yearT
and t1.monthT = t2.monthT
union all
select
'yoy' as code,
t1.*,
case when t2.amount = 0 then null else (t1.amount - t2.amount) / ABS(t2.amount)end as YOY
from
(select
base.Item_Code,
base.Primary_Department_Code as department,
base.Primary_Department as departmentName,
base.Account as AccountT,
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
and base.Secondary_Department_Code = MainSheet.部门
where
base.Account in ('Research_Expense','Sell_Expense','Admini_Cost')
and 年结JE <> 'T'
and base.Primary_Department not in('八大区','服务部')
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Account,
base.Primary_Department_Code,
base.Item_Code,
account.Account_Attribute,
account.Account_AttributeName,
base.Primary_Department)t1
left join
(select
base.Item_Code,
base.Primary_Department_Code as department,
base.Primary_Department as departmentName,
base.Account as AccountT,
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
and base.Secondary_Department_Code = MainSheet.部门
where
base.Account in ('Research_Expense','Sell_Expense','Admini_Cost')
and 年结JE <> 'T'
and base.Primary_Department not in('八大区','服务部')
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Account,
base.Primary_Department_Code,
base.Item_Code,
account.Account_Attribute,
account.Account_AttributeName,
base.Primary_Department)t2
on
t1.yearT - 1 = t2.yearT
and t1.monthT = t2.monthT
and t1.department = t2.department
and t1.Account_Attribute = t2.Account_Attribute
union all
select
'bg' as code,
t1.*,
case when t2.amount = 0 then null else t1.amount / t2.amount end as amountBT
from
(select
Budget_code,
Budget_Department_Code as department,
Budget_Department as departmentName,
Budget_Account as AccountT,
YEAR(MainSheet.Budget_Time) as yearT,
MONTH(MainSheet.Budget_Time) as monthT,
SUM(MainSheet.Budget_Amount) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[finance_Budget_Sub] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.Budget_Account_Code = account.Account_Code
where
Budget_Account in ('Research_Expense','Sell_Expense','Admini_Cost')
and Budget_Department_Code not in('107','108','109','110','111','112','113','114','115','116','117','118','124')
group by
YEAR(MainSheet.Budget_Time),
MONTH(MainSheet.Budget_Time),
Budget_Account,
Budget_Department_Code,
Budget_code,
account.Account_Attribute,
account.Account_AttributeName,
Budget_Department)t1
left join
(select
YEAR(Budget_Time) as yearT,
MONTH(Budget_Time) as monthT,
SUM(Budget_Amount) as amount
from
[dbo].[finance_Budget_Sub]
where
Budget_code = '6001'
group by
YEAR(Budget_Time),
MONTH(Budget_Time))t2
on
t1.yearT = t2.yearT
and t1.monthT = t2.monthT
union all ------------------------------服务部、八大区
select
'it' as code,
t1.*,
case when t2.amountI = 0 then null else t1.amount / t2.amountI end as amountIT
from
(select
base.Item_Code,
base.Secondary_Department_Name as department,
base.Primary_Department as departmentName,
base.Account as AccountT,
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
and base.Secondary_Department_Code = MainSheet.部门
where
base.Account = 'Sell_Expense'
and 年结JE <> 'T'
and base.Primary_Department in ('八大区','服务部')
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Account,
base.Secondary_Department_Name,
base.Item_Code,
account.Account_Attribute,
account.Account_AttributeName,
base.Primary_Department)t1
left join
(select
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) * base.Mark as amountI
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
where
base.Item_Code = '6001'
and account.Account_Mark = 'Inco_Main'
and 年结JE <> 'T'
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Mark)t2
on
t1.yearT = t2.yearT
and t1.monthT = t2.monthT
union all
select
'yoy' as code,
t1.*,
case when t2.amount = 0 then null else (t1.amount - t2.amount) / ABS(t2.amount) end as YOY
from
(select
base.Item_Code,
base.Secondary_Department_Name as department,
base.Primary_Department as departmentName,
base.Account as AccountT,
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
and base.Secondary_Department_Code = MainSheet.部门
where
base.Account = 'Sell_Expense'
and 年结JE <> 'T'
and base.Primary_Department in ('八大区','服务部')
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Account,
base.Secondary_Department_Name,
base.Item_Code,
account.Account_Attribute,
account.Account_AttributeName,
base.Primary_Department)t1
left join
(select
base.Item_Code,
base.Secondary_Department_Name as department,
base.Primary_Department as departmentName,
base.Account as AccountT,
YEAR(MainSheet.日期) as yearT,
MONTH(MainSheet.日期) as monthT,
SUM(MainSheet.金额) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[序时账] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.科目编码 = account.Account_Code
left join
[dbo].[sys_finance_base] base
on
account.Base_Code = base.Item_Code
and account.Account_Mark = base.Account
and base.Secondary_Department_Code = MainSheet.部门
where
base.Account = 'Sell_Expense'
and 年结JE <> 'T'
and base.Primary_Department in ('八大区','服务部')
group by
YEAR(MainSheet.日期),
MONTH(MainSheet.日期),
base.Account,
base.Secondary_Department_Name,
base.Item_Code,
account.Account_Attribute,
account.Account_AttributeName,
base.Primary_Department)t2
on
t1.yearT - 1 = t2.yearT
and t1.monthT = t2.monthT
and t1.department = t2.department
and t1.Account_Attribute = t2.Account_Attribute
union all
select
'bg' as code,
t1.*,
case when t2.amount = 0 then null else t1.amount / t2.amount end as amountBT
from
(select
Budget_code,
Budget_Department as department,
CAST('八大区' as varchar) as departmentName,
Budget_Account as AccountT,
YEAR(MainSheet.Budget_Time) as yearT,
MONTH(MainSheet.Budget_Time) as monthT,
SUM(MainSheet.Budget_Amount) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[finance_Budget_Sub] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.Budget_Account_Code = account.Account_Code
where
Budget_Account = 'Sell_Expense'
and Budget_Department_Code in ('107','108','109','110','111','112','113','114')
group by
YEAR(MainSheet.Budget_Time),
MONTH(MainSheet.Budget_Time),
Budget_Account,
Budget_Department_Code,
Budget_code,
account.Account_Attribute,
account.Account_AttributeName,
Budget_Department)t1
left join
(select
YEAR(Budget_Time) as yearT,
MONTH(Budget_Time) as monthT,
SUM(Budget_Amount) as amount
from
[dbo].[finance_Budget_Sub]
where
Budget_code = '6001'
group by
YEAR(Budget_Time),
MONTH(Budget_Time))t2
on
t1.yearT = t2.yearT
and t1.monthT = t2.monthT
union all
select
'bg' as code,
t1.*,
case when t2.amount = 0 then null else t1.amount / t2.amount end as amountBT
from
(select
Budget_code,
Budget_Department as department,
CAST('服务部' as varchar) as departmentName,
Budget_Account as AccountT,
YEAR(MainSheet.Budget_Time) as yearT,
MONTH(MainSheet.Budget_Time) as monthT,
SUM(MainSheet.Budget_Amount) as amount,
account.Account_Attribute as Account_Attribute,
account.Account_AttributeName as NameT
from
[dbo].[finance_Budget_Sub] MainSheet
left join
[dbo].[sys_finance_account] account
on
MainSheet.Budget_Account_Code = account.Account_Code
where
Budget_Account = 'Sell_Expense'
and Budget_Department_Code in ('115','116','117','118','124')
group by
YEAR(MainSheet.Budget_Time),
MONTH(MainSheet.Budget_Time),
Budget_Account,
Budget_Department_Code,
Budget_code,
account.Account_Attribute,
account.Account_AttributeName,
Budget_Department)t1
left join
(select
YEAR(Budget_Time) as yearT,
MONTH(Budget_Time) as monthT,
SUM(Budget_Amount) as amount
from
[dbo].[finance_Budget_Sub]
where
Budget_code = '6001'
group by
YEAR(Budget_Time),
MONTH(Budget_Time))t2
on
t1.yearT = t2.yearT
and t1.monthT = t2.monthT)A1 这段sql报以上错误，在哪里发生隐式转换找出来
扫一扫
1422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
