where tx.audit_date between mp.effective_start_date and mp.effective_end_date
and tx.audit_date between '2010-01-01' and '2010-12-31'
group by tx.audiT_EVENT_IDENTIFIER
Can someone please confirm if I am using the count statement correctly? I am not sure if I am double counting or if my statement is correct. I just want to count the number of records for each audit_event that match my select statment.
Since you are gouping on audiT_EVENT_IDENTIFIER column so this query will give you the count of event_identifier for each values of audiT_EVENT_IDENTIFIER in year 2010. if you just want the count of all event_identifier (not classified on audiT_EVENT_IDENTIFIER level) then you should remove audiT_EVENT_IDENTIFIER column from the select list and gouping by clause should be remove.