June 15, 2010 at 5:59 am
TABLE1:
USER_LOGIN NO_OF_LOGINS TOTAL_DURATION
------------------------- ------------ ----------------
edward 31 35 Hrs 43 Mins.
neena 177 545 Hrs 5 Mins.
npco 30 9 Hrs 9 Mins.
fdesk 27 53 Hrs 1 Mins.
susan 19 11 Hrs 10 Mins.
xtech 9 0 Hrs 50 Mins.
TABLE 2 :
USER_LOGIN LOGIN_DT LOGOUT_DT LOCATION DURATION
---------- ---------------- ---------------- ------------- -------------------------------
susan 10/26/2009 14:48 10/26/2009 14:49 192.168.3.61 0 Hrs 1 Mins.
susan 10/26/2009 14:56 10/26/2009 15:13 192.168.3.61 0 Hrs 17 Mins.
susan 10/26/2009 15:13 10/26/2009 15:20 192.168.3.61 0 Hrs 7 Mins.
susan 10/28/2009 10:37 10/28/2009 11:00 192.168.5.156 0 Hrs 23 Mins.
susan 10/30/2009 11:22 10/30/2009 12:17 192.168.5.156 0 Hrs 55 Mins.
susan 11/03/2009 15:02 11/03/2009 15:03 192.168.3.126 0 Hrs 1 Mins.
edward 11/05/2009 14:28 11/05/2009 14:29 192.168.3.26 0 Hrs 1 Mins.
edward 11/09/2009 12:21 11/09/2009 12:21 192.168.3.61 0 Hrs 0 Mins.
edward 11/09/2009 12:21 11/09/2009 12:21 192.168.3.61 0 Hrs 0 Mins.
RESULT:
edward 31 35 Hrs 43 Mins. (COINTAINS 31 ROWS)
edward 11/05/2009 14:28 11/05/2009 14:29 192.168.3.26 0 Hrs 1 Mins.
edward 11/09/2009 12:21 11/09/2009 12:21 192.168.3.61 0 Hrs 0 Mins.
edward 11/09/2009 12:21 11/09/2009 12:21 192.168.3.61 0 Hrs 0 Mins.
.
.
31 ROWS
susan 19 11 Hrs 10 Mins. (COINTAINS 19 ROWS)
susan 10/26/2009 14:48 10/26/2009 14:49 192.168.3.61 0 Hrs 1 Mins.
susan 10/26/2009 14:56 10/26/2009 15:13 192.168.3.61 0 Hrs 17 Mins.
susan 10/26/2009 15:13 10/26/2009 15:20 192.168.3.61 0 Hrs 7 Mins.
susan 10/28/2009 10:37 10/28/2009 11:00 192.168.5.156 0 Hrs 23 Mins.
susan 10/30/2009 11:22 10/30/2009 12:17 192.168.5.156 0 Hrs 55 Mins.
susan 11/03/2009 15:02 11/03/2009 15:03 192.168.3.126 0 Hrs 1 Mins.
.
.
19 ROWS
HOW TO GET THIS OUTPUT
either using union or any other way
June 15, 2010 at 6:14 am
June 15, 2010 at 6:57 am
CREATE TABLE USER_SESSION(
USER_NUM int NOT NULL,
LOGIN_DT datetime NOT NULL,
LOGOUT_DT datetime NULL,
USER_LOCATION varchar(4000) NULL,
)
INSERT INTO USER_SESSION values(228,'2009-10-21 15:37:35.000','2009-10-21 16:34:26.000','192.168.3.126')
INSERT INTO USER_SESSION values(228,'2009-10-22 14:17:19.000','2009-10-22 14:23:47.000','127.0.0.1')
INSERT INTO USER_SESSION values(228,'2009-10-22 14:51:34.000','2009-10-22 15:24:11.000','127.0.0.1')
INSERT INTO USER_SESSION values(23,'2009-10-22 15:09:30.000','2009-10-22 15:10:10.000','127.0.0.1')
CREATE TABLE USER(
USER_NUM int IDENTITY(1,1) NOT NULL,
USER_LOGIN varchar(25) NOT NULL)
INSERT INTO USER_SESSION VALUES(23,'rbray')
INSERT INTO USER_SESSION VALUES(228, 'sri')
--------------------------------------------------------------------------
DECLARE @IPARAM0 DATETIME,@IPARAM1 DATETIME
SET @IPARAM0='20090101'
SET @IPARAM1='20091231'
CREATE TABLE #SUMRY_DTL1(USER_LOGIN VARCHAR(15),LOGIN_DT VARCHAR(20),LOGOUT_DT VARCHAR(20),LOCATION VARCHAR(20),
DURATION VARCHAR(20) )
CREATE TABLE #SUMRY1(USER_LOGIN VARCHAR(15),NO_OF_LOGINS VARCHAR(5),TOTAL_DURATION VARCHAR(20),BLANK1 VARCHAR(5),
BLANK2 VARCHAR(2))
--RETRIVE USER_NUM & TIME
SELECT USER_NUM,CONVERT(varchar,(LOGOUT_DT - LOGIN_DT), 108) AS HHMMSS
INTO #USER_TIME FROM USER_SESSION WITH(NOLOCK) WHERE (LOGIN_DT between @IPARAM0 and @IPARAM1)ORDER BY USER_NUM
--RETRIVE NUMBER OF LOGIN COUNTS
select USER_NUM,COUNT(USER_NUM) NO_OF_LOGINS INTO #USER_COUNT FROM #USER_TIME GROUP BY USER_NUM
select B.USER_NUM,
(select C.NO_OF_LOGINS from #USER_COUNT C where C.USER_NUM=B.USER_NUM)[NO_OF_LOGINS],
sum(convert(int,substring(convert(varchar,HHMMSS,108),1,2)))
sum(convert(int,substring(convert(varchar,HHMMSS,108),4,2)))[MN],
sum(convert(int,substring(convert(varchar,HHMMSS,108),7,2)))[SEC]
into #USER_TIME_CALC FROM #USER_TIME A
LEFT OUTER JOIN #USER_COUNT B ON A.USER_NUM=B.USER_NUM
group by B.USER_NUM
--CALCULATE HOURS AND MINUTES
UPDATE #USER_TIME_CALC SET MN= (MN+(SEC/60)), SEC=(SEC%60)
UPDATE #USER_TIME_CALC SET HR=(HR+(MN/60)),MN=(MN%60)
--DISPALY USER LOGIN NAME,NO OF LOGINS,TOTAL TIME
SELECT USER_LOGIN,NO_OF_LOGINS,
ISNULL(CONVERT(VARCHAR(5),HR)+' Hrs '+CONVERT(VARCHAR(2),MN)+' Mins.','0 Hrs 0 Mins.') TOTAL_DURATION
INTO #SUMRY
from #USER_TIME_CALC A WITH(NOLOCK) JOIN USER B ON A.USER_NUM=B.USER_NUM
ORDER BY B.USER_NUM
--SELECT * FROM #SUMRY
--SESSION DETAILS
SELECT B.USER_LOGIN USER_LOGIN,
convert(varchar(10),login_dt,101)+' '+SUBSTRING(convert(varchar, login_dt,114),1,5) LOGIN_DT,
ISNULL(convert(varchar(10),LOGOUT_DT,101)+' '+SUBSTRING(convert(varchar, LOGOUT_DT,114),1,5),'NA')LOGOUT_DT,
ISNULL(USER_LOCATION,'NA')LOCATION,
ISNULL(CONVERT(varchar,(DATEDIFF(mi,LOGIN_DT,LOGOUT_DT)/60)) +' Hrs '+CONVERT(VARCHAR,(DATEDIFF(mi,LOGIN_DT,LOGOUT_DT)%60))+
' Mins.',' NA ') DURATION INTO #SUMRY_DTL
FROM USER_SESSION A JOIN USER B ON A.USER_NUM=B.USER_NUM
WHERE (LOGIN_DT between @IPARAM0 and @IPARAM1)
order by A.USER_NUM
--select * from #SUMRY_DTL
insert into #SUMRY1(USER_LOGIN,NO_OF_LOGINS,TOTAL_DURATION,BLANK1,BLANK2)
select USER_LOGIN,NO_OF_LOGINS,TOTAL_DURATION,'','' from #SUMRY ORDER BY USER_LOGIN
INSERT INTO #SUMRY_DTL1(USER_LOGIN,LOGIN_DT,LOGOUT_DT,LOCATION,DURATION)
SELECT USER_LOGIN,LOGIN_DT,LOGOUT_DT,LOCATION,DURATION FROM #SUMRY_DTL ORDER BY USER_LOGIN
SELECT * FROM #SUMRY_DTL1
union
SELECT * FROM #SUMRY1
----------------------------
DROP TABLE #USER_TIME_CALC
DROP TABLE #SUMRY_DTL1
DROP TABLE #SUMRY_DTL
DROP TABLE #USER_TIME
DROP TABLE #USER_COUNT
DROP TABLE #SUMRY1
DROP TABLE #SUMRY
June 15, 2010 at 7:09 am
Please test your code before posting it. USER is not very good name for the object.
June 15, 2010 at 7:14 am
NEED OUTPUT LIKE THIS:
neena 177 545 Hrs 5 Mins.
<NULL> 11/30/2009 18:54 11/30/2009 21:11 127.0.0.1 2 Hrs 17 Mins.
<NULL> 11/30/2009 19:11 11/30/2009 21:37 127.0.0.1 2 Hrs 26 Mins.
<NULL> 11/30/2009 19:37 11/30/2009 21:38 127.0.0.1 2 Hrs 1 Mins.
<NULL> 12/01/2009 10:58 12/01/2009 13:07 127.0.0.1 2 Hrs 9 Mins.
<NULL> 12/01/2009 11:07 12/01/2009 13:08 127.0.0.1 2 Hrs 1 Mins.
<NULL> 12/01/2009 11:09 12/01/2009 13:11 127.0.0.1 2 Hrs 2 Mins.
<NULL> 12/01/2009 12:10 12/01/2009 14:37 127.0.0.1 2 Hrs 27 Mins.
<NULL> 12/01/2009 14:48 12/01/2009 16:51 127.0.0.1 2 Hrs 3 Mins.
new123 8 0 Hrs 29 Mins.
<NULL> 11/13/2009 14:44 11/13/2009 14:49 192.168.5.156 0 Hrs 5 Mins.
<NULL> 11/19/2009 12:34 11/19/2009 12:34 192.168.5.156 0 Hrs 0 Mins.
<NULL> 11/19/2009 12:35 11/19/2009 12:36 192.168.5.156 0 Hrs 1 Mins.
<NULL> 11/19/2009 12:36 11/19/2009 12:38 192.168.5.156 0 Hrs 2 Mins.
<NULL> 11/19/2009 15:30 11/19/2009 15:44 192.168.5.156 0 Hrs 14 Mins.
<NULL> 11/19/2009 15:45 11/19/2009 15:54 192.168.5.156 0 Hrs 9 Mins.
<NULL> 11/19/2009 16:07 11/19/2009 16:07 192.168.5.156 0 Hrs 0 Mins.
<NULL> 11/19/2009 16:35 11/19/2009 16:35 192.168.5.156 0 Hrs 0 Mins.
newvasta 1 0 Hrs 0 Mins.
<NULL> 11/30/2009 15:48 11/30/2009 15:48 127.0.0.1 0 Hrs 0 Mins.
npco 30 9 Hrs 9 Mins.
<NULL> 10/26/2009 14:07 10/26/2009 14:15 192.168.3.61 0 Hrs 8 Mins.
<NULL> 10/26/2009 14:39 10/26/2009 14:40 192.168.3.61 0 Hrs 1 Mins.
<NULL> 11/09/2009 16:25 11/09/2009 16:29 192.168.3.61 0 Hrs 4 Mins.
<NULL> 11/11/2009 12:15 11/11/2009 12:21 192.168.3.61 0 Hrs 6 Mins.
<NULL> 11/11/2009 12:21 11/11/2009 12:30 192.168.3.61 0 Hrs 9 Mins.
<NULL> 11/12/2009 12:16 11/12/2009 12:20 192.168.3.61 0 Hrs 4 Mins.
<NULL> 11/12/2009 12:22 11/12/2009 12:33 192.168.3.61 0 Hrs 11 Mins.
<NULL> 11/12/2009 12:57 11/12/2009 19:26 192.168.3.45 6 Hrs 29 Mins.
<NULL> 11/12/2009 19:26 11/12/2009 19:31 127.0.0.1 0 Hrs 5 Mins.
I'M GETTING THIS:
npco 11/18/2009 21:09 11/18/2009 21:13 127.0.0.1 0 Hrs 4 Mins.
npco 11/18/2009 21:16 11/18/2009 21:17 127.0.0.1 0 Hrs 1 Mins.
npco 11/18/2009 21:21 11/18/2009 21:22 127.0.0.1 0 Hrs 1 Mins.
npco 11/21/2009 10:04 11/21/2009 10:06 127.0.0.1 0 Hrs 2 Mins.
npco 11/21/2009 10:06 11/21/2009 10:07 127.0.0.1 0 Hrs 1 Mins.
npco 11/23/2009 11:52 11/23/2009 11:57 127.0.0.1 0 Hrs 5 Mins.
npco 11/30/2009 15:46 11/30/2009 15:48 127.0.0.1 0 Hrs 2 Mins.
npco 30 9 Hrs 9 Mins.
quinn 1 0 Hrs 0 Mins.
quinn 11/12/2009 15:34 NA 192.168.3.61 NA
radio1 11/02/2009 10:52 11/02/2009 10:52 192.168.5.156 0 Hrs 0 Mins.
radio1 11/02/2009 11:21 11/02/2009 11:22 192.168.5.156 0 Hrs 1 Mins.
radio1 2 0 Hrs 1 Mins.
rbray 10/21/2009 16:37 10/21/2009 16:45 192.168.3.116 0 Hrs 8 Mins.
rbray 10/21/2009 16:45 10/21/2009 16:48 192.168.3.116 0 Hrs 3 Mins.
rbray 10/21/2009 16:50 10/21/2009 16:51 192.168.3.116 0 Hrs 1 Mins.
rbray 10/21/2009 17:04 10/21/2009 17:07 192.168.2.11 0 Hrs 3 Mins.
rbray 10/21/2009 17:07 10/21/2009 17:09 192.168.3.116 0 Hrs 2 Mins.
rbray 10/21/2009 17:11 10/21/2009 17:12 192.168.3.116 0 Hrs 1 Mins.
rbray 10/21/2009 17:12 10/21/2009 17:13 192.168.3.116 0 Hrs 1 Mins.
June 15, 2010 at 7:20 am
Eugene Elutin (6/15/2010)
Please test your code before posting it. USER is not very good name for the object.
I shorted table name just to post it
June 15, 2010 at 8:11 am
Friend, next time please test your setup code before posting it.
Here we are, in first attempt (it may require some tuning):
-- SETUP:
/*
CREATE TABLE USER_SESSION(
USER_NUM int NOT NULL,
LOGIN_DT datetime NOT NULL,
LOGOUT_DT datetime NULL,
USER_LOCATION varchar(4000) NULL,
)
INSERT INTO USER_SESSION values(228,'2009-10-21 15:37:35.000','2009-10-21 16:34:26.000','192.168.3.126')
INSERT INTO USER_SESSION values(228,'2009-10-22 14:17:19.000','2009-10-22 14:23:47.000','127.0.0.1')
INSERT INTO USER_SESSION values(228,'2009-10-22 14:51:34.000','2009-10-22 15:24:11.000','127.0.0.1')
INSERT INTO USER_SESSION values(23,'2009-10-22 15:09:30.000','2009-10-22 15:10:10.000','127.0.0.1')
CREATE TABLE (
USER_NUM int IDENTITY(1,1) NOT NULL,
USER_LOGIN varchar(25) NOT NULL)
set identity_insert on
INSERT INTO (USER_NUM, USER_LOGIN) VALUES(23,'rbray')
INSERT INTO (USER_NUM, USER_LOGIN) VALUES(228, 'sri')
set identity_insert off
*/
-- Query:
DECLARE @IPARAM0 DATETIME,@IPARAM1 DATETIME
SET @IPARAM0='20090101'
SET @IPARAM1='20091231'
;WITH LogTimeSum
AS
(
SELECT 1 AS Ord, USER_NUM, COUNT(*) LogCnt, SUM(DATEDIFF(SECOND,LOGIN_DT, LOGOUT_DT)) AS LoggedOnTimeS
FROM USER_SESSION
WHERE LOGIN_DT BETWEEN @IPARAM0 and @IPARAM1
GROUP BY USER_NUM
)
--select * from LogTime
SELECT USER_LOGIN, [NO_OF_LOGINS/LOGIN_DT], [TOTAL_DURATION/LOGOUT_DT], LOCATION, DURATION
FROM
(
SELECT 1 AS Ord
,usr.USER_NUM AS UserNo
,usr.USER_LOGIN AS USER_LOGIN
,CAST(lts.LogCnt AS VARCHAR(30)) AS [NO_OF_LOGINS/LOGIN_DT]
,CAST(CAST(lts.LoggedOnTimeS/3600 AS VARCHAR) + ' Hrs ' +
CAST((lts.LoggedOnTimeS - (lts.LoggedOnTimeS/3600)*3600)/60 AS VARCHAR) + ' Mins' AS VARCHAR(30)) AS [TOTAL_DURATION/LOGOUT_DT]
,CAST('' AS VARCHAR(4000)) AS LOCATION
,CAST('' AS VARCHAR(20)) AS DURATION
FROM LogTimeSum lts
JOIN usr
ON usr.USER_NUM = lts.USER_NUM
UNION ALL
SELECT 2
,usr.USER_NUM
,usr.USER_LOGIN
,CONVERT(VARCHAR(30), uss.LOGIN_DT, 113)
,CONVERT(VARCHAR(30), uss.LOGOUT_DT, 113)
,uss.USER_LOCATION
,CAST(CAST(DATEDIFF(SECOND,uss.LOGIN_DT, uss.LOGOUT_DT)/3600 AS VARCHAR) + ' Hrs ' +
CAST((DATEDIFF(SECOND,uss.LOGIN_DT, uss.LOGOUT_DT) - (DATEDIFF(SECOND,uss.LOGIN_DT, uss.LOGOUT_DT)/3600)*3600)/60 AS VARCHAR) + ' Mins' AS VARCHAR(30))
FROM USER_SESSION uss
JOIN usr
ON uss.USER_NUM = usr.USER_NUM
WHERE LOGIN_DT BETWEEN @IPARAM0 and @IPARAM1
) Q
ORDER BY USER_LOGIN, UserNo, Ord
Now, there are some questions you need to answer to yourself:
What do you want do display if the login duration is just few seconds? 1 min or 0 min? Looking your example you tend to display 0, but what about if each separate login will be for a few seconds but you have many of them - you can end up with having few minutes in the total but each individual record will display 0!
I have left date unformatted. There is a reason for that: If you have time login time as 10:10:30 and logout time of 10:10:50, would you like to display duration as 0 or 1 (take in count the above question)? If you display duration as 1 min (to match up total) then having login at 10:10 and logout at 10:10 with duration of 1 min will look a bit strange.
On another hand you can calculate individual durations (displaying 0 min if the login duration is less than a minute) and then calculate sum of the individual durations. But here you can have another issue when displaying the time as HH:MM, if the login happens at 10:10:50 and logout at 10:11:10, you will display duration as 0 mins but log in/out time as 10:10 and 10:11 showing that the duration is 1 min.
I am not sure what you really want.
June 15, 2010 at 10:47 pm
Thank you very much. I appreciate taking you time off to help me.
As this is my first post its quite cluttered...next time I'll post as per posting rules.
can we get the output like this,
bmgr1236220 Hrs 28 Mins
27 Nov 2009 17:48:47:00027 Nov 2009 17:50:50:000127.0.0.10 Hrs 2 Mins
27 Nov 2009 17:38:29:00027 Nov 2009 17:48:47:000127.0.0.10 Hrs 10 Mins
14 Nov 2009 14:21:16:00014 Nov 2009 14:23:10:000192.168.3.610 Hrs 1 Mins
14 Nov 2009 14:23:24:00014 Nov 2009 15:21:00:000192.168.3.610 Hrs 57 Mins
13 Nov 2009 17:24:17:00013 Nov 2009 17:27:44:000192.168.3.610 Hrs 3 Mins
18 Nov 2009 14:24:52:00027 Nov 2009 17:38:29:000192.168.3.61219 Hrs 13 Mins
edward3159 Hrs 43 Mins
23 Nov 2009 18:48:19:00023 Nov 2009 18:51:08:000127.0.0.10 Hrs 2 Mins
24 Nov 2009 14:27:30:00024 Nov 2009 14:59:09:000127.0.0.10 Hrs 31 Mins
27 Nov 2009 15:41:33:00027 Nov 2009 15:42:56:000127.0.0.10 Hrs 1 Mins
Here is the sample code to test:
CREATE TABLE #SMRY_DTL(USER_NUM VARCHAR(15),LOGIN_DT VARCHAR(20),LOGOUT_DT VARCHAR(20),LOCATION VARCHAR(20),
DURATION VARCHAR(20) )
CREATE TABLE #SMRY(USER_NUM VARCHAR(15),NO_OF_LOGINS VARCHAR(5),TOTAL_DURATION VARCHAR(20),LOCATION VARCHAR(10),
DURATION VARCHAR(2))
INSERT INTO #SMRY VALUES('1','467','3315 Hrs 5 Mins','','')
INSERT INTO #SMRY VALUES('2','31','59 Hrs 43 Mins','','')
INSERT INTO #SMRY VALUES('4','177','713 Hrs 5 Mins','','')
INSERT INTO #SMRY VALUES('5','30','9 Hrs 9 Mins','','')
INSERT INTO #SMRY VALUES('6','27','365 Hrs 1 Mins','','')
INSERT INTO #SMRY VALUES('8','19','83 Hrs 10 Mins','','')
INSERT INTO #SMRY VALUES('11','9','0 Hrs 50 Mins','','')
INSERT INTO #SMRY VALUES('12','1','0 Hrs 0 Mins','','')
INSERT INTO #SMRY_DTL VALUES('1','10/26/2009 14:48','10/26/2009 14:49','192.168.3.61','0 Hrs 1 Mins')
INSERT INTO #SMRY_DTL VALUES('1','10/26/2009 14:56','10/26/2009 15:13','192.168.3.61','0 Hrs 16 Mins')
INSERT INTO #SMRY_DTL VALUES('1','10/26/2009 15:13','10/26/2009 15:20','192.168.3.61','0 Hrs 7 Mins')
INSERT INTO #SMRY_DTL VALUES('1','10/28/2009 10:37','10/28/2009 11:00','192.168.5.156','0 Hrs 23 Mins')
INSERT INTO #SMRY_DTL VALUES('4', '11/13/2009 11:31','11/13/2009 14:13','192.168.3.61','2 Hrs 42 Mins')
INSERT INTO #SMRY_DTL VALUES('4','11/13/2009 13:05','11/13/2009 17:31','192.168.3.61','4 Hrs 25 Mins')
INSERT INTO #SMRY_DTL VALUES('4','11/13/2009 11:03','11/13/2009 13:31','192.168.3.61','2 Hrs 28 Mins')
INSERT INTO #SMRY_DTL VALUES('4','11/11/2009 16:19','11/11/2009 18:21','192.168.3.61','2 Hrs 2 Mins')
INSERT INTO #SMRY_DTL VALUES('4','11/11/2009 16:25','11/12/2009 12:24','192.168.3.61','19 Hrs 59 Mins')
INSERT INTO #SMRY_DTL VALUES('6','10/30/2009 12:17','10/30/2009 12:18','192.168.5.156','0 Hrs 0 Mins')
INSERT INTO #SMRY_DTL VALUES('6','10/30/2009 12:18','10/30/2009 12:26','192.168.5.156','0 Hrs 7 Mins')
INSERT INTO #SMRY_DTL VALUES('6','10/30/2009 15:21','11/12/2009 14:54','192.168.5.156','311 Hrs 32 Mins')
INSERT INTO #SMRY_DTL VALUES('6','10/27/2009 11:50','10/27/2009 12:53','192.168.3.61','1 Hrs 2 Mins')
INSERT INTO #SMRY_DTL VALUES('6','10/27/2009 12:53','10/27/2009 12:53','127.0.0.1','0 Hrs 0 Mins')
INSERT INTO #SMRY_DTL VALUES('6','10/27/2009 12:55','10/27/2009 14:18','127.0.0.1','1 Hrs 23 Mins')
select * from #SMRY
select * from #SMRY_DTL
------------------------------------------------------------
DROP TABLE #SMRY_DTL
DROP TABLE #SMRY
June 16, 2010 at 4:09 am
You don't want to display the same login multiple times? Try:
-- SETUP:
/*
CREATE TABLE USER_SESSION(
USER_NUM int NOT NULL,
LOGIN_DT datetime NOT NULL,
LOGOUT_DT datetime NULL,
USER_LOCATION varchar(4000) NULL,
)
INSERT INTO USER_SESSION values(228,'2009-10-21 15:37:35.000','2009-10-21 16:34:26.000','192.168.3.126')
INSERT INTO USER_SESSION values(228,'2009-10-22 14:17:19.000','2009-10-22 14:23:47.000','127.0.0.1')
INSERT INTO USER_SESSION values(228,'2009-10-22 14:51:34.000','2009-10-22 15:24:11.000','127.0.0.1')
INSERT INTO USER_SESSION values(23,'2009-10-22 15:09:30.000','2009-10-22 15:10:10.000','127.0.0.1')
CREATE TABLE (
USER_NUM int IDENTITY(1,1) NOT NULL,
USER_LOGIN varchar(25) NOT NULL)
set identity_insert on
INSERT INTO (USER_NUM, USER_LOGIN) VALUES(23,'rbray')
INSERT INTO (USER_NUM, USER_LOGIN) VALUES(228, 'sri')
set identity_insert off
*/
-- Query:
DECLARE @IPARAM0 DATETIME,@IPARAM1 DATETIME
SET @IPARAM0='20090101'
SET @IPARAM1='20091231'
;WITH LogTimeSum
AS
(
SELECT 1 AS Ord, USER_NUM, COUNT(*) LogCnt, SUM(DATEDIFF(SECOND,LOGIN_DT, LOGOUT_DT)) AS LoggedOnTimeS
FROM USER_SESSION
WHERE LOGIN_DT BETWEEN @IPARAM0 and @IPARAM1
GROUP BY USER_NUM
)
--select * from LogTime
SELECT USER_LOGIN, [NO_OF_LOGINS/LOGIN_DT], [TOTAL_DURATION/LOGOUT_DT], LOCATION, DURATION
FROM
(
SELECT 1 AS Ord
,usr.USER_NUM AS UserNo
,usr.USER_LOGIN AS USER_LOGIN
,CAST(lts.LogCnt AS VARCHAR(30)) AS [NO_OF_LOGINS/LOGIN_DT]
,CAST(CAST(lts.LoggedOnTimeS/3600 AS VARCHAR) + ' Hrs ' +
CAST((lts.LoggedOnTimeS - (lts.LoggedOnTimeS/3600)*3600)/60 AS VARCHAR) + ' Mins' AS VARCHAR(30)) AS [TOTAL_DURATION/LOGOUT_DT]
,CAST('' AS VARCHAR(4000)) AS LOCATION
,CAST('' AS VARCHAR(20)) AS DURATION
FROM LogTimeSum lts
JOIN usr
ON usr.USER_NUM = lts.USER_NUM
UNION ALL
SELECT 2
,usr.USER_NUM
,''
,CONVERT(VARCHAR(30), uss.LOGIN_DT, 113)
,CONVERT(VARCHAR(30), uss.LOGOUT_DT, 113)
,uss.USER_LOCATION
,CAST(CAST(DATEDIFF(SECOND,uss.LOGIN_DT, uss.LOGOUT_DT)/3600 AS VARCHAR) + ' Hrs ' +
CAST((DATEDIFF(SECOND,uss.LOGIN_DT, uss.LOGOUT_DT) - (DATEDIFF(SECOND,uss.LOGIN_DT, uss.LOGOUT_DT)/3600)*3600)/60 AS VARCHAR) + ' Mins' AS VARCHAR(30))
FROM USER_SESSION uss
JOIN usr
ON uss.USER_NUM = usr.USER_NUM
WHERE LOGIN_DT BETWEEN @IPARAM0 and @IPARAM1
) Q
ORDER BY UserNo, Ord, [NO_OF_LOGINS/LOGIN_DT]
June 16, 2010 at 5:30 am
Thanks for your help.
I created another temporary table and dumped the result into it using cursor.
June 16, 2010 at 7:01 am
:w00t::w00t::w00t:Cursor?:w00t::w00t::w00t:
Why would you need to use a cursor here?
June 16, 2010 at 11:47 pm
As I my boss needs output the way I posted previously for report. so I took cursor to insert into another table. One row from first query and n rows from second query based on user_num/login_name. And I would like to know why time is not matching. (See attachment)
June 17, 2010 at 1:05 am
1. To achieve the required order and format of data NO CURSOR is required in your case. As shown in my examples, you just introduce the artificial "order" column which guaranties required order after the union.
Using the cursor in your case will lead the future developers (who will see that code) to call you many nasty names 😀
2. Please read carefully my previous posts. I have already warned you about potential numbers mismatches and the reasons behind them.
June 17, 2010 at 2:13 am
Yes you are right.
I didn't went through your previous post correctly.
Now got it.
June 17, 2010 at 3:48 am
Now,
You have couple of choices:
1. Calculate time of single login and then sum it.
- It will illuminate the mismatches, but look quite stupid as you will have some thing like:
SomeName Total duration 0
log1 duration: 0
log2 duration: 0
log3 duration: 0
doesn't look good
2. Show the time not like 0 min but as "less than 1 min" or "<1 Mins"
This would not require to change in current logic and will look more appropriate
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply