Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:
select count(defendanteventpros.vbkey) as countcase from defendanteventpros
where exists (select * from defendanteventpros where eventid=2 and eventdate between '1/1/2007' and '12/31/2007')
The defendanteventpros table has a vbkey (primarykey) eventid (type of event) and an evetndate (when the event takes place).
What I'm trying to find is how many cases had 4 or more events and were received (eventid=2 from above) between 1/1/2007 and 12/31/2007.
I keep getting a big number that I know is not correct. I would appreciate any help anyone can offer! Thanks!
I'll give you one more chance to explain this clearly, otherwise I'm going to invoke the "If you can't describe it, you can't code it" rule.
The VBKey column represents the actual case number (each client has a specific case number, this is that number);
The EventNumber column represents the unique ID of an event;
The EventID column represents the type of event (ie, Received Paperwork, Paperwork filed, Drug Test Request,...);
The EventDate column just specifies the date the event took place.
The table will have multiple VBKeys b/c each case can have more than one event.