sql server 2008 存储过程排序记录

最近在项目开发中遇到一个棘手的问题。经过一段时间的分析使用存储过程解决了。自己对SQL这边面理解不是很到位以前也没写过，决定记录一下。

1、业务逻辑

在申请探视时，一个探视终端和一个被探视终端实现自动排队功能和选择日期排队功能，申请方只需选择探视时长即可。被申请人方选择自动或日期排队。

2、技术难点

  1）一般会议按照时间顺序排队即可。探视系统涉及多终端之间会相连的问题。会议不能按照会议表中时间来排序必须取到这两个终端其中一个存在的会然后进行排队。需要终端ID组使用@deves.nodes('QM/DEV')  。

2）排队的时间问题，比如A终端探视了B终端和C终端各20分钟。那么D终端探视C终端时候前20分钟是浪费的，D终端在后面申请探视E终端时双方终端前20分钟都有空闲时间。但如果按照D和C的结束算那么应该是40分钟之后开始排序。而且终端之间必然相互影响的这样一天时间中只要影响到的终端只能共用8小时，无法满足100多终端探视业务需求，使用解决方案为取终端空闲时间进行匹配在，取出中间时长减去会议时长进行匹配。

3、使用表

T_Business_Conference//会议表表

T_Business_Party//会场表（记录会议的终端信息）

T_System_Dictionary//字典表

T_System_DicType//字段类型表

T_System_ConferencePRI//会议调度表

T_System_Setting//系统设置表

4、实现

1）查找出已经预约当天的冲突终端会议。

2）查找出可用时间段。

3）进行匹配

4）添加会议排队，由调度服务自动调度预约会议添加MUC和录播进行远程视频探视。

5、代码

USE [Visit_YNSF]
GO
-- =============================================
-- Author:QMYJ
-- Create date: 2014-07-27
-- Description: 根据会议开始时间与结束时间，会场终端，查询该时间段内冲突的级别比当前会议类型级别低的会议
-- =============================================
-- EXEC getConferenceLeisureTime '0','2014-07-28','15','<QM><DEV Code="53030301"/><DEV Code="53030501"/></QM>'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[getConferenceLeisureTime](@type nvarchar(1),
@ConferenceDate Datetime,
@pick int,
@deves XML
)
AS
BEGIN
IF(@type = '0')--- 自动排序
set @ConferenceDate = DATEADD(dd,3,GETDATE());---自动排队以当前日期的三天后开始日期
SET NOCOUNT ON;
DECLARE @devTable TABLE(DevCode Nvarchar(50));
INSERT INTO @devTable
SELECT T.OpTable.value('(@Code)[1]','NVARCHAR(50)') 
FROM @deves.nodes('QM/DEV') T(OpTable);
DECLARE @Table3 Table(ID uniqueidentifier,MeetStartTime DateTime,MeetEndTime DateTime);
INSERT INTO @Table3
SELECT DISTINCT T1.ID,t1.MeetStartTime,t1.MeetEndTime
FROM dbo.T_Business_Conference AS T1
JOIN dbo.T_Business_Party AS T2 ON T1.ID = T2.MeetID
JOIN @devTable AS T3 ON T2.DeviceID = T3.DevCode
LEFT JOIN dbo.T_System_Dictionary AS T4 ON T1.MeetType = T4.DicCode
AND T4.TypeCode = '1000107'
WHERE T1.IsCancel <> '1' 
AND T1.IsStop <> '1'
AND T1.MeetType IN
(
SELECT MeetType FROM dbo.T_System_ConferencePRI
WHERE MeetLevel>=
(
SELECT MeetLevel FROM dbo.T_System_ConferencePRI
WHERE MeetType = '02'
)
)
AND DATEDIFF(DD,T1.MeetStartTime,@ConferenceDate) = 0
AND DATEDIFF(DD,T1.MeetEndTime,@ConferenceDate) = 0

SELECT  * FROM @Table3
DECLARE @firstTime DATETIME;
DECLARE @secTime DateTime;
DECLARE @threeTime DateTime;
DECLARE @fourTime DateTime;
DECLARE @DiffVistaPacke int;
SET @DiffVistaPacke =(SELECT DiffVistaPacke FROM [Visit_YNSF].[dbo].[T_System_Setting]);
SET @firstTime = DATEADD(hh,8,@ConferenceDate);
SET @secTime = DATEADD(hh,12,@ConferenceDate);
SET @threeTime = DATEADD(hh,14,@ConferenceDate);
SET @fourTime = DATEADD(hh,17,@ConferenceDate);
DECLARE @Table1 Table(row int,startTime DateTime);
DECLARE @Table2 TABLE(id uniqueidentifier,startTime DateTime,endTime dateTime,timePick int);
INSERT INTO @Table1
SELECT ROW_NUMBER() over(Order by T.T ASC),T.T FROM
(
SELECT @firstTime AS T
UNION
SELECT @secTime AS T
UNION 
SELECT @threeTime AS T
UNION
SELECT @fourTime AS T
UNION
SELECT DATEADD(MINUTE,@DiffVistaPacke,MeetStartTime) AS T FROM @Table3
WHERE DATEDIFF(DD,MeetStartTime,@ConferenceDate) = 0
UNION
SELECT DATEADD(MINUTE,@DiffVistaPacke,MeetEndTime) AS T FROM @Table3
WHERE DATEDIFF(DD,MeetStartTime,@ConferenceDate) = 0 ) AS T
DELETE FROM @Table1
WHERE startTime < @firstTime
or (startTime > @secTime and startTime <@threeTime)
or startTime > @fourTime
INSERT INTO @Table2(id,startTime,endTime,timePick)
SELECT NEWID(),T1.startTime AS startTime,T2.startTime as endTime,DATEDIFF(MINUTE,T1.startTime,T2.startTime) FROM @Table1 AS T1
LEFT JOIN @Table1 AS T2 ON (t1.row+1) = t2.row
DELETE FROM @Table2
WHERE id in(
SELECT TT.id FROM @Table2 AS TT
JOIN @Table3 AS CC ON TT.startTime = DATEADD(MINUTE,-@DiffVistaPacke,CC.MeetStartTime)
AND TT.endTime = DATEADD(MINUTE,@DiffVistaPacke,cc.MeetEndTime))
OR timePick is null
Select TOP 3 UD.id,UD.startTime,DATEADD(MINUTE,@pick,UD.startTime) as endTime  FROM(
select * from @Table2
where timePick >=@pick
and DATEDIFF(mi,startTime,DATEADD(hh,12,@ConferenceDate)) !=0) AS UD
END