Counting records

[Kevin123456]

Hi,

I was wondering if someone had an idea of how to tackle a problem I've got.

I've got a database that records the time that someone starts and finishes using a piece of software in each record. There is another table that records how many concurrent users can run the software. Can anyone suggest a way that I can get a report to count the maximum number of users using the piece of software during the reporting period and report anytimes that the count exceeds the number of allowed concurrent users.

Thanks

 

[kenhamady]

You would need to have two different records (StartTime and EndTime) to make this work relatively easily. Do you have one or two records per use? If you only have one, you can force them to duplicate in a report as long as you can add a small table to the database. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

One approach would be to join the table with the start and end times to itself with a condition that each record would match all other records that started before it started and ended after it started. By counting these records (and adding 1), you would get a count of how many licenses were used at each start time.

You would join the software table to the log table. Then add the log table again.

If this is not clear enough, e-mail me and I'll
send you my phone number.

Cheers,
- Ido ixm7@psu.edu

 

[Kevin123456]

There are separate fields that record both the start and end times for the usage.

 

[dgillz]

OK I have an idea here.

Lets say you want to measure this once every hour, and you are only measuring from 9am to 4pm (Theres probably no need to measure off peak hours anyway).

Create a formula field for each hour (or time period) you are measuring. The formulas will look like this:

@9am
if 9:00:00 in {Logontime} to {Logofftime} then 1 else 0

@10am
if 10:00:00 in {Logontime} to {Logofftime} then 1 else 0

etc., etc.

Then, make your report fields user, logontime, logofftime, and your time period fields. Insert a summary total by user (a user could log on and off several times during the day) and a grand total for each time period field.

The totals, while not EXACTLY a maximum of concurrent users, (which could be only last a microsecond) would be a good tool to keep a pulse on the number of users. Of course if an hour is too long you can make each "bucket" as small a period of time as you wish.

This would not count "anytime" that number of users exceeds the maximum, and I don't really know how to do that unless your time period is one second, but hopefully this will help. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[IdoMillet]

The solution I proposed would catch all max cases because
max situations must occur upon start. ixm7@psu.edu

 

[kenhamady]

Kevin,

In case your last comment was for me, the times would need to be in two different records (not fields in the same record) to use my simple approach. My thought is to sort the Start and end times into one consecutive list. Then you create a variable and as you get to each record you add 1 if it is a start, and subtract one if it is an end.

If the variable goes over a certain amount you can append the count and the date to a string variable. You can even check it against another variable to see if it is the highest so far. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[kenhamady]

Ido,

I am curious how you would implement the joins, since you can't do both a less than and a greater than at the same time between a pair of tables. Are you putting your condition somewhere else? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

Ken,

That's a Crystal Linking Expert limitation/bug,
not a SQL limitation.

You can create such a linking condition using
SQL designer or a view (query in Access) approach.

Cheers,
- Ido

ixm7@psu.edu

 

[Kevin123456]

Thanks for the advice you've all posted before. The typical structure of a record is to record the user id, the software id, the time they started using the software in the number of seconds since 1/1/1970 00:00:00 and the time they finished using the software (again in seconds since 1/1/1970 00:00:00)

Hope this clarifies things.

 

[kenhamady]

You can still use my technique if you can add a table. You can still use Ido's if you can write some SQL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

Kevin,

This SQL statement should do the trick:
--------------------------------------------
SELECT Software.Software_Name, Software_Log.StartTime, Software_Log.User, Count(*) AS total_logs_at_start_of_this_user
FROM Software_Log AS Software_Log_1, Software INNER JOIN Software_Log
ON Software.Software_ID = Software_Log.Software_ID
WHERE Software_Log_1.StartTime <= Software_Log.StartTime AND Software_Log_1.EndTime >= Software_Log.StartTime
GROUP BY Software.Software_Name, Software_Log.StartTime, Software_Log.User;
---------------------------------------------

Cheers,
- Ido ixm7@psu.edu