i have a database with a Table
HoundID
JudgeID
TimeScore
Score
in this table i manually enter the information for all fields.
this query need to be able to detect each Time score with a 10 minute interval limit in other works each timescore with in 20 minutes of each other it only keeps the highest score Based on Start Time.
Sample
Start time 6:00am end time 11:00am
HoundID JudgeID TimeScore Score
333 2 9:00 35 keep and count
333 4 9:03 30 keep and do not count
333 3 9:10 25 keep and count
333 2 9:50 15 keep and count
333 9 10:00 35 keep and count
333 4 10:15 20 keep and count
333 3 10:09 25 keep and do not count
323 2 10:10 35 keep and count
343 1 10:34 20 keep and count
343 10 10:45 15 keep and count
343 2 9:00 35 keep and count
Need total for each hounded
Per each HoundID all scores much be counted if over a 10 minute time interval from each time HoundID was scored. No to scores can be within a 10 minute time interval from each other per hound ID.
For example HoundID 333
9:00 35
9:10 25
9:50 15
10:00 35
10:15 20
Total 130
SELECT A.HoundID
, A.TimeScore
, A.Score
HoundID
JudgeID
TimeScore
Score
in this table i manually enter the information for all fields.
this query need to be able to detect each Time score with a 10 minute interval limit in other works each timescore with in 20 minutes of each other it only keeps the highest score Based on Start Time.
Sample
Start time 6:00am end time 11:00am
HoundID JudgeID TimeScore Score
333 2 9:00 35 keep and count
333 4 9:03 30 keep and do not count
333 3 9:10 25 keep and count
333 2 9:50 15 keep and count
333 9 10:00 35 keep and count
333 4 10:15 20 keep and count
333 3 10:09 25 keep and do not count
323 2 10:10 35 keep and count
343 1 10:34 20 keep and count
343 10 10:45 15 keep and count
343 2 9:00 35 keep and count
Need total for each hounded
Per each HoundID all scores much be counted if over a 10 minute time interval from each time HoundID was scored. No to scores can be within a 10 minute time interval from each other per hound ID.
For example HoundID 333
9:00 35
9:10 25
9:50 15
10:00 35
10:15 20
Total 130
SELECT A.HoundID
, A.TimeScore
, A.Score
