jeudi 7 janvier 2016

Group and Stuff multiple rows based on Count condition

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