Sum the Lock Time from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum the Lock Time from two tables

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2010-09-10 : 05:38:09

Hi All,

DECLARE @Lock TABLE(LogInID INT, UserName VARCHAR(50), IpAddress VARCHAR(50), LogInDate DATETIME)

INSERT INTO @Lock

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 1:05AM' UNION ALL

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:00AM' UNION ALL

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:14AM' UNION ALL

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 3:02AM' UNION ALL

SELECT 11, 'rkbabu', '111.111.111.111', 'Aug 24 2010 11:38PM'

DECLARE @UnLock TABLE(LogInID INT, UserName VARCHAR(50), IpAddress VARCHAR(50), LogInDate DATETIME)

INSERT INTO @UnLock

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 1:12AM' UNION ALL

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:03AM' UNION ALL

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:43AM' UNION ALL

SELECT 10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 3:02AM' UNION ALL

SELECT 11, 'rkbabu', '111.111.111.111', 'Aug 24 2010 11:50PM'

SELECT * FROM @Lock

SELECT * FROM @UnLock

I want to get the result as difference between 'Aug 24 2010 1:05AM' in @Lock and 'Aug 24 2010 1:12AM' in @UnLock

'Aug 24 2010 2:00AM' in @Lock and 'Aug 24 2010 2:03AM' in @UnLock and so on.

We should use group by LogInDate, UserName, IpAddress

Finnally the difference of time should be sumed for LogInDate, UserName, IpAddress

Thanks,

Babu Kumarasamy.

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 05:41:02

What should be your expected o/p?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

baburk
Posting Yak Master

108 Posts

Posted - 2010-09-10 : 06:56:36

quote:
Originally posted by Idera

What should be your expected o/p?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

00.51 rkbabu 111.111.111.111 2010-08-24 00:00:00.000

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 07:25:24

00.51 rkbabu 111.111.111.111 2010-08-24 00:00:00.000

How are you getting the red value?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 08:21:23

Except for the Logindate part below is the query


SELECT   Sum(diff), 
         username, 
         ipaddress 
FROM     (SELECT l.logindate, 
                 l.username, 
                 l.ipaddress, 
                 (Datediff(hh,l.logindate,u.logindate) * 60 + Datediff(MINUTE,l.logindate,u.logindate)%60) diff 
          FROM   (SELECT *, 
                         Row_number() 
                           OVER(ORDER BY logindate) AS lid 
                  FROM   @Lock) l 
                 INNER JOIN (SELECT *, 
                                    Row_number() 
                                      OVER(ORDER BY logindate) AS ulid 
                             FROM   @UnLock) u 
                   ON u.ulid = l.lid) t 
GROUP BY username, 
         ipaddress

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

CSears
Starting Member

39 Posts

Posted - 2010-09-10 : 23:23:38

quote:

Originally posted by Idera

Except for the Logindate part below is the query


SELECT   Sum(diff), 
         username, 
         ipaddress,
         CONVERT(VARCHAR(10), logindate, 120) 
FROM     (SELECT l.logindate, 
                 l.username, 
                 l.ipaddress, 
                 (Datediff(hh,l.logindate,u.logindate) * 60 + Datediff(MINUTE,l.logindate,u.logindate)%60) diff 
          FROM   (SELECT *, 
                         Row_number() 
                           OVER(ORDER BY logindate) AS lid 
                  FROM   @Lock) l 
                 INNER JOIN (SELECT *, 
                                    Row_number() 
                                      OVER(ORDER BY logindate) AS ulid 
                             FROM   @UnLock) u 
                   ON u.ulid = l.lid) t 
GROUP BY username, 
         ipaddress,
         CONVERT(VARCHAR(10), logindate, 120)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Bold faced sections are those that are added to include the date section of the code. This will be a text version that you will be able to convert to datetime format if required. The convert function will allow you to chop off the time portion so you are left with the ssum of lock times grouped by IP address, User, and Date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources