ALTER FUNCTION [dbo].[get_ov_one]
(
@emp_code varchar(10),@selected_period varchar(10)
)
RETURNS decimal(18,2)
AS
BEGINdeclare @st_date datetime,@en_date datetime,@r_dec decimal(18,2),@pclass_code varchar(20),@salary_type varchar(20)select @emp_code = rtrim(@emp_code),@selected_period = rtrim(@selected_period) if @emp_code is null or @emp_code = '' or @selected_period is null or @selected_period = ''
return 0select @st_date = from_date,@en_date = to_date from t_period(index = pk_t_period) where period = @selected_periodselect @pclass_code = pclass_code,@salary_type = salary_type from t_emp(index = pk_t_emp) where emp_code = @emp_code if @salary_type is null or @salary_type <> '5'
return 0 if (select count(*) from t_pro_table where pclass_code = @pclass_code) = 0
return 0declare @t table (a$code varchar(20) ,adjust_date datetime,b$ref3 varchar(20),b$ref4 varchar(20),primary key (a$code,adjust_date))
insert @t
select a$code ,adjust_date ,b$ref3 ,b$ref4
from aud$staff
where
adjust_date between cast(convert(char(8),@st_date,112) as datetime) and cast(convert(char(8),@en_date,112) + ' 23:59' as datetime)
select
@r_dec = sum(isnull(prod_qty,0) * isnull(job_price,0) * isnull(cft,0))
from erp_piece a(index = ERP_PIECE_idx2)
join t_emp b(index = pk_t_emp) on
emp_code = staff_code
left join @t on
adjust_date = (select min(adjust_date) from @t where emp_code = a$code and prod_date <= adjust_date and prod_date = a.prod_date)
and emp_code = a$code
join t_pro_table d on
case pclass_type
when '1' then-------层1
case when
d.dept_code =
case when adjust_date is null then b.dept_code
else b$ref3 end
then 1
else 0 end
when '2' then-------层1
case when
d.dept_code = case when adjust_date is null then b.dept_code
else b$ref3 end
and d.group_code = case when adjust_date is null then b.team_code
else b$ref4 end
then 1
else 0 end
when '3' then-------层1
case when
d.dept_code = case when adjust_date is null then b.dept_code
else b$ref3 end
and d.group_code = case when adjust_date is null then b.team_code
else b$ref4 end
and d.duty_code = b.duty_code
then 1
else 0 end
else 0 end-------层1
= 1
where
prod_date between cast(convert(char(8),@st_date,112) as datetime) and cast(convert(char(8),@en_date,112) + ' 23:59' as datetime)
and d.pclass_code = @pclass_code
return @r_dec
END
(
@emp_code varchar(10),@selected_period varchar(10)
)
RETURNS decimal(18,2)
AS
BEGINdeclare @st_date datetime,@en_date datetime,@r_dec decimal(18,2),@pclass_code varchar(20),@salary_type varchar(20)select @emp_code = rtrim(@emp_code),@selected_period = rtrim(@selected_period) if @emp_code is null or @emp_code = '' or @selected_period is null or @selected_period = ''
return 0select @st_date = from_date,@en_date = to_date from t_period(index = pk_t_period) where period = @selected_periodselect @pclass_code = pclass_code,@salary_type = salary_type from t_emp(index = pk_t_emp) where emp_code = @emp_code if @salary_type is null or @salary_type <> '5'
return 0 if (select count(*) from t_pro_table where pclass_code = @pclass_code) = 0
return 0declare @t table (a$code varchar(20) ,adjust_date datetime,b$ref3 varchar(20),b$ref4 varchar(20),primary key (a$code,adjust_date))
insert @t
select a$code ,adjust_date ,b$ref3 ,b$ref4
from aud$staff
where
adjust_date between cast(convert(char(8),@st_date,112) as datetime) and cast(convert(char(8),@en_date,112) + ' 23:59' as datetime)
select
@r_dec = sum(isnull(prod_qty,0) * isnull(job_price,0) * isnull(cft,0))
from erp_piece a(index = ERP_PIECE_idx2)
join t_emp b(index = pk_t_emp) on
emp_code = staff_code
left join @t on
adjust_date = (select min(adjust_date) from @t where emp_code = a$code and prod_date <= adjust_date and prod_date = a.prod_date)
and emp_code = a$code
join t_pro_table d on
case pclass_type
when '1' then-------层1
case when
d.dept_code =
case when adjust_date is null then b.dept_code
else b$ref3 end
then 1
else 0 end
when '2' then-------层1
case when
d.dept_code = case when adjust_date is null then b.dept_code
else b$ref3 end
and d.group_code = case when adjust_date is null then b.team_code
else b$ref4 end
then 1
else 0 end
when '3' then-------层1
case when
d.dept_code = case when adjust_date is null then b.dept_code
else b$ref3 end
and d.group_code = case when adjust_date is null then b.team_code
else b$ref4 end
and d.duty_code = b.duty_code
then 1
else 0 end
else 0 end-------层1
= 1
where
prod_date between cast(convert(char(8),@st_date,112) as datetime) and cast(convert(char(8),@en_date,112) + ' 23:59' as datetime)
and d.pclass_code = @pclass_code
return @r_dec
END
http://topic.csdn.net/u/20080826/16/7d629871-e05f-41cb-9f71-7776d1102981.html