各种视图.sql

水费管理系统

/*
	功能: 水表位置视图
*/

if exists(select * from sysobjects where type='V' and name = 'v_wm_pos')
	drop view v_wm_pos
go

create view v_wm_pos
as
select code_name wm_pos, standard_code pos_code, code_level, seq_nbr from code_name where code_type = 'WM_POS'
go

/*
	功能: 银行视图
*/
if exists(select * from sysobjects where type='V' and name = 'v_bank')
	drop view v_bank
go

create view v_bank
as
select code_name bank_name, standard_code bank_code, code_level, seq_nbr from code_name where code_type = 'BANK'
go

/*
	功能: 抄表单视图
*/
if exists(select * from sysobjects where type='V' and name = 'v_read_sheet')
	drop view v_read_sheet
go

create view v_read_sheet
as
select 
	rs.id,
	cust_code,
	wa.water_area_code,
	wa.water_area_name,
	cust_name,
	address,
	phone,
	p.wm_pos,
	fee_std_code,
	last_value,
	this_value,
	this_amt,
	price,
	fee_amt,
	print_flg,
	tc_code,
	read_time,
	input_flg
 from read_sheet rs
	left join water_area wa on rs.water_area_code = wa.water_area_code
	left join v_wm_pos p on rs.pos_code = p.pos_code
go

/*
	功能: 费用单价视图
*/
if exists(select * from sysobjects where type='V' and name = 'v_fee_price')
	drop view v_fee_price
go

create view v_fee_price 
as 
	select f.fee_std_code, f.unit_price 
		from fee_std_price f, (select fee_std_code, max(begin_time) begin_time from fee_std_price group by fee_std_code) t
		where f.begin_time = t.begin_time and f.fee_std_code = t.fee_std_code

go

/*
	功能: 用水量视图
*/
if exists(select * from sysobjects where type='V' and name = 'v_cw_amt')
	drop view v_cw_amt
go

create view v_cw_amt 
as 

select f.fee_std_code, f.fee_std_name, amt.water_amt, amt.read_time
	from fee_std f left join  water_meter wm on f.fee_std_code = wm.fee_std_code join cw_amt amt on amt.wm_code = wm.wm_code

go

/*
	功能：按月统计的用水量视图
*/
if exists(select * from sysobjects where type='V' and name = 'v_cw_amt_month')
	drop view v_cw_amt_month
go

create view v_cw_amt_month 
as 

select left(convert(varchar(20), read_time, 112), len(convert(varchar(20), read_time, 112)) - 2) month, fee_std_name, sum(water_amt) water_amt 
	from v_cw_amt 
	group by left(convert(varchar(20), read_time, 112), len(convert(varchar(20), read_time, 112)) - 2), fee_std_name

go