--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'

GO


--删除旧记录
delete from SYS_Scripts where id=1878 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1878)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1878, 85, 'SQL_检索挂号类别', '检索挂号类别(挂号)', '检索挂号类别(挂号)', 1, 0, '<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1" UseW="1"><Sql>SET DATEFIRST 7 
Declare @Date datetime ,
        @CurrTime Datetime,
        @ltime datetime ,
        @DateNum int  ,
        @TmpDate varchar(20) 

SET @TmpDate =''%s''     
  
SET @CurrTime = getdate() 
SELECT @DateNum = datediff(day,''2000-01-02'', @CurrTime) %% 7 + 1
SELECT @ltime = convert(varchar, @CurrTime , 108)
SET @Date = CONVERT(datetime, CONVERT(varchar(10), @CurrTime , 120)) 

IF object_id(''tempdb..#tmpRegBCONew'') is not null 
BEGIN
  drop table #tmpRegBCONew
END
SELECT * INTO #tmpRegBCONew
FROM 
  (select
*
from BCO1
where BCO03 &lt;= BCO04
and (BCO03 &lt;= @ltime and BCO04 &gt;= @ltime) 
union all
select
*
from BCO1
where BCO03 &gt; BCO04
and (BCO03 &lt;= @ltime or BCO04 &gt;= @ltime )
 ) a
  
SELECT a.BCB01, a.BCB02, a.BCB03, a.BAO02, a.BCK01, a.BBY01, a.BCE01, a.BCE03, a.BCB12,  
    a.ABBRP, a.ABBRW ,b.BCK03 ,d.BBY05  , e.BCE02 ,f.BGG01 , f.BGG05 , f.BGG06 
FROM BCB1 a
  JOIN BBY1 d on a.BBY01 = d.BBY01
  JOIN BGG1 f on a.BCB01 = f.BCB01 
  JOIN BGH1 m ON f.BGG01 = m.BGG01 
  JOIN #tmpRegBCONew n ON isnull(m.BGH04,'''') = n.BCO02 
  LEFT JOIN BCK1 b ON a.BCK01 = b.BCK01 
  LEFT JOIN BCE1 e ON a.BCE01 = e.BCE01
where m.BGH03 = @DateNum  AND BGG03 &lt;= @Date and BGG18 &gt;= @Date</Sql><HD><Ie FD="BCB02" CA="编码" WD="100"/><Ie FD="BCB03" CA="名称" WD="100"/><Ie FD="BAO02" CA="号类" WD="100"/><Ie FD="BCK03" CA="科室" WD="100"/><Ie FD="BCE03" CA="医生" WD="100"/><Ie FD="BBY05" CA="项目" WD="100" SZ="1"/></HD><QF><Ie FD="BCB02" WE="7"/><Ie FD="BCB03" WE="7"/><Ie FD="a.ABBRP" WE="7"/><Ie FD="a.ABBRW" WE="7"/></QF></Grid></Configs>
', 1, ' select BCB01, BCB02, BCB03, BAO02, BCK01, BBY01, BCE01, 
   BCE03, BCB09, BCB10, BCB11, BCB12, BCB13, BCB14, BCB15, 
   BCB16, ABBRP, ABBRW 
 from BCB1 
 where BCB01 = %d', '2016-09-05 09:11:15', '(8065)冯义强', 0)
else
print 'SYS_Scripts.id=1878 已经存在.'

GO