March 15, 2014 at 7:24 am
hi all,
please assist in the below
i've the below table for the attendance log and I need the data to be as the next table, how can I use crrostab for this?
log_id__ date __ time __ reader_id __ user_id
1 __ 05/03/2014 __ 08:28:32 __ in_b __ 1350
2 __ 05/03/2014 __ 16:29:07 __ out_a __ 1350
3 __ 05/03/2014 __ 08:30:48 __ in_a __ 1375
4 __ 05/03/2014 __ 08:58:40 __ out_a __ 1375
5 __ 05/03/2014 __ 08:59:10 __ in_b __ 1375
6 __ 05/03/2014 __ 11:08:23 __ out_a __ 1375
7 __ 05/03/2014 __ 11:09:13 __ in_a __ 1375
8 __ 05/03/2014 __ 11:10:49 __ out_a __ 1375
9 __ 05/03/2014 __ 11:56:41 __ in_a __ 1375
10 __ 05/03/2014 __ 12:00:04 __ out_b __ 1375
11 __ 05/03/2014 __ 12:10:27 __ in_b __ 1375
12 __ 05/03/2014 __ 12:10:53 __ out_a __ 1375
13 __ 05/03/2014 __ 12:17:50 __ in_a __ 1375
14 __ 05/03/2014 __ 12:33:19 __ in_a __ 1375
15 __ 05/03/2014 __ 12:36:57 __ out_a __ 1375
16 __ 05/03/2014 __ 12:38:17 __ in_a __ 1375
17 __ 05/03/2014 __ 12:40:17 __ out_b __ 1375
18 __ 05/03/2014 __ 14:12:22 __ in_b __ 1375
19 __ 05/03/2014 __ 16:30:35 __ out_a __ 1375
20 __ 05/03/2014 __ 08:23:23 __ in_b __ 1377
21 __ 05/03/2014 __ 16:23:32 __ out_b __ 1377
I need the result to be as follow (by geting the defference between the first time and the last time for the same day)
user_id __ 01-Mar __ 02-Mar __ 03-Mar __ 04-Mar __ 05-Mar
1350 __ 8
1359 __ 8
1375 __ 6
March 17, 2014 at 12:37 am
You'll get a lot more responses if you make it easy for people to recreate your sample data easily. Here's an example:
SELECT '1' AS log_id
,'05/03/2014' AS inOutDate
, '08:28:32' AS inOutTime
,'in_b' AS reader_id
,'1350' AS userID
UNION ALL
SELECT '2','05/03/2014', '16:29:07','out_a','1350'
UNION ALL
SELECT '3','05/03/2014', '08:30:48','in_a','1375'
UNION ALL
SELECT '4','05/03/2014', '08:58:40','out_a','1375'
UNION ALL
SELECT '5','05/03/2014', '08:59:10','in_b','1375'
UNION ALL
SELECT '6','05/03/2014', '11:08:23','out_a','1375'
UNION ALL
SELECT '7','05/03/2014', '11:09:13','in_a','1375'
UNION ALL
SELECT '8','05/03/2014', '11:10:49','out_a','1375'
UNION ALL
SELECT '9','05/03/2014', '11:56:41','in_a','1375'
UNION ALL
SELECT '10','05/03/2014', '12:00:04','out_b','1375'
UNION ALL
SELECT '11','05/03/2014', '12:10:27','in_b','1375'
UNION ALL
SELECT '12','05/03/2014', '12:10:53','out_a','1375'
UNION ALL
SELECT '13','05/03/2014', '12:17:50','in_a','1375'
UNION ALL
SELECT '14','05/03/2014', '12:33:19','in_a','1375'
UNION ALL
SELECT '15','05/03/2014', '12:36:57','out_a','1375'
UNION ALL
SELECT '16','05/03/2014', '12:38:17','in_a','1375'
UNION ALL
SELECT '17','05/03/2014', '12:40:17','out_b','1375'
UNION ALL
SELECT '18','05/03/2014', '14:12:22','in_b','1375'
UNION ALL
SELECT '19','05/03/2014', '16:30:35','out_a','1375'
UNION ALL
SELECT '20','05/03/2014', '08:23:23','in_b','1377'
UNION ALL
SELECT '21','05/03/2014', '16:23:32','out_b','1377'
March 17, 2014 at 12:59 am
Thanks a lot "Mr or Mrs. 500", appreciated.:blush:
March 17, 2014 at 7:48 am
As pietlinden stated you need to provide your sample data in a consumable format. But you also need to provide your results in a meaningful way. You have listed three user_ids in your sample output but one of them is not even in your sample data.
I need the result to be as follow (by geting the defference between the first time and the last time for the same day)
It looks like maybe you have broken date and time into 2 columns. I would have kept them together but that is another discussion. What do you mean by getting the difference? In hours? Minutes? It looks like maybe you want hours? How do you define that? Your first sample starts at 8:28 and ends at 16:29. That is just over 8 hours. What if the value were reversed (8:29 - 16:28)? Do you now want to return 7?
Most likely what you want to do here is leverage a calendar table (or maybe a tally table) and then cross tab (maybe even a dynamic cross tab depending).
More than anything if you want us to help you, you have to help us first by posting details about what you really want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply