SQL存储过程创建临时表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[Mike_1]
as
create table #tmp(
tmCompany varchar(50),
tmCategoryName varchar(50),
tmAmount money,
tmUSDAmount money,
tmPrecent varchar(10),
tmCompanyID int,
tmCategoryID int,
tmTotalCompany money,
tmOrder int
)
--Insert data
insert #tmp(tmCompanyID,tmCategoryID,tmAmount,tmUSDAmount,tmOrder)
select tpCompany,tpCategory,sum(tpTotalAmount),sum(tpTotalAmountUSD),1
from Test_PO
group by tpCompany,tpCategory

--Got Company Data

Create table #Sum(
suCompanyID int,
suTotal money
)
insert #Sum(suCompanyID,suTotal)
select tmCompanyID,sum(tmAmount)
from #tmp
group by tmCompanyID
--select * From #Sum

update #tmp set tmTotalCompany=suTotal from #Sum where tmCompanyID=suCompanyID

--Update Precent
update #tmp set tmPrecent=convert(varchar(10),tmAmount/tmTotalCompany*100)+'%'

--Insert Sum Data
insert #tmp(tmCompanyID,tmCategoryName,tmAmount,tmUsdAmount,tmOrder,tmPrecent)
select tmCompanyID,'合计',sum(tmAmount),sum(tmUSDAmount),2,100
from #tmp
group by tmCompanyID

--update Company Name
update #tmp set tmCompany=cpAlterName from Test_Company where cpID=tmCompanyID
--Update Category Name
update #tmp set tmCategoryName=ctName from Test_category where ctID=tmCategoryID
select * from #tmp order by tmCompanyID,tmOrder desc


=======================================================================================


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[Mike_2]
as
create table #tmp(
tmCompany varchar(50),
tmDepartName varchar(50),
tmAmount money,
tmUSDAmount money,
tmPrecent float,
tmCompanyID int,
tmDepartID int,
tmTotalCompany money,
tmOrder int
)
--Insert data
insert #tmp(tmCompanyID,tmDepartID,tmAmount,tmUSDAmount,tmOrder)
select tpCompany,usDepartment,sum(tpTotalAmount),sum(tpTotalAmountUSD),1
from Test_PO,Test_User where tpApplicant=usID
group by tpCompany,usDepartment

--Got Company Data
Create table #Sum(
suCompanyID int,
suTotal money
)
insert #Sum(suCompanyID,suTotal)
select tmCompanyID,sum(tmAmount)
from #tmp
group by tmCompanyID

update #tmp set tmTotalCompany=suTotal from #Sum where tmCompanyID=suCompanyID

--Update Precent
update #tmp set tmPrecent=tmAmount/tmTotalCompany*100

--Insert Sum Data
insert #tmp(tmCompanyID,tmDepartName,tmAmount,tmUsdAmount,tmOrder,tmPrecent)
select tmCompanyID,'合计',sum(tmAmount),sum(tmUSDAmount),2,100
from #tmp
group by tmCompanyID

--update Company Name
update #tmp set tmCompany=cpAlterName from Test_Company where cpID=tmCompanyID
--Update Depart Name
update #tmp set tmDepartName=dpName from Test_Department where dpID=tmDepartID

select * from #tmp order by tmCompanyID,tmOrder

go
exec Mike_2
====================================================================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[mike_3]
as
create table #tmp(
tmCompany varchar(50),
tmDepartName varchar(100),
tmCostCenterName varchar(100),
tmAmount money,
tmUSDAmount money,
tmCompanyID int,
tmDepartID int,
tmCostCenterID int,
tmOrder int
)
--Insert data
insert #tmp(tmCompanyID,tmDepartID,tmCostCenterID,tmAmount,tmUSDAmount,tmOrder)
select tpCompany,usDepartment,tpCostCenterID,sum(tpTotalAmount),sum(tpTotalAmountUSD),1
from Test_PO,Test_User
where tpApplicant=usID and tpCostCenterID<>0
group by tpCompany,usDepartment,tpCostCenterID

insert #tmp(tmCompanyID,tmDepartID,tmCostCenterID,tmAmount,tmUSDAmount,tmOrder)
select tpCompany,usDepartment,tpCPAID,sum(tpTotalAmount),sum(tpTotalAmountUSD),1
from Test_PO,Test_User
where tpApplicant=usID and tpCPAID<>0
group by tpCompany,usDepartment,tpCPAID

select * from #tmp
--Insert Department Sum Data
insert #tmp(tmCompanyID,tmDepartID,tmCostCenterName,tmAmount,tmUsdAmount,tmOrder)
select tmCompanyID,tmDepartID,'合计',sum(tmAmount),sum(tmUSDAmount),2
from #tmp
group by tmCompanyID,tmDepartID

--Insert Sum Data
insert #tmp(tmCompanyID,tmDepartID,tmCostCenterName,tmAmount,tmUsdAmount,tmOrder)
select tmCompanyID,sum(tmDepartID),'公司总计',sum(tmAmount),sum(tmUSDAmount),3
from #tmp
group by tmCompanyID

--update Company Name
update #tmp set tmCompany=cpAlterName from Test_Company where cpID=tmCompanyID

--Update Department Name
update #tmp set tmDepartName=dpName from Test_Department where dpID=tmDepartID

--Update CostCenter Name
update #tmp set tmCostCenterName=AccountCodeDesc from Test_AccountCode where ACID=tmCostCenterID

--Insert Total Sum
insert #tmp(tmCompanyID,tmCompany,tmAmount,tmUSDAmount)
select sum(tmCompanyID),'所有总计',sum(tmAmount),sum(tmUSDAmount)
from #tmp
where tmCostCenterName='公司总计'

select * from #tmp order by tmCompanyID,tmDepartID,tmOrder


=================================================================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[mike_4]
as
create table #tmp(
tmCompany varchar(50),
tmCategoryName varchar(50),
tmItemCode varchar(30),
tmItemName varchar(200),
tmAmount money,
tmUSDAmount money,
tmPrecent float,
tmCompanyID int,
tmCategoryID int,
tmItemID int,
tmTotalCompany money,
tmOrder int
)
--Insert data
insert #tmp(tmCompanyID,tmCategoryID,tmItemID,tmAmount,tmUSDAmount,tmOrder)
select tpCompany,tpCategory,rdItem,sum(tpTotalAmount),sum(tpTotalAmount*8),1
from Test_

PO,Test_Detail
where tpCode=rdPR
group by tpCompany,tpCategory,rdItem

--Got Company Data
Create table #Sum(
suCompanyID int,
suTotal money
)
insert #Sum(suCompanyID,suTotal)
select tmCompanyID,sum(tmAmount)
from #tmp
group by tmCompanyID

update #tmp set tmTotalCompany=suTotal from #Sum where tmCompanyID=suCompanyID

--Update Precent
update #tmp set tmPrecent=tmAmount/tmTotalCompany*100

--Insert Category Sum Data
insert #tmp(tmCompanyID,tmCategoryID,tmItemName,tmAmount,tmUsdAmount,tmOrder,tmPrecent)
select tmCompanyID,tmCategoryID,'Category合计',sum(tmAmount),sum(tmUSDAmount),2,100
from #tmp
group by tmCompanyID,tmCategoryID

--Insert Company Sum Data
insert #tmp(tmCompanyID,tmCategoryName,tmAmount,tmUsdAmount,tmOrder,tmPrecent,tmCategoryID)
select tmCompanyID,'合计',sum(tmAmount),sum(tmUSDAmount),3,100,sum(tmCategoryID)
from #tmp
group by tmCompanyID

--update Company Name
update #tmp set tmCompany=cpAlterName from Test_Company where cpID=tmCompanyID
--Update Category Name
update #tmp set tmCategoryName=ctName from Test_category where ctID=tmCategoryID
--Update Item Name
update #tmp set tmitemCode=itName,tmItemName=itDescription from Test_Item where tmItemID=itID

--Insert Total Sum
insert #tmp(tmCompanyID,tmCompany,tmAmount,tmUSDAmount)
select sum(tmCompanyID),'所有总计',sum(tmAmount),sum(tmUSDAmount)
from #tmp
where tmCategoryName='合计'

select * from #tmp order by tmCompanyID,tmCategoryID,tmOrder


==========================================================================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Procedure [dbo].[mike_5]
as
create table #tmp(
tmCompany varchar(50),
tmCategoryName varchar(50),
tmItemCode varchar(30),
tmItemName varchar(200),
tmAmount money,
tmUSDAmount money,
tmPrecent float,
tmCompanyID int,
tmCategoryID int,
tmItemID int,
tmTotalCompany money,
tmOrder int,
tmCount int
)
--Insert data
insert #tmp(tmCompanyID,tmCategoryID,tmItemID,tmAmount,tmUSDAmount,tmOrder)
select tpCompany,tpCategory,rdItem,sum(tpTotalAmount),sum(tpTotalAmount*8),2
from Test_PO,Test_Detail
where tpCode=rdPR
group by tpCompany,tpCategory,rdItem

--Got Company Data
Create table #Sum(
suCompanyID int,
suTotal money,
suCOunt int
)
insert #Sum(suCompanyID,suTotal,suCount)
select tmCompanyID,sum(tmAmount),count(*)
from #tmp
group by tmCompanyID

update #tmp set tmTotalCompany=suTotal,tmCOunt=suCount from #Sum where tmCompanyID=suCompanyID

--Update Precent
update #tmp set tmPrecent=tmAmount/tmTotalCompany*100

--Insert Category Sum Data
insert #tmp(tmCompanyID,tmCategoryID,tmItemName,tmAmount,tmUsdAmount,tmOrder,tmPrecent)
select tmCompanyID,tmCategoryID,'Category合计',

sum(tmAmount),sum(tmUSDAmount),0,100
from #tmp
group by tmCompanyID,tmCategoryID

--Insert Company Sum Data
insert #tmp(tmCompanyID,tmCategoryName,tmAmount,tmUsdAmount,tmOrder,tmPrecent,tmCategoryID)
select tmCompanyID,'合计',sum(tmAmount),sum(tmUSDAmount),1,100,sum(tmCategoryID)
from #tmp
group by tmCompanyID

--update Company Name
update #tmp set tmCompany=cpAlterName from Test_Company where cpID=tmCompanyID
--Update Category Name
update #tmp set tmCategoryName=ctName from Test_category where ctID=tmCategoryID
--Update Item Name
update #tmp set tmitemCode=itName,tmItemName=itDescription from Test_Item where tmItemID=itID
select * from #tmp order by tmCompanyID,tmCategoryID,tmOrder