Concurrent Laptops Checked Out

[kernal]

Crystal 9 connected to a mysql database.

We have 5 laptops that can be checked out and I need to find out how many are checked out concurrently. This is to determine if we need to have more (because all laptops have been checked out at the same time) or less (not all laptops have been checked out at the same time) laptops that can be checked out.

MYSQL Database

ID CHECKED_OUT CHECKED_IN LAPTOP_ID
1 01/07/2010 14:06:13 01/15/2010 15:43:57 1115
2 01/15/2010 16:35:46 01/19/2010 10:38:40 1115
3 01/19/2010 08:07:21 01/19/2010 11:31:04 1114
4 01/22/2010 09:28:36 01/25/2010 10:30:16 1115
5 01/20/2010 12:03:32 01/20/2010 12:03:35 1114
6 01/20/2010 14:11:25 01/20/2010 15:46:41 1114
7 01/21/2010 08:05:03 01/21/2010 11:11:18 1114
8 01/25/2010 08:46:03 01/25/2010 11:38:48 1114
9 01/26/2010 08:41:56 01/26/2010 09:06:19 1114
10 01/27/2010 08:13:18 01/27/2010 15:43:19 1114
11 01/27/2010 15:43:28 02/01/2010 15:54:08 1114
12 02/01/2010 15:54:15 02/02/2010 08:33:24 1114
13 02/01/2010 15:54:21 02/02/2010 08:33:29 1115
14 02/02/2010 15:39:56 02/02/2010 15:40:16 1115
15 02/04/2010 09:00:57 02/09/2010 16:42:29 1115
16 02/03/2010 08:03:31 02/03/2010 11:06:23 1114
17 02/04/2010 08:03:58 02/05/2010 08:08:11 1114
18 02/10/2010 09:49:31 02/16/2010 08:34:34 1114
19 02/10/2010 09:49:45 02/10/2010 11:31:20 1115
20 02/16/2010 08:07:20 02/17/2010 11:00:17 1115
21 02/17/2010 14:24:59 02/17/2010 16:42:25 1115
22 02/17/2010 16:43:08 02/19/2010 16:38:16 1115
23 02/24/2010 09:28:48 02/25/2010 12:31:23 1115
24 02/22/2010 09:58:37 02/25/2010 12:31:07 1114
25 07/12/2010 13:39:51 07/12/2010 13:40:49 1115
26 03/01/2010 09:17:43 03/01/2010 13:16:25 1116
27 03/01/2010 11:15:17 03/01/2010 14:55:57 1114
28 03/01/2010 16:57:28 03/02/2010 10:58:24 1116
29 03/10/2010 10:40:30 03/12/2010 08:49:26 1114
30 03/10/2010 10:41:22 03/10/2010 13:52:28 1116
31 03/17/2010 10:51:00 03/17/2010 16:58:16 1116
32 03/25/2010 13:20:18 03/26/2010 16:35:21 1114
33 03/29/2010 16:06:09 05/07/2010 09:02:27 1116
34 03/31/2010 08:02:00 03/31/2010 16:22:05 1114
35 04/01/2010 08:59:37 04/05/2010 08:02:51 1114
36 04/07/2010 13:36:03 04/08/2010 12:41:36 1114
37 04/13/2010 08:05:19 04/13/2010 16:43:51 1114
38 04/14/2010 08:03:17 04/14/2010 16:45:23 1114
39 04/14/2010 16:55:17 04/14/2010 16:56:33 1114
40 05/03/2010 08:05:14 05/04/2010 08:08:47 1114
41 05/06/2010 11:10:33 05/06/2010 13:46:20 1114
42 05/06/2010 13:46:32 05/06/2010 15:30:15 1114
43 05/11/2010 10:47:57 05/11/2010 14:15:13 1116
44 05/12/2010 16:33:18 05/17/2010 08:38:51 1116
45 05/18/2010 08:14:00 05/19/2010 14:13:30 1116
46 05/19/2010 14:14:31 05/20/2010 14:26:10 1116
47 05/20/2010 14:26:16 05/21/2010 08:23:20 1116
48 06/01/2010 09:16:20 06/02/2010 13:06:30 1116
49 06/02/2010 13:06:49 06/02/2010 15:00:30 1116
50 06/03/2010 09:34:42 06/03/2010 11:15:15 1114
51 06/03/2010 09:34:57 06/03/2010 15:04:45 1116
52 06/07/2010 08:12:13 06/07/2010 12:34:54 1116
53 06/07/2010 16:23:00 06/08/2010 09:05:24 1114
54 06/08/2010 08:23:13 06/08/2010 15:11:16 1116
55 06/08/2010 09:47:59 06/08/2010 12:46:44 1117
56 06/09/2010 08:04:13 06/09/2010 15:03:15 1116
57 06/10/2010 08:03:01 06/10/2010 15:34:46 1116
58 06/10/2010 13:48:47 06/10/2010 13:48:54 1118
59 06/10/2010 13:49:02 06/10/2010 15:31:20 1118
60 06/14/2010 11:27:30 06/14/2010 13:55:49 1116
61 06/15/2010 08:06:34 06/15/2010 15:17:40 1116
62 06/15/2010 08:15:16 06/15/2010 14:30:28 1118
63 06/15/2010 09:29:24 06/15/2010 15:17:28 1117
64 06/16/2010 08:10:38 06/16/2010 12:29:03 1117
65 06/16/2010 12:24:06 06/18/2010 13:57:02 1118
66 06/17/2010 08:06:02 06/18/2010 13:57:03 1117
67 06/17/2010 08:19:36 06/18/2010 13:57:05 1116
68 06/21/2010 08:14:18 06/21/2010 12:30:09 1117
69 06/21/2010 08:18:59 06/22/2010 09:03:22 1118
70 06/22/2010 11:09:56 06/22/2010 14:20:12 1118
71 06/24/2010 08:08:45 06/28/2010 09:13:50 1116
72 06/28/2010 09:13:58 06/29/2010 08:12:10 1116
73 06/29/2010 08:12:20 06/29/2010 13:25:01 1116
74 06/30/2010 08:11:32 06/30/2010 12:33:55 1116

Help appreciated. Thanks

 

[kernal]

Figured it out using a formula that lbass posted in a different message. Thanks lbass

Original post that I used with my fields:

I think you would need to create a table "Alldatetimes" with all possible datetimes in it for the period under consideration. Then link this to the table with the start and end times, by using a left join from {Alldatetimes.datetime} to {yourtable.startdatetime} where the join type is >= and then also create a left join from {Alldatetimes.datetime} to {yourtable.enddatetime} where the join type is <=. Then you could group on {Alldatetimes.datetime} using minute (or the interval you want) as the group interval, and then insert a distinctcount on the ID field

 

[lbass]

Gee, I didn't think of that this time around, and was trying to come up with a different solution. Glad you got it working.

-LB

 

[kernal]

you rock lbass! I use this formula a lot