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

    Unanswered: Calculating total events (query)

    Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:

    Code:
    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')
    having count(defendanteventpros.vbkey)>4
    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!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	count(defendanteventpros.vbkey) as countcase
    from	defendanteventpros
    where	eventid=2
    		and eventdate between '1/1/2007' and '12/31/2007')
    having	count(defendanteventpros.vbkey)>4
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Thanks. I tried your's and now I get the same number every time. If I change it to > 100, the number never changes and I know none of the clients have more than 100 events.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the DDL for your table, including unique constraints.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    here you go:

    Code:
    CREATE TABLE [dbo].[DefendantEventPros] (
    	[VBKey] [int] NOT NULL ,
    	[EventNumber] [int] IDENTITY (1, 1) NOT NULL ,
    	[EventID] [smallint] NULL ,
    	[EventDate] [datetime] NULL ,
    	[EventTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[EventComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Judge] [smallint] NULL ,
    	[ProsAtty] [smallint] NULL ,
    	[PubDefID] [smallint] NULL ,
    	[EventEndDate] [datetime] NULL ,
    	[EventEndTime] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[CourtReporterID] [smallint] NULL ,
    	[EventComplete] [smallint] NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    vbkey is simply the id of the client. eventid is the type of event being entered. Thanks!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by bla4free
    The defendanteventpros table has a vbkey (primarykey)
    Quote Originally Posted by bla4free
    [EventNumber] [int] IDENTITY (1, 1) NOT NULL ,
    Quote Originally Posted by bla4free
    vbkey is simply the id of the client.
    Excuse me?
    Quote Originally Posted by bla4free
    What I'm trying to find is how many cases had 4 or more events
    Cases? I don't see any Case column in your table....

    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by blindman
    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.

    Does this make anymore sense?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	count(*)
    from	--subquery
    		(select	VBKey
    		from	DefendantEventPros
    		group by VBKey
    		having count(*) > = 4) Subquery
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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