I have a script that runs every 10 minutes and returns table with events from past 24 hours (marked by the script run time)
ID Name TimeOfEvent EventCategory TeamColor
1 Verlene Bucy 2015-01-30 09:10:00.000 1 Blue
2 Geneva Rendon 2015-01-30 09:20:00.000 2 Blue
3 Juliane Hartwig 2015-01-30 09:25:00.000 3 Blue
4 Vina Dutton 2015-01-30 12:55:00.000 2 Red
5 Cristin Lewis 2015-01-30 15:50:00.000 2 Red
6 Reiko Cushman 2015-01-30 17:10:00.000 1 Red
7 Mallie Temme 2015-01-30 18:35:00.000 3 Blue
8 Keshia Seip 2015-01-30 19:55:00.000 2 Blue
9 Rosalia Maher 2015-01-30 20:35:00.000 3 Red
10 Keven Gabel 2015-01-30 21:25:00.000 3 Red
Now I'd like to select two groups of Names based on these conditions:
1) Select Names from same EventCategory having 4 or more records in past 24 hours.
2) Select Names from same EventCategory and same TeamColor having 2 or more records in past 1 hour.
So my result would be:
4+per24h: Geneva Rendon, Vina Dutton, Cristin Lewis, Keshia Seip EventCategory = 2
4+per24h: Juliane Hartwig, Mallie Temme, Rosalia Maher, Keven Gabel EventCategory = 3
2+per1h: Rosalia Maher, Keven Gabel EventCategory = 3, TeamColor = Red
For the first one, I have written this:
SELECT mt.EventCategory, MAX(mt.[name]), MAX(mt.TimeOfEvent), MAX(mt.TeamColor)
FROM #mytable mt
GROUP BY mt.EventCategory
HAVING COUNT(mt.EventCategory) >= 4
because I don't care for the actual time as long as it's in the past 24 hours (and it always is), but I have trouble stuffing the names in one row.
The second part, I have no idea how to do. Because the results need to have both same EventCategory and TeamColor and also be limited by the one hour bracket.
Aucun commentaire:
Enregistrer un commentaire