Date based Data analysis - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Date based Data analysis

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2003-10-27 : 19:36:27

I have got a request for the management types to write a report / stored proc to analyise some data in our system.

We have a table which tracks the login logout time of a user.

CREATE TABLE [dbo].[AGENT_SESSION] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[USER_ID] [varchar] (20) COLLATE NOT NULL ,
[START_TIME] [datetime] NULL ,
[END_TIME] [datetime] NULL
) ON [PRIMARY]
GO

Primary key is the ID (i use this later to track their work). The start and end time are their login and logout times.

What the management need is to see how many people were logged in at any time during the day, more specifically every hour on the hour.

So we want data something like


Day        00:00 01:00 02:00 03:00 04:00 -----> 22:00 23:00
28/7/2003      1     2     1     3     4            1     2
29/7/2003      4     3     2     1     3            0     1

etc etc

Now i had a go at doing it but ran into the problem where the start and end date rolls over between 2 days. With my first try if the person logged in at about 23:00 and logged out at 04:00 then we missed the data after midnight :(

My current thoughts are that if the user enters a date range, i then create a temp table with one entry for every time i will want to check. eg if they enter 25/6/2003 - 30/6/2003 i would create a temp table with rows for 25/6/2003 00:00:00, 25/6/2003 01:00:00 ---> 30/6/2003 23:00:00. Then loop through the AGENT_SESSION table and where the date in the temp table is between the log in, log out times then add one to the counter for that date/time.

It would be even better if we could have it so that it doesnt just use the time on the hour but the whole hour, eg if they logged in at any time during the hour it would up the counter. I have dont know how to check that the hour range falls within the login range.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-27 : 19:49:04

What about an inequality join? Something like:

SELECT USER_ID, COUNT(*)
FROM Agent_Session t1
JOIN YourTimeRangeTable t2 on t2.checktime between t1.start_time and t1.end_time
GROUP BY USER_ID

YourTimeRangeTable could either be a real table that you populate, or it could be a derived table like is used in [url]http://www.sqlteam.com/item.asp?ItemID=3332[/url]

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

scullee
Posting Yak Master

103 Posts

Posted - 2003-10-27 : 21:05:57

Thats what im after :D

A few mods required but i will see how i go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources