原表:FeeItem(钱Debit,时间HotelDate,编号TransCode)
目标表:transcodeamount(hoteldate,T1,T2,T3,T4,T5,T6)
要求:从FeeItem表中统计某天(HotelDate)编号为100-105钱的总和,再插入transcodeamount表。
Declare @hoteldate datetime
Declare @T1 money
Declare @T2 money
Declare @T3 money
Declare @T4 money
Declare @T5 money
Declare @T6 moneySET @hoteldate='2008-07-06'select @T1=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=100
select @T2=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=101
select @T3=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=102
select @T4=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=103
select @T5=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=104
select @T6=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=105Print CAST ( @T1 AS VARCHAR(10) )
Print CAST ( @T2 AS VARCHAR(10) )
Print CAST ( @T3 AS VARCHAR(10) )
Print CAST ( @T4 AS VARCHAR(10) )
Print CAST ( @T5 AS VARCHAR(10) )
Print CAST ( @T6 AS VARCHAR(10) )insert into transcodeamount VALUES(@hoteldate,@T1,@T2,@T3,@T4,@T5,@T6)
目标表:transcodeamount(hoteldate,T1,T2,T3,T4,T5,T6)
要求:从FeeItem表中统计某天(HotelDate)编号为100-105钱的总和,再插入transcodeamount表。
Declare @hoteldate datetime
Declare @T1 money
Declare @T2 money
Declare @T3 money
Declare @T4 money
Declare @T5 money
Declare @T6 moneySET @hoteldate='2008-07-06'select @T1=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=100
select @T2=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=101
select @T3=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=102
select @T4=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=103
select @T5=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=104
select @T6=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=105Print CAST ( @T1 AS VARCHAR(10) )
Print CAST ( @T2 AS VARCHAR(10) )
Print CAST ( @T3 AS VARCHAR(10) )
Print CAST ( @T4 AS VARCHAR(10) )
Print CAST ( @T5 AS VARCHAR(10) )
Print CAST ( @T6 AS VARCHAR(10) )insert into transcodeamount VALUES(@hoteldate,@T1,@T2,@T3,@T4,@T5,@T6)
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=100,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=101,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=102,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=103,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=104,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=105
)
SET @hoteldate='2008-07-06' exec(
'insert into transcodeamount VALUES( '+@hoteldate+' ,
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+' and TransCode=100,
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+' and TransCode=101,
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+' and TransCode=102,
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+' and TransCode=103,
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+' and TransCode=104,
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+' and TransCode=105
')
Select
Max(Case TransCode When 100 Then T Else 0 End) As T1,
Max(Case TransCode When 101 Then T Else 0 End) As T2,
Max(Case TransCode When 102 Then T Else 0 End) As T3,
Max(Case TransCode When 103 Then T Else 0 End) As T4,
Max(Case TransCode When 104 Then T Else 0 End) As T5,
Max(Case TransCode When 105 Then T Else 0 End) As T6
From
(Select TransCode,sum(Debit) As T
From FeeItem
Where HotelDate=@hoteldate
And TransCode Between 100 And 105
Group By TransCode
) As a