Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: counting problem...

    Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:
    Code:
    SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
    FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
    WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55 
    AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9
    This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.

    now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select count(ProsAtty) AS CountOfProsAtty
      from DefendantCase 
     where StatusID=17 
       and ProsAtty=55 
       and EventID=9
       and exists 
           ( select 937
               from DefendantEventPros
              where VBKey = DefendantCase.VBKey
                and EventDate 
                    between dateadd(day,-60,getdate()) 
                        and getdate() )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I hope I don't show up in that result set

  4. #4
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by r937
    Code:
    select count(ProsAtty) AS CountOfProsAtty
      from DefendantCase 
     where StatusID=17 
       and ProsAtty=55 
       and EventID=9
       and exists 
           ( select 937
               from DefendantEventPros
              where VBKey = DefendantCase.VBKey
                and EventDate 
                    between dateadd(day,-60,getdate()) 
                        and getdate() )
    thank you! that works perfectly!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •