1. 子表-----计划收入
select CollectionPlanID as 收入编号,CollectionTime as 计划收入时间,collectionMoney 计划收入金额,contractid 合同编号
from T_Bas_CollectionPlan
2.子表-----实际收入
select collectionid as 收入编号,collectiontime as 实际收入时间,collectionMoney 实际收入金额,contractid 合同编号
from T_Bas_Collection
3.合并后的纵表--------收入纵表
SELECT ContractID 合同编号, '计划收入' as 收入类型, SUM(CollectionMoney) AS 收入金额,year(CollectionTime) as 年份,month(CollectionTime) as 月份
FROM dbo.T_Bas_CollectionPlan
GROUP BY ContractID,year(CollectionTime),month(CollectionTime)
union
SELECT ContractID AS 合同编号, '实际收入' as 收入类型,SUM(CollectionMoney) AS 收入金额, year(CollectionTime) as 年份,month(CollectionTime) as 月份
FROM dbo.T_Bas_Collection
GROUP BY ContractID,year(CollectionTime),month(CollectionTime)
4.转换后的横表---------收入横表
USE contract
GO
/****** 对象: StoredProcedure [dbo].[GetContractBudget] 脚本日期: 05/14/2008 15:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetContractBudget]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--存放收入数据包括:计划收入和实际收入的纵表格式
create table #tmp1(contractid int,acollection nvarchar(50),collectionMoney decimal(16,2) ,ayear int , amonth int)
insert into #tmp1(contractid,acollection,collectionMoney,ayear,amonth)
SELECT ContractID, '计划收入' as acollection, SUM(CollectionMoney) AS CollectionPlan,
year(CollectionTime) as collectionPlanYear,
month(CollectionTime) as collectionPlanMonth
FROM dbo.T_Bas_CollectionPlan
GROUP BY ContractID,year(CollectionTime),month(CollectionTime)
union
SELECT ContractID AS ContractNo, '实际收入' as acollection,SUM(CollectionMoney) AS sumCollection,
year(CollectionTime) as CollectionRealYear,
month(CollectionTime) as CollectionRealMonth
FROM dbo.T_Bas_Collection
GROUP BY ContractID,year(CollectionTime),month(CollectionTime)
--select * from #tmp1
--存放--存放收入数据包括:计划收入和实际收入的横表格式
create table #result1(contractID int,ayear int,amonth int,collectionPlan decimal(16,2),collectionReal decimal(16,2))
insert into #result1
select contractid,ayear,amonth, sum(case acollection when '计划收入' then collectionMoney else 0 end) as 'a计划收入',
sum(case acollection when '实际收入' then collectionMoney else 0 end) as 'a实际收入'
from #tmp1
group by contractid,ayear,amonth
select contractid 合同编号,ayear 年份,amonth 月份,collectionPlan 计划收入金额,collectionReal 实际收入金额from #result1
drop table #tmp1
drop table #result1
END