Calculating and summing up datetime intervals

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)

Calculating and summing up datetime intervals

Author

Topic

enmadrid
Starting Member

5 Posts

Posted - 2009-04-14 : 18:23:39

Hello everyone. I've been lurking for a while but I'm ready to ask my first question. I'm basically wondering if i can join two queries on their row number.

I basically have a table that logs agent login and logout times. I'm looking to come up with the total login time for all agents.
Here's the table definition:

CREATE TABLE [dbo].[I3_JDM256_AS0](
[workflowname] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[campaignname] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[siteid] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[agenttime] [datetime] NOT NULL,
[agentid] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[stageid] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[callid] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[callidkey] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[propertyname] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[propertyvalue] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Here are the queries returning the login and logout times.

SELECT ROW_NUMBER() OVER (ORDER BY s.agentid, agenttime) AS rowNumber,
*
FROM dbo.I3_JDM256_AS0 s
WHERE s.agenttime BETWEEN '2009-04-01' AND '2009-04-07'
AND propertyname = 'Login'
ORDER BY s.agentid, agenttime

SELECT ROW_NUMBER() OVER (ORDER BY s.agentid, agenttime) AS rowNumber,
*
FROM dbo.I3_JDM256_AS0 s
WHERE s.agenttime BETWEEN '2009-04-01' AND '2009-04-07'
AND propertyname = 'Logout'
ORDER BY s.agentid, agenttime

The returned results

rowNumber workflowname campaignname siteid agenttime agentid stageid callid callidkey propertyname propertyvalue
1 JDM256 JDM256 CustomerSite 2009-04-01 13:02:26.000 2341 0 Login JDM256
2 JDM256 JDM256 CustomerSite 2009-04-02 13:00:42.000 2341 0 Login JDM256
3 JDM256 JDM256 CustomerSite 2009-04-06 13:00:40.000 2341 0 Login JDM256
4 JDM256 JDM256 CustomerSite 2009-04-01 16:44:35.000 2400 0 Login JDM256
5 JDM256 JDM256 CustomerSite 2009-04-02 16:43:28.000 2400 0 Login JDM256
6 JDM256 JDM256 CustomerSite 2009-04-02 16:58:19.000 2400 0 Login JDM256
7 JDM256 JDM256 CustomerSite 2009-04-03 16:42:08.000 2400 0 Login JDM256
8 JDM256 JDM256 CustomerSite 2009-04-06 16:43:47.000 2400 0 Login JDM256
9 JDM256 JDM256 CustomerSite 2009-04-06 16:47:04.000 2400 0 Login JDM256

rowNumber workflowname campaignname siteid agenttime agentid stageid callid callidkey propertyname propertyvalue
1 JDM256 JDM256 CustomerSite 2009-04-01 21:00:29.000 2341 0 Logout JDM256
2 JDM256 JDM256 CustomerSite 2009-04-02 17:59:44.000 2341 0 Logout JDM256
3 JDM256 JDM256 CustomerSite 2009-04-06 20:59:32.000 2341 0 Logout JDM256
4 JDM256 JDM256 CustomerSite 2009-04-01 21:03:46.000 2400 1001628637 100162863720090402 Logout JDM256
5 JDM256 JDM256 CustomerSite 2009-04-02 16:50:10.000 2400 1001686459 100168645920090402 Logout JDM256
6 JDM256 JDM256 CustomerSite 2009-04-02 21:03:36.000 2400 1001730178 100173017820090403 Logout JDM256
7 JDM256 JDM256 CustomerSite 2009-04-03 20:04:50.000 2400 1001806639 100180663920090404 Logout JDM256
8 JDM256 JDM256 CustomerSite 2009-04-06 16:46:57.000 2400 0 Logout JDM256
9 JDM256 JDM256 CustomerSite 2009-04-06 21:02:57.000 2400 1001970311 100197031120090407 Logout JDM256
10 JDM256 JDM256 CustomerSite 2009-04-06 20:59:35.000 2472 0 Logout JDM256

When trying to join on the row number, I get way too many results. I'm having trouble understanding why. The idea would be to then do a DATEDIFF and sum up the returned intervals.

SELECT ROW_NUMBER() OVER (ORDER BY s.agentid, s.agenttime) AS rowNumber,
*
FROM dbo.I3_JDM256_AS0 s
INNER JOIN (
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY s.agentid, agenttime) AS rowNumber,
*
FROM dbo.I3_JDM256_AS0 s
WHERE s.agenttime BETWEEN '2009-04-01' AND '2009-04-07'
AND propertyname = 'Logout'
ORDER BY s.agentid, agenttime
) logout ON rowNumber = logout.rowNumber
WHERE s.agenttime BETWEEN '2009-04-01' AND '2009-04-07'
AND s.propertyname = 'Login'
ORDER BY s.agentid, s.agenttime

Please let me know if this is the correct approach and thanks in advance for your help.

PS: I was worried as to what would happen if an agent forgot to log out. I checked for the sum of login/logout events and always come up with an even number. Also, when sport checking the queries above for a larger timeframe, the system returns a coherent login/logout time value (same day). But if you can suggesta better way to validate things (maybe a datediff < 1 day on the join...), that'd be great too

--checking sum of agent events
SELECT s.agentid, COUNT(s.agentid) AS agent_activity_events
FROM dbo.I3_JDM256_AS0 s
WHERE s.agenttime BETWEEN '2009-04-01' AND '2009-04-07'
AND propertyname IN ('Login', 'Logout')
GROUP BY s.agentid

enmadrid
Starting Member

5 Posts

Posted - 2009-04-15 : 11:53:10

A friend suggested to use two derived queries and it did the trick:

SELECT logins.rowNumber,
logins.campaignname,
logins.agentid,
logins.agenttime AS login_time,
logouts.agenttime AS logout_time,
DATEDIFF(second, logins.agenttime, logouts.agenttime) AS time_logged_in_secs
FROM ( SELECT TOP 10000
ROW_NUMBER() OVER ( ORDER BY s.agentid, agenttime ) AS rowNumber,
*
FROM dbo.I3_JDM256_AS0 s
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(10), s.agenttime, 101)) BETWEEN '2009-04-01'
AND '2009-04-07'
AND propertyname = 'Login'
ORDER BY s.agentid,
agenttime
) AS logins
INNER JOIN ( SELECT TOP 10000
ROW_NUMBER() OVER ( ORDER BY s.agentid, agenttime ) AS rowNumber,
*
FROM dbo.I3_JDM256_AS0 s
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(10), s.agenttime, 101)) BETWEEN '2009-04-01'
AND '2009-04-07'
AND propertyname = 'Logout'
ORDER BY s.agentid,
agenttime
) AS logouts ON logins.rowNumber = logouts.rowNumber
ORDER BY logins.rowNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources