Looping through Stored Procedures

Question

Looping through Stored Procedures

Ceevei

Valued Member

Points: 51
More actions
February 19, 2012 at 9:09 am

#146529

Hi guys
I need assistance with following situation.
I have log in log out times of 100+ users to a system. They can log in to different channels at the same time.
I need there end to end log in log out times irrespective of Channel
Look at the below sample (after Preview this is not looking neat hence putting it as excel attachment)
I've removed the dodgy data I had in here earlier. Please see my later post to generate data. Thanks Jeff
I'm guessing this can be accomplished by Stored Proc. But I'm open to any other option if it does not hit performance.
This table is on MS SQL 2005.
Any help is greatly appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

It would be extremely beneficial to your cause if you posted the data in a more readily consumable format than an Excel spreadsheet. Please see the first link in my signature line below for how to do that. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ceevei Valued Member Points: 51 More actions · Answer 2

Thanks Jeff.

I'll be doing that really shortly. Have been working all night. Going to get some sleep.

Cheers

Ceevei Valued Member Points: 51 More actions · Answer 3

Its here!!

SET DATEFORMAT DMY

IF OBJECT_ID('#AgentResults2', 'U') IS NOT NULL DROP TABLE #AgentResults2;

CREATE TABLE #AgentResults2 (FUser_id varchar(48), FLoginDt datetime, FLogoutDt DateTime)

Insert Into #AgentResults2 (FUser_id , FLoginDt, FLogoutDt )

Select '30001','17/02/2012 8:09:23.117 AM' ,'17/02/2012 8:39:28.527 AM' UNION ALL

Select '30001','17/02/2012 8:50:26.087 AM','17/02/2012 9:31:32.040 AM' UNION ALL

Select '30001','17/02/2012 10:28:42.430 AM','17/02/2012 10:54:13.880 AM' UNION ALL

Select '30001','17/02/2012 10:59:20.567 AM','17/02/2012 12:00:36.030 PM' UNION ALL

Select '30001','17/02/2012 12:32:15.473 PM','17/02/2012 2:15:36.547 PM' UNION ALL

Select '30001','17/02/2012 12:49:48.177 PM','17/02/2012 2:10:01.097 PM' UNION ALL

Select '30001','17/02/2012 2:30:49.293 PM','17/02/2012 3:41:02.387 PM' UNION ALL

Select '30001','17/02/2012 3:44:05.800 PM','17/02/2012 4:01:33.613 PM' UNION ALL

Select '39300','17/02/2012 8:06:31.250 AM','17/02/2012 3:51:31.930 PM' UNION ALL

Select '39300','17/02/2012 10:15:08.923 AM','17/02/2012 10:21:29.833 AM' UNION ALL

Select '39363','17/02/2012 9:58:44.287 AM','17/02/2012 11:20:08.950 AM' UNION ALL

Select '39363','17/02/2012 11:20:29.203 AM','17/02/2012 1:27:36.717 PM' UNION ALL

Select '39363','17/02/2012 1:27:36.717 PM','17/02/2012 3:05:12.383 PM' UNION ALL

Select '39363','17/02/2012 3:05:31.527 PM','17/02/2012 4:57:13.733 PM' UNION ALL

Select '39363','17/02/2012 4:57:44.640 PM','17/02/2012 5:59:20.273 PM' UNION ALL

Select '39395','17/02/2012 9:37:14.353 AM','17/02/2012 10:15:52.397 AM' UNION ALL

Select '39395','17/02/2012 10:15:28.427 AM','17/02/2012 10:58:47.080 AM' UNION ALL

Select '39395','17/02/2012 10:57:03.590 AM','17/02/2012 11:53:47.933 AM' UNION ALL

Select '39395','17/02/2012 11:51:31.567 AM','17/02/2012 2:26:27.640 PM' UNION ALL

Select '39395','17/02/2012 2:31:30.247 PM','17/02/2012 4:04:44.217 PM' UNION ALL

Select '39395','17/02/2012 4:10:49.013 PM','17/02/2012 4:11:26.983 PM' UNION ALL

Select '39395','17/02/2012 4:17:16.813 PM','17/02/2012 5:55:47.187 PM' UNION ALL

Select '39395','17/02/2012 4:55:25.900 PM','17/02/2012 5:26:07.310 PM' ;

Select * from #AgentResults2;

IF OBJECT_ID('#AgentOutcome', 'U') IS NOT NULL DROP TABLE #AgentOutcome;

CREATE TABLE #AgentOutcome (FUser_id varchar(48), FLoginDt datetime, FLogoutDt DateTime)

Insert Into #AgentOutcome (FUser_id , FLoginDt, FLogoutDt )

Select '30001','17/02/2012 8:09:23.117 AM','17/02/2012 8:39:28.527 AM' UNION ALL