各种报表.sql

水费管理系统

/*
	功能：售水量月报表
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_sale_month_rep')
	drop proc wsp_sale_month_rep
go

create proc wsp_sale_month_rep(
	@date datetime )
as 

declare @date_tmp varchar(20)
set @date_tmp = left(convert(varchar(20), @date, 112), len(convert(varchar(20), @date, 112)) - 2)

select fee_std_name, sum(case [month] when @date_tmp then water_amt else 0.00 end) this_month, 
		sum(water_amt) this_year
	from v_cw_amt_month
	where month between left(@date_tmp, len(@date_tmp) - 2) + '01' and @date_tmp
	group by fee_std_name
go

--wsp_sale_month_rep '20040801'