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

    Unanswered: semi-complex query

    Hi. I'm trying to figure out this query and I'm just having the hardest time with it. I've gotten my entire query figured out except for this last part. Basically, I have an events table (ClientEvents) which has events listed for clients. I'm trying to find out which clients have not completed their service. The begin service event is EventID=27 and the end service event is EventID=28. I'm trying to find the clients who are "missing" (well, not really missing b/c they are technically in the process of their service) EventID=28. This events table keeps a record of service so I can't really use the NOT EXISTS b/c a lot of clients have been serviced before. Does anybody have any ideas? Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you provide us with what you've tried already, some sample data and expected output?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Here's some sample data from the ClientEvents table:
    Code:
    EventNum  ClientID  EventID  EventDate
    100       44        27       8/1/2007
    101       44        28       8/3/2007
    
    107       56        27       8/5/2007
    108       56        28       8/6/2007
    109       56        27       8/9/2007
    When I run this query, just from this sample data, I should only get ClientID 56 as a result since he's missing an EventID=28. Does this make sense?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When I run this query
    What query is that?

    Also, Client 56 does has an EventID of 28...
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by bla4free
    Does this make sense?

    No

    Do you mean Client 44?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    create table #t1 (EventNum int,  ClientID int,  EventID int,  EventDate datetime)
    
    insert into #t1 select
    100  ,44  ,27  ,'8/1/2007' union all select
    101  ,44  ,28  ,'8/3/2007' union all select
    
    107  ,56  ,27  ,'8/5/2007' union all select
    108  ,56  ,28  ,'8/6/2007' union all select
    109  ,56  ,27  ,'8/9/2007'
    
    select * from #t1 a
    where a.EventID=27
      and not exists
         (select 1 from #t1 b
          where b.EventID=28
            and b.ClientID=a.ClientID
            and b.EventNum>a.EventNum
         )
    
    drop table #t1

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by georgev
    What query is that?

    Also, Client 56 does has an EventID of 28...
    Quote Originally Posted by Brett Kaiser
    No

    Do you mean Client 44?
    For every eventid of 27 (begin job), an eventid of 28 is entered to signify when the job is complete. As for my example:

    Code:
    EventNum  ClientID  EventID  EventDate
    100       44        27       8/1/2007
    101       44        28       8/3/2007
    
    107       56        27       8/5/2007
    108       56        28       8/6/2007
    109       56        27       8/9/2007
    110       56        28       8/11/2007
    ClientID 44 has a 27 and 28, so nothing is being done for this client. ClientID 56, however, is missing an end job event (EventID 28). I added the bold event to signify that ClientID 56 would have no jobs open b/c an EventID 28 was added, canceling out the EventID 27 before it.

    What I want is a list of clients who are essentially missing an EventID 28. This is how I cam up w/ ClientID 56 from before. Thanks for your help!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    pd somehow managed to read your mind and his code is correct.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can there be more than one incomplete job per client?
    Code:
    EventNum  ClientID  EventID  EventDate
    207       55        27       8/5/2007
    208       55        27       8/6/2007
    209       55        28       8/9/2007
    210       55        28       8/11/2007
    Then how do you know if 209 is the completed message for EventNum 207 or 208

    Alternate
    Code:
    select ClientID
    from #t1 a
    group by ClientID
    having count(case when EventID=27 then EventID end)
        != count(case when EventID=28 then EventID end)
    Last edited by pdreyer; 09-07-07 at 11:17. Reason: Added Alternate code

  10. #10
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by pdreyer
    Alternate
    Code:
    select ClientID
    from #t1 a
    group by ClientID
    having count(case when EventID=27 then EventID end)
        != count(case when EventID=28 then EventID end)
    THIS WORKED PERFECTLY!!! thank you sooooo much!!!!

Posting Permissions

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