USE [BLT]
GO
/****** Object: StoredProcedure [dbo].[getLiveCustomerAccount] Script Date: 04/15/2013 09:24:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
获取入住酒店宾客消费明细账单信息
*/
ALTER proc [dbo].[getLiveCustomerAccount]
(@zdh varchar(13))
as
create table #AccountTemp
(AccountId varchar(13),
moneyOfEating float,
moneyOfKtv float,
moneyOfBall float,
moneyOfSwim float,
moneyOfShower float,
other float
)
insert into #AccountTemp values(@zdh,0,0,0,0,0,0);
declare @liveId varchar(13);
declare @point varchar(10);
declare @moneyOfBall float;
declare @moneyOfKtv float;
declare @moneyOfEating float;
declare @moneyOfSwim float;
declare @moneyOfShower float;
declare @moneyOfOther float;
select top 1 @liveId = t.ClientM_liveId ,
@point = t.ClientM_point,
@moneyOfBall = sum(t.ClientM_money)
from BLT.dbo.Client_mix t
where t.ClientM_point='保龄球' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point
select top 1 @liveId = t.ClientM_liveId ,
@point = t.ClientM_point,
@moneyOfKtv = sum(t.ClientM_money)
from BLT.dbo.Client_mix t
where t.ClientM_point='KTV' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point
select top 1 @liveId = t.ClientM_liveId ,
@point = t.ClientM_point,
@moneyOfEating = sum(t.ClientM_money)
from BLT.dbo.Client_mix t
where t.ClientM_point='餐饮' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point
select top 1 @liveId = t.ClientM_liveId ,
@point = t.ClientM_point,
@moneyOfSwim = sum(t.ClientM_money)
from BLT.dbo.Client_mix t
where t.ClientM_point='游泳' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point
select top 1 @liveId = t.ClientM_liveId ,
@point = t.ClientM_point,
@moneyOfShower = sum(t.ClientM_money)
from BLT.dbo.Client_mix t
where t.ClientM_point='洗浴' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point
select top 1 @liveId = t.ClientM_liveId ,
@point = t.ClientM_point,
@moneyOfOther = sum(t.ClientM_money)
from BLT.dbo.Client_mix t
where t.ClientM_point='其他' and t.ClientM_liveId=@zdh
group by t.ClientM_liveId,t.ClientM_point
update #AccountTemp
set AccountId = @liveId,
moneyOfBall = @moneyOfBall,
moneyOfEating = @moneyOfEating,
moneyOfKtv = @moneyOfKtv,
moneyOfSwim = @moneyOfSwim,
moneyOfShower = @moneyOfShower,
other = @moneyOfOther
where AccountId = @zdh;
select * from #AccountTemp
