ALTER FUNCTION CountOneSimsPlan(@plan_id int,@plbgn char(10),@plend char(10),@intBureauID int)
RETURNS @CountOneSimsPlan TABLE
(
planed int,
complete int
)
AS
BEGIN
declare @ctemp2 char(10),@ctemp1 char(10),@detail_type int,@Foreign_id int,@foreign_type int--,@pen_id char(10)
declare @temp table
(
button_id char(12),
nd_button char(12),
pen_id char(10)
)
select @detail_type = detail_type, @foreign_type = foreign_type, @foreign_id = foreign_id from [plan] where id = @plan_id and bureau_id = @intBureauID and del_tag = 0
select @ctemp1=dbo.datetochar(@plbgn)
select @ctemp2=dbo.datetochar(@plend+' 23:59')
if (@Detail_type=0)
begin
insert @CountOneSimsPlan
values(0,0)
end
else if (@Detail_type=1)--此时计划编号失去作用,故函数没有调用它
begin
insert @temp
select dev.xinxiniu,nd.button_id,nd.pen_id
FROM dbo.team_dev td INNER JOIN dev on td.dev_id=dev.id
and td.del_tag=0 and dev.del_tag=0
and td.bureau_id=dev.bureau_id
and td.bureau_id=@intBureauID
and td.zubianhao=@Foreign_id--@Foreign_id
--and td.subunit_id=@subunit_id
inner join station s on dev.jizhanid=s.id
and s.del_tag=0
left join nudity_data nd on dev.xinxiniu=nd.button_id
and time > @ctemp1 and time<@ctemp2
insert @CountOneSimsPlan
select count(distinct button_id) ,0 from @temp
update @CountOneSimsPlan set complete=(select count(distinct nd_button) from @temp)
end
else if (@detail_type=2)
begin
insert @temp
select dev.xinxiniu,nd.button_id,nd.pen_id
from dbo.plan_detail pd inner join dev on pd.dev_id=dev.id
and pd.del_tag=0 and dev.del_tag=0
and pd.bureau_id=dev.bureau_id
and pd.bureau_id=@intBureauID
-- and pd.Foreign_id=@Foreign_id
--plan_id,bureau_id可以唯一标识一个计划么?,可以做到可以的
and pd.plan_id=@plan_id
inner join station s on dev.jizhanid=s.id
and s.del_tag=0
left join nudity_data nd on dev.xinxiniu=nd.button_id
and time > @ctemp1 and time<@ctemp2
insert @CountOneSimsPlan
select count(distinct button_id) ,0 from @temp
update @CountOneSimsPlan set complete=(select count(distinct nd_button) from @temp)
end
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO