September 5, 2014 at 2:47 am
Yeah we have forced the driver to log out from the System/Device which we have provided,
Just a have look for below query, will it be ok if I implement it.
IF NOT OBJECT_ID('TEMPDB..#TEMP_IN') IS NULL
BEGIN
DROP TABLE #TEMP_IN
END
IF NOT OBJECT_ID('TEMPDB..#TEMP_OUT') IS NULL
BEGIN
DROP TABLE #TEMP_OUT
END
SELECT
IDENTITY(BIGINT ,1 ,1) AS ROW_INDEX
,DDLH_IN.DRIVER_ID
,DDLH_IN.IS_LOGGED_IN
,'' AS DRIVER_NAME
,LOGGED_IN_DATETIME= DDLH_IN.LOGGED_DATETIME
into #TEMP_IN
FROM DBO.D_DRIVER_LOGGED_HISTORY DDLH_IN WHERE DDLH_IN.DRIVER_ID = 4 AND IS_LOGGED_IN = 1
ORDER BY DDLH_IN.LOGGED_DATETIME
SELECT
IDENTITY(BIGINT ,1 ,1) AS ROW_INDEX
,DDLH_IN.DRIVER_ID
,DDLH_IN.IS_LOGGED_IN
,'' AS DRIVER_NAME
,LOGGED_IN_DATETIME= DDLH_IN.LOGGED_DATETIME
into #TEMP_OUT
FROM DBO.D_DRIVER_LOGGED_HISTORY DDLH_IN WHERE DDLH_IN.DRIVER_ID = 4 AND IS_LOGGED_IN = 0
ORDER BY DDLH_IN.LOGGED_DATETIME
SELECT
*
FROM
#TEMP_IN as DDLH_IN
JOIN #TEMP_OUT AS DDLH_OUT
ON DDLH_IN.DRIVER_ID = DDLH_OUT.DRIVER_ID
AND DDLH_IN.ROW_INDEX = DDLH_OUT.ROW_INDEX
Patel Mohamad
September 5, 2014 at 3:20 am
Assuming there is a logout following the login, you can try this :
I recreate the order of the logging with ROW_NUMBER().
WITH cte (DRIVER_ID, LOGGED_DATETIME, IS_LOGGED_IN, R) AS (
SELECTDRIVER_ID,
LOGGED_DATETIME,
IS_LOGGED_IN,
ROW_NUMBER() OVER( PARTITION BY Driver_Id ORDER BY LOGGED_DATETIME)
FROMD_DRIVER_LOGGED_HISTORY
)
SELECTDRIVER_ID,
Log_In.LOGGED_DATETIME AS LOGGED_IN_DATETIME,
Log_Out.LOGGED_DATETIME AS LOGGED_OUT_DATETIME
FROMcte AS Log_In LEFT OUTER JOIN
(SELECT * FROM cte WHERE IS_LOGGED_IN = 0 ) AS Log_Out
ON Log_In.DRIVER_ID = Log_Out.DRIVER_ID
AND Log_Out.R = Log_In.R + 1
WHERELog_In.IS_LOGGED_IN = 1
ORDERBY Log_In.LOGGED_DATETIME
September 5, 2014 at 6:04 am
patelmohamad (9/5/2014)
Hi Koen Verbeeck,I have tried it but no luck, I have 81 Records for that particular driver but it is multiplying the records and the output value is 6561 records,
it is not fulfilling the requirement any other idea?
Thanks
Since you only have 81 records for this driver, how about posting the data up as CREATE TABLE...INSERT INTO... so that folks can see exactly what you've got and code against it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 5, 2014 at 6:34 am
Hi Koen Verbeeck
Data file is attached with the Post
have a look
Thanks
Patel Mohamad
September 5, 2014 at 6:53 am
SELECT *
FROM D_DRIVER_LOGGED_HISTORY i
OUTER APPLY (
SELECT TOP 1 LOGGED_DATETIME
FROM D_DRIVER_LOGGED_HISTORY o
WHERE o.DRIVER_ID = i.DRIVER_ID
AND o.IS_LOGGED_IN = 0
AND o.LOGGED_DATETIME > i.LOGGED_DATETIME
ORDER BY LOGGED_DATETIME
) oa
WHERE i.IS_LOGGED_IN = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 5, 2014 at 7:07 am
ok, it can be done by numbering the logging events in order, then splitting into logins and logouts, and then doing a full outer join on the tow parts with a suitable ON condition; here's something that will work (may not be terribly efficient).
WITH DR4 AS(
SELECT ROW_NUMBER() OVER (PARTITION BY DRIVER_ID ORDER BY LOGGED_DATETIME) AS RN
,DRIVER_ID
,LOGGED_DATETIME
,IS_LOGGED_IN
FROM D_DRIVER_LOGGED_HISTORY WHERE DRIVER_ID = 4
),
LOGIN AS (SELECT * FROM DR4 WHERE IS_LOGGED_IN = 0),
LOGOUT AS (SELECT * FROM DR4 WHERE IS_LOGGED_IN = 1)
SELECT 4 AS DRIVER_ID
,'SAM' AS DRIVER_NAME
,LOGIN.LOGGED_DATETIME AS LOGGED_IN_DATETIME
,LOGOUT.LOGGED_DATETIME AS LOGGED_OUT_DATETIME
FROM LOGIN FULL JOIN LOGOUT ON LOGIN.RN = LOGOUT.RN-1
;
edit 1; fixed silly typing error (is_logged _n in partitioning instead of driver_id!)
edit 2; Chris posted his solution while I was typing. It will perform better than mine, but if the first recorded event for driver 4 is a logout it will produce the wrong answer; also if the last recorded event for the driver is a login; or both.
Tom
September 5, 2014 at 11:39 pm
Thanks all of you,
The solution is given by all the expert is better than mine...
Thanks for the co-operation.
Patel Mohamad
September 6, 2014 at 7:27 pm
Post withdrawn... I ended up posting pretty much the same code as Chris.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2014 at 12:29 am
There is a more efficient method of solving this problem by partitioning the set on the log-in-out action. When a driver is logged in, it increments the login sequence by one and likewise when he is logged out it increments the logout sequence. By keeping both in and out sequences in the same column, a simple cross-tab can be used to split the timestamp column into login and logout columns.
😎
The code uses the previously provided DDL and data sample.
USE tempdb;
GO
/* Since we are not doing Google driver-less vehicles,
let's put a person behind the wheel
*/
;WITH DRIVER_REGISTER AS
(
SELECT
4 AS DRIVER_ID
,'SAM' AS DRIVER_NAME
)
/* Assumptions:
1) A Driver logges in and out in that order having
neither overlapping periods nor orphant logins.
2) No correlation needed such as packing or splitting
of sessions.
Using the ROW_NUMBER function to create two sequences
for each driver, one for logging in and the other for
logging out. This produces an order of sessions used
in the GROUPED_IN_OUT as session enumeration.
*/
,LOGIN_HIST_BASE AS
(
SELECT
DLH.ROW_INDEX
,ROW_NUMBER() OVER
(
PARTITION BY DLH.DRIVER_ID
,DLH.IS_LOGGED_IN
ORDER BY DLH.LOGGED_DATETIME ASC
,DLH.IS_LOGGED_IN DESC
) AS DLILIO_RID
,DLH.DRIVER_ID
,DR.DRIVER_NAME
,DLH.IS_LOGGED_IN
,DLH.LOGGED_DATETIME
FROM dbo.D_DRIVER_LOGGED_HISTORY DLH
INNER JOIN DRIVER_REGISTER DR
ON DLH.DRIVER_ID = DR.DRIVER_ID
)
/* Taking advantage of Microsoft's peculiarity of implicitly
assigning the lowest possible value to a missing/unknow,
NULLs are eliminated with MAX() and fully granular grouping
to compress the set and produce LOGIN and LOGOUT columns.
*/
,GROUPED_IN_OUT AS
(
SELECT
LHB.DLILIO_RID
,LHB.DRIVER_ID
,LHB.DRIVER_NAME
,MAX(CASE WHEN LHB.IS_LOGGED_IN = 1 THEN LHB.LOGGED_DATETIMEEND) AS LOGIN_DATETIME
,MAX(CASE WHEN LHB.IS_LOGGED_IN = 0 THEN LHB.LOGGED_DATETIMEEND) AS LOOUT_DATETIME
FROM LOGIN_HIST_BASE LHB
GROUP BY
LHB.DLILIO_RID
,LHB.DRIVER_ID
,LHB.DRIVER_NAME
)
/* And the rest is all history (pun intended)
*/
SELECT
GIO.DLILIO_RID
,GIO.DRIVER_ID
,GIO.DRIVER_NAME
,GIO.LOGIN_DATETIME
,GIO.LOOUT_DATETIME
--,DATEDIFF(MILLISECOND,GIO.LOGIN_DATETIME,GIO.LOOUT_DATETIME) / 1000.0 AS SESSION_DURATION
FROM GROUPED_IN_OUT GIO;
Results
DLILIO_RID DRIVER_ID DRIVER_NAME LOGIN_DATETIME LOOUT_DATETIME
----------- ---------- ----------- ------------------------ -----------------------=
1 4 SAM 2014-07-05 09:47:22.260 2014-07-05 09:47:41.173
2 4 SAM 2014-07-05 09:48:08.043 2014-07-05 09:48:22.163
3 4 SAM 2014-07-05 09:49:21.140 2014-07-05 09:49:34.107
4 4 SAM 2014-07-05 09:57:48.140 2014-07-07 14:00:23.067
5 4 SAM 2014-07-10 16:24:08.500 2014-07-10 16:35:31.557
6 4 SAM 2014-07-15 09:57:40.373 2014-07-15 13:15:17.910
7 4 SAM 2014-07-16 16:11:53.893 2014-07-16 16:46:47.077
8 4 SAM 2014-07-17 11:58:44.480 2014-07-17 12:32:55.913
9 4 SAM 2014-07-17 13:54:30.740 2014-07-17 13:54:40.747
10 4 SAM 2014-07-17 14:32:04.787 2014-07-21 14:59:53.363
11 4 SAM 2014-07-21 15:00:17.207 2014-07-23 12:45:13.320
12 4 SAM 2014-08-11 12:19:20.387 2014-08-11 12:58:24.120
13 4 SAM 2014-08-11 12:58:53.263 2014-08-11 12:59:22.240
14 4 SAM 2014-08-11 13:16:28.180 2014-08-12 08:06:10.697
15 4 SAM 2014-08-12 15:57:12.667 2014-08-12 16:29:36.523
16 4 SAM 2014-08-18 16:13:43.950 2014-08-21 13:26:20.640
17 4 SAM 2014-08-21 15:41:02.700 2014-08-21 16:21:58.803
18 4 SAM 2014-08-22 10:17:23.313 2014-08-22 12:50:26.907
19 4 SAM 2014-08-22 12:51:21.607 2014-08-22 12:51:45.860
20 4 SAM 2014-08-22 12:52:14.763 2014-08-22 12:54:25.670
21 4 SAM 2014-08-22 12:54:57.730 2014-08-22 15:32:56.460
22 4 SAM 2014-08-22 15:33:47.237 2014-08-25 08:44:22.763
23 4 SAM 2014-08-25 11:47:02.443 2014-08-25 16:41:04.840
24 4 SAM 2014-08-26 08:04:45.960 2014-08-26 15:52:35.010
25 4 SAM 2014-08-26 15:53:15.167 2014-08-26 15:53:46.033
26 4 SAM 2014-08-27 08:55:05.733 2014-08-27 08:57:59.687
27 4 SAM 2014-08-28 11:37:23.350 2014-08-28 13:13:07.080
28 4 SAM 2014-08-30 12:31:52.890 NULL
A STATISTICS IO comparison of the provided solutions
USE tempdb;
GO
SET NOCOUNT ON;
PRINT 'ROW_NUMBER AND AGGREGATION
------------------------------------------------------'
SET STATISTICS IO ON;
/* Since we are not doing Google driver-less vehicles,
let's put a person behind the wheel
*/
;WITH DRIVER_REGISTER AS
(
SELECT
4 AS DRIVER_ID
,'SAM' AS DRIVER_NAME
)
/* Assumptions:
1) A Driver logges in and out in that order having
neither overlapping periods nor orphant logins.
2) No correlation needed such as packing or splitting
of sessions.
Using the ROW_NUMBER function to create two sequences
for each driver, one for logging in and the other for
logging out. This produces an order of sessions used
in the GROUPED_IN_OUT as session enumeration.
*/
,LOGIN_HIST_BASE AS
(
SELECT
DLH.ROW_INDEX
,ROW_NUMBER() OVER
(
PARTITION BY DLH.DRIVER_ID
,DLH.IS_LOGGED_IN
ORDER BY DLH.LOGGED_DATETIME ASC
,DLH.IS_LOGGED_IN DESC
) AS DLILIO_RID
,DLH.DRIVER_ID
,DR.DRIVER_NAME
,DLH.IS_LOGGED_IN
,DLH.LOGGED_DATETIME
FROM dbo.D_DRIVER_LOGGED_HISTORY DLH
INNER JOIN DRIVER_REGISTER DR
ON DLH.DRIVER_ID = DR.DRIVER_ID
)
/* Taking advantage of Microsoft's peculiarity of implicitly
assigning the lowest possible value to a missing/unknow,
NULLs are eliminated with MAX() and fully granular grouping
to compress the set and produce LOGIN and LOGOUT columns.
*/
,GROUPED_IN_OUT AS
(
SELECT
LHB.DLILIO_RID
,LHB.DRIVER_ID
,LHB.DRIVER_NAME
,MAX(CASE WHEN LHB.IS_LOGGED_IN = 1 THEN LHB.LOGGED_DATETIMEEND) AS LOGIN_DATETIME
,MAX(CASE WHEN LHB.IS_LOGGED_IN = 0 THEN LHB.LOGGED_DATETIMEEND) AS LOOUT_DATETIME
FROM LOGIN_HIST_BASE LHB
GROUP BY
LHB.DLILIO_RID
,LHB.DRIVER_ID
,LHB.DRIVER_NAME
)
/* And the rest is all history (pun intended)
*/
SELECT
GIO.DLILIO_RID
,GIO.DRIVER_ID
,GIO.DRIVER_NAME
,GIO.LOGIN_DATETIME
,GIO.LOOUT_DATETIME
--,DATEDIFF(MILLISECOND,GIO.LOGIN_DATETIME,GIO.LOOUT_DATETIME) / 1000.0 AS SESSION_DURATION
FROM GROUPED_IN_OUT GIO;
SET STATISTICS IO OFF;
PRINT 'ROW_NUMBER, USBQUERIES AND FULL JOIN
------------------------------------------------------'
SET STATISTICS IO ON;
;WITH DR4 AS(
SELECT ROW_NUMBER() OVER (PARTITION BY DRIVER_ID ORDER BY LOGGED_DATETIME) AS RN
,DRIVER_ID
,LOGGED_DATETIME
,IS_LOGGED_IN
FROM D_DRIVER_LOGGED_HISTORY WHERE DRIVER_ID = 4
),
LOGIN AS (SELECT * FROM DR4 WHERE IS_LOGGED_IN = 0),
LOGOUT AS (SELECT * FROM DR4 WHERE IS_LOGGED_IN = 1)
SELECT 4 AS DRIVER_ID
,'SAM' AS DRIVER_NAME
,LOGIN.LOGGED_DATETIME AS LOGGED_IN_DATETIME
,LOGOUT.LOGGED_DATETIME AS LOGGED_OUT_DATETIME
FROM LOGIN FULL JOIN LOGOUT ON LOGIN.RN = LOGOUT.RN-1
;
SET STATISTICS IO OFF;
PRINT 'OUTER APPLY AND NESTED CONDITIONAL QUERY
------------------------------------------------------'
SET STATISTICS IO ON;
SELECT *
FROM D_DRIVER_LOGGED_HISTORY i
OUTER APPLY (
SELECT TOP 1 LOGGED_DATETIME
FROM D_DRIVER_LOGGED_HISTORY o
WHERE o.DRIVER_ID = i.DRIVER_ID
AND o.IS_LOGGED_IN = 0
AND o.LOGGED_DATETIME > i.LOGGED_DATETIME
ORDER BY LOGGED_DATETIME
) oa
WHERE i.IS_LOGGED_IN = 1
SET STATISTICS IO OFF;
PRINT 'ROW_NUMBER AND SELF-JOIN CTE
------------------------------------------------------'
SET STATISTICS IO ON;
WITH cte (DRIVER_ID, LOGGED_DATETIME, IS_LOGGED_IN, R) AS (
SELECTDRIVER_ID,
LOGGED_DATETIME,
IS_LOGGED_IN,
ROW_NUMBER() OVER( PARTITION BY Driver_Id ORDER BY LOGGED_DATETIME)
FROMD_DRIVER_LOGGED_HISTORY
)
SELECTLog_In.DRIVER_ID,
Log_In.LOGGED_DATETIME AS LOGGED_IN_DATETIME,
Log_Out.LOGGED_DATETIME AS LOGGED_OUT_DATETIME
FROMcte AS Log_In LEFT OUTER JOIN
(SELECT * FROM cte WHERE IS_LOGGED_IN = 0 ) AS Log_Out
ON Log_In.DRIVER_ID = Log_Out.DRIVER_ID
AND Log_Out.R = Log_In.R + 1
WHERELog_In.IS_LOGGED_IN = 1
ORDERBY Log_In.LOGGED_DATETIME
SET STATISTICS IO OFF;
Messages output
ROW_NUMBER AND AGGREGATION
------------------------------------------------------
Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'D_DRIVER_LOGGED_HISTORY'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
ROW_NUMBER, USBQUERIES AND FULL JOIN
------------------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'D_DRIVER_LOGGED_HISTORY'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
OUTER APPLY AND NESTED CONDITIONAL QUERY
------------------------------------------------------
Table 'D_DRIVER_LOGGED_HISTORY'. Scan count 29, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
ROW_NUMBER AND SELF-JOIN CTE
------------------------------------------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'D_DRIVER_LOGGED_HISTORY'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply