Query Design

  • Thread starter Thread starter Mathew
  • Start date Start date
M

Mathew

Hi~

Below is a sample of my table structure and data:

GroupName Dt_time Score
Group1 04/01/2004 12:00PM 61
Group1 04/01/2004 1:00PM 49
Group1 04/01/2004 2:00PM 37
Group1 04/01/2004 4:00PM 58
Group1 04/01/2004 6:00PM 62
Group2 04/01/2004 12:00PM 69
Group2 04/01/2004 1:00PM 71
Group2 04/01/2004 3:00PM 49
Group2 04/01/2004 7:00PM 61
Group2 04/01/2004 10:00PM 68
Group3 04/01/2004 4:00PM 69
Group3 04/01/2004 5:00PM 73
Group3 04/01/2004 11:00PM 81

I want to run a query that will tell me the total amount
of time that a particular group runs a score lower than
60. The results would look like below:

Group1 5 Hours (or 300 minutes)
Group2 4 Hours (or 240 minutes)

(Group3 would not show up because they do not have a score
below 60)

Each group may have as many as 70-100 entries that spans
over a period of days ... Does anyone have any suggestions
as to how to approach this?

Any help would be much appreciated.

Mathew
 
This is not fully tested, but with the data you gave us, you could try an SQL
statement that looks like the following.

SELECT A.[Group], Sum(DateDiff("h",.[Dt_time],[A].[DT_Time])) AS DiffHours
FROM Source AS A LEFT JOIN Source AS B ON A.Group = B.Group
WHERE A.DT_Time=(Select Min(Dt_Time)
FROM Source
WHERE Dt_Time > B.Dt_Time AND [Group] = B.[Group])
AND B.Score<60
GROUP BY A.Group

Replace "Source" with the name of your table.
 
Back
Top