if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_AppointPercent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_AppointPercent]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



-- =============================================
-- 爽约率查询
-- =============================================
CREATE PROCEDURE P_AppointPercent
@S_BeginTime datetime ,          --开始时间  是由查询的医生提供
@S_EndTime datetime ,            --结束时间
@DeptID NVarChar(50),            --部门编号
@AllDept bit,                    --查询所有部门
@CountApp   int out,              --预约人数
@CountAppYse int out,            --赴约人数
@CountAppNo int out            --赴约人数
AS

set @S_BeginTime=convert( varchar(100), @S_BeginTime, 23)  --标准化时间格式
set @S_EndTime=convert( varchar(100), @S_EndTime, 23)  --标准化时间格式

if @AllDept=1
begin
    select @CountApp=count(*) --预约人数
    from AppointList   
    where AppointTime >= @S_BeginTime and AppointTime<=@S_EndTime  and cancel=0  -- cancel=0 表示有效预约，=1表示该预约已经取消


    select @CountAppYse=count(*) --赴约人数
    from AppointList a 
    left join YY_TEST.dbo.gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1
    where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is not null and a.cancel=0


    select @CountAppNo=count(*) --爽约人数,算出爽约率就行了，在对赴约进行重复记录Distinc是，可能去掉预约多科室的情况。
    from AppointList a 
    left join YY_TEST.dbo.gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1
    where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0

    --a.* --爽约记录列表
    --select  periodName,dlTo.DeptName DeptName,dlFrom.DeptName FromDeptName,convert( varchar(100), AppointTime, 23)AppointTime, 
    select  periodName,'所有科室' DeptName,dlFrom.DeptName FromDeptName,convert( varchar(100), AppointTime, 23)AppointTime, 
    dbo.f_realName(a.ManCreate) ManCreate, a.* 
    from AppointList a 
    left join YY_TEST.dbo.gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1
    left join periodList per 
    on a.periodID=per.periodID  
    left join DeptList dlTo 
    on a.DeptID=dlTo.ID 
    left join DeptList dlFrom 
    on a.FromDeptID=dlFrom.ID 
    where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0
    order by AppointTime,MedicalNo

end
else
begin
    select @CountApp=count(*) --预约人数
    from AppointList   
    where AppointTime >= @S_BeginTime and AppointTime<=@S_EndTime  and cancel=0 and DeptID=@DeptID -- cancel=0 表示有效预约，=1表示该预约已经取消


    select @CountAppYse=count(*) --赴约人数
    from AppointList a 
    left join gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1
    where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is not null and a.cancel=0 and a.DeptID=@DeptID


    select @CountAppNo=count(*) --爽约人数,算出爽约率就行了，在对赴约进行重复记录Distinc是，可能去掉预约多科室的情况。
    from AppointList a 
    left join gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1
    where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0 and a.DeptID=@DeptID

    --a.* --爽约记录列表
    select  periodName,dlTo.DeptName DeptName,dlFrom.DeptName FromDeptName,convert( varchar(100), AppointTime, 23)AppointTime, 
    dbo.f_realName(a.ManCreate) ManCreate, a.* 
    from AppointList a 
    left join gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1
    left join periodList per 
    on a.periodID=per.periodID  
    left join DeptList dlTo 
    on a.DeptID=dlTo.ID 
    left join DeptList dlFrom 
    on a.FromDeptID=dlFrom.ID 
    where AppointT