422,764 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,764 IT Pros & Developers. It's quick & easy.

crosstab query for duration of login logout

100+
P: 171
Hi All
I have a table with field names as username, Position,login,logout.
Login, logout fields data type is date and time (dd-mm-yy hh:nn)
I wish to generate a crosstab query with a 30 minute interval as column heading (0000-0029,0030-0059,0100-0129 etc.until 2330-2359)[preferred as 15 or slots like 0000-0015,0016-0030 etc or even 10 minutes slots], position as row heading, datepart from the login also row heading and the value in each time slice (0000-0029,0030-0059,0100-0129 etc until 2330-2359) should be the username.
The duration of login logout varies. There could be a a user XXX worked on position A more than 30 minutes(as an example user XXX Position A login 01-09-17 01:38 logout 01-09-17 02:42). The crosstab result should display as 01-09-17 Position A and under column 0000-0029 the user name logged on during that period, 0030-0059 the username etc
Hope I have explained clearly and expecting a kind response from our experts
Oct 3 '17 #1
Share this Question
Share on Google+
1 Reply


100+
P: 171
Hi All
I think I will re phrase the question which will be enable me to get some help.
I have a table for users records with login logout times from certain position.
I would like to loop through this recordset and add to another table for each users time spent during each hour. The second table will have columns like user, position, and 24 other columns with header like 0000-0059, 0100-0159 etc.
SO if a user has login at 0118 and logout at 0215, the code need to add an entry for the user in the second table where column 0100-0159 42 minutes and in the 0200-0259 column with remaining minutes of 0200 hour that is 15 minutes. And if the logout was 0446 then the record should have all the minutes used in the corresponding hour. Hope I have explained. Please help
Oct 11 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.