1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| DECLARE @Session nvarchar(20)
DECLARE CUR_SessionList CURSOR FOR
SELECT [Session]
FROM dbo.[Qry - SessionList]
OPEN CUR_SessionList
FETCH NEXT FROM CUR_SessionList INTO @Session
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #@Session (
Datestamp VARCHAR(40),
Heure VARCHAR(40),
CampId VARCHAR(40),
AgentId VARCHAR(40),
TypeConn VARCHAR(40)
)
INSERT INTO #@Session
SELECT [DATE] as Datestamp, Heure, CampId, AgentId, TypeConn
FROM TbVoxcoFile
WHERE Session = @Session
ORDER BY Heure, CampId, AgentId, Session;
INSERT INTO iAgentNetBiosByCamp (Datestamp, HrsIn, HrsOut, AgentID, CampId, sHrsIn, sHrsOut)
SELECT convert(datetime,T.Datestamp,121), dbo.fn_FormatTime(T.Heure) as HrsIn,
dbo.fn_FormatTime((SELECT TOP 1 HEURE FROM [#@Session] where [#@Session].TypeConn='OUT' AND T.HEURE < [#@Session].heure)) AS HrsOut,
T.AgentID ,
T.CampId ,
dbo.fn_heurEnSeconde(dbo.fn_FormatTime(T.Heure)) as sHrsIn,
dbo.fn_heurEnSeconde(dbo.fn_FormatTime((SELECT TOP 1 HEURE FROM [#@Session] where [#@Session].TypeConn='OUT' AND T.HEURE < [#@Session].heure))) AS sHrsOut
FROM [#@Session] AS T
WHERE T.TypeConn='IN'
ORDER BY T.HEURE;
AND iAgentNetBiosByCamp
DROP TABLE #@Session
FETCH NEXT FROM CUR_SessionList
INTO @Session
END
CLOSE CUR_SessionList
DEALLOCATE CUR_SessionList |
Partager