Counting Unique Overlapping Times with Multiple Criteria

[caddcop]

I am working with data that I am not able to modify in any way. In fact, I am only allowed Read Only Access.


The data has a UserName and a ProductName field. These are my aggregate items. Within the data, there are also fields for Start and End times which are date/time values- but I am required to round all start times down to the beginning of the hour and round all of the End times up to the end of the hour. This results in duplicate records if a user exits and restarts one of the tracked products within the same hour. In some instances, the start hours would be the same but not the end hours. In other instances the start hours would be different but the stop hours would be the same. Finally, in some instances both the start hours would be the same and both the end hours would be the same.
And then, other records might have the same products but for other users or the same user but other products.
What I need to count is the highest total of concurrent users of each product on any given day. It should only count any users use, in any given hour, as 1, even if they have multiple records for that product that overlap that hour.
I've tried using Excel for this but the amount of data is too much for Excel on my PC. Or the recalculation times are taking too long for this to be useful.
Any thought and ideas would be welcomed.
I have another thread open where I am dealing with the clock hour rounding issues, so if this seems familiar, that is probably why. But these are two different issues with the same data, so I made two threads.

[Dal Jeanis]

Okay, so the base level of your query is "Each Hour of Each day". Forget the overlapping times - they're irrelevant. You don't care how many times a user logged on or off in an hour - it's just yes or no for each UserName for each hour. So we start by creating a convenient pseudo-table of the hours we want to check.

So let's assume you can make a temporary local table tblHours for the query that includes 1 record for each hour of the day, and tblDays that includes one record per Day for the time frame in question.

tblDays
   DayTime     Date

tblHours 
   HourTime    Date/Time

tblLogs
   UserName    Text   
   ProductName Text   
   StartTime   Date/Time (includes day and hour of logon)  
   EndTime     Date/Time (includes day and hour of logoff)     

Query1:
SELECT TD.DayTime, TH.HourTime, TL.UserName, TL.ProductName
FROM tblDays AS TD, tblHours AS TH, TblLogs AS TL
WHERE (TD.DayTime + TH.HourTime) BETWEEN TL.StartTime AND TL.EndTime
GROUP BY TD.DayTime, TH.HourTime, TL.UserName, TL.ProductName

At heart, that query is a cross join of tblDays and tblHours, then joined against tblLog and deduped by hour. That gives you one de-duplicated record for each person who was logged on in each hour of the time frame. Don't worry about your boss's time rounding - it doesn't matter at all for this method, as long as the hour records you create in tblHours match up with the official start of period.

Next, we'll count them up by hour, and get the max count for each day for each product:

Query2:
   SELECT Q2.ProductName, Q1.DayTime, MAX(HourCount) AS MaxLoggedUsers
   FROM 
       (SELECT Q1.ProductName,  Q1.HourTime, Count(TL.UserName) As HourCount
        FROM Query1 AS Q1
        GROUP BY Q1.ProductName, Q1.DayTime, Q1.HourTime) AS Q2
   GROUP BY Q2.ProductName, Q1.DayTime;

And that's it.

[caddcop]

OK, Query 1 is up and running. My tblLogs is actually a query and some of my fields have different names, but I was eventually able to get it functioning.

Before I jump into Query 2, I have a question. My start and stop times are a date/time field and contain date and time data. Is there no way to group by the Date in those fields without relying on another table? I have two queries set up to generate a log file for the current month and the previous month by using month(Now()) and month(now())+1. So each month I can run these new queries and only get current data already. Not having to keep updating my localDate table would simplify things.

[Dal Jeanis]

The way I showed you is conceptually simple and will absolutely work. You are wanting slices of your data at arbitrary date/time values (the start of each hour), and you have to somehow tell access what those arbitrary values are.

There are other ways to get at the data, but none of them are as conceptually simple as the one I gave you.

For example, you could create a VBA routine that would loop through the data range and run a very similar query to find the same information. But there's no real advantage to using code to create step-by-step the same effect and result that SQL will give you in one single action.


NOTE: You might need to modify "BETWEEN TL.StartTime AND TL.EndTime" to "BETWEEN MyRoundFunction(TL.StartTime) AND MyRoundFunction(TL.EndTime)", with MyRoundFunction being some public function that returns your boss's required translation of the time.

[caddcop]

Query 2 is up and running.

I ran the query with the endtime displayed and you are correct, the end time needs to be rounded up. When I add that, does the Between wording need to change? Does Between work like "less than" and "greater than", or "less than or equal" and "greater than or equal"?

[caddcop]

I was able to add a +1 to my endtime portion of the query and it returns the hour the user exited the program, rounded to the start of the next hour, just like I need.

So my only question now is, can I get a single row for each product for each hour where usage is zero, too?

I am exporting the final query results to Excel for graphing and having no zero values is misleading in the graph.

[Dal Jeanis]

oooo - a challenge.

Yes, you just need your product table left-joined into the mix at the appropriate place...

Assuming that each product on tblProducts has a single record with a field ProductName that matches the ProductName on the log records...

Query1:
SELECT 
   TP2.ProductName, 
   TP2.DayTime, 
   TP2.HourTime, 
   TL.UserName 
FROM
   (SELECT 
       TP1.ProductName, 
       TD.DayTime, 
       TH.HourTime
     FROM 
       tblProducts AS TP1, 
       tblDays AS TD, 
       tblHours AS TH) AS TP2
 LEFT JOIN 
    tblLogs AS TL
 ON TP2.ProductName = TL.ProductName
WHERE (TP2.DayTime + TP2.HourTime) BETWEEN TL.StartTime AND TL.EndTime
GROUP BY TP2.ProductName, TP2.DayTime, TP2.HourTime, TL.UserName;

Note that the inner select - aliased TP2 - is now a true cross-join, resulting in one record with each possible combination of product name, day, and hour. When you left-join that against the tblLog, you'll get either one Query1 record for an hour with no log data, or one Query1 record per log record in that hour.

I don't think Query2 needs updated. The null record in the inner (Q2) select of Query2 should result in a Count(0) for that hour, and if no users logged on that day, that gives you a MaxLoggedUsers of zero for that product for that day.

No, actually it wasn't right in the first place. I'm sure that syntax would have thrown an error. Here's the corrected version.

Query2:
   SELECT Q2.ProductName, Q2.DayTime, MAX(Q2.HourCount) AS MaxLoggedUsers
   FROM 
       (SELECT Q1.ProductName, Q1.DayTime, Q1.HourTime, Count(Q1.UserName) As HourCount
        FROM Query1 AS Q1
        GROUP BY Q1.ProductName, Q1.DayTime, Q1.HourTime) AS Q2
   GROUP BY Q2.ProductName, Q2.DayTime;

[caddcop]

You are correct, your first Query 2 needed edits before it worked, but some of those were because some of my fields have different names. I also found that adding a Distinct modifier to the outer select really reduced the number of records. Without it I had 2837 records, with it, I had 242. Once I work out the zero usages, it will balloon up some.

This is my version of you Query 2 that I came up with.

SELECT Distinct Q2.Product, Q2.Date, Max(Q2.HourCount) AS MaxLoggedUsers
FROM [SELECT Q1.Product, Q1.Date,  Q1.Hour, Count(Q1.UserName) As HourCount
        FROM ByHourSummaryCurrentMonth AS Q1
        GROUP BY Q1.Product, Q1.Date, Q1.Hour]. AS Q2
GROUP BY Q2.Product, Q2.Date, Q2.Hour;

My SQL skills are really rusty, so your dot notations are stretching my knowledge. I was correcting your Query 2 by pure trial and error to get it functioning. Your revised Query 1 looks like another one of those that will take me a while to work through.

[Dal Jeanis]

You can't get non-distinct rows if the GROUP by is correct.

1) Get rid of the DISTINCT and get rid of Q2.Hour from the GROUP BY. That's where the duplicate rows came from. Otherwise it looks fine.

2) Never call a data field "Date" or "Hour". They are reserved words, and will come back to bite you on the butt. Yes, I realized my example field names were stupid-sounding, but they weren't reserved words.