There was a query in the Microsoft System Center Community Forum requesting for assistance for a SQL query to list all alerts for a particular SCOM Group within a specific time frame.
I have create the query below and hopefully this will help anyone that needs something similar.
All is needed is to replace those in bold and underlined based on your needs.
,RGV.targetMonitoringObjectDisplayName [Server Name]
,RGV.SourceMonitoringObjectDisplayName [SCOM Group Name]
,ars.DWCreatedDateTime [Alert Date]
from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
left join dbo.vManagedEntity ME on ME.ManagedEntityRowId = alt.ManagedEntityRowId
left join OperationsManager.dbo.RelationshipGenericView RGV on RGV.TargetMonitoringObjectDisplayName = ME.DisplayName
where ars.DWCreatedDateTime between 'start date (mm/dd/yy)' and 'end date (mm/dd/yy)'
and ME.DisplayName in (select TargetMonitoringObjectDisplayName
AND SourceMonitoringObjectDisplayName = 'Group Name' )