SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MSSQL 2005 Snafu - Getting Latest Status

    We're doing an inmate search, and what we want to do is return back ONLY 1 PERSON, no matter how many bookings they have ( 1 INMATE can have MULTIPLE bookings) BUT we want to pull back the STATUS of their most RECENT booking to determine if they are currently incarcerated or not.

    Here's what I have

    Code:
    SELECT
    DISTINCT GJ.JacketID,
    GJ.FirstName,
    GJ.LastName,
    ( SELECT
    TOP 1 B.ReleasedFlag
    FROM Booking AS B
    WHERE B.JacketID = GJ.JacketID
    ORDER BY B.BookingDateTime DESC ) AS ReleasedFlag -- to get the latest booking
    FROM
    GlobalJacket AS GJ
    LEFT JOIN Booking AS B
    ON B.JacketID = GJ.JacketID
    WHERE
    B.BookingDateTime >= '2005-08-01 00:00:00' -- Only pull back bookings on 8/1/2005 or later
    There's other code in there, but it's running off a search inquiry. Right now, we have 576 inmates booked, and doing a name search of 'mart' (searches their first/last name) comes back with 1134 records. We know this can't be right, because, there cannot be anymore than 576 inmates, and not everyone has 'mart' somewhere in their name.

    In the end, the results with show 1 person per line, and we use the ReleasedFlag of their LATEST booking to determine if they are incarcerated, so it would look like:

    [Red Flag] Smith, Mark
    [Green Flag] Vargas, Derrick
    [Green Flag] Williams, Scott
    etc.

    What am I doing wrong??

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT GJ.JacketID
         , GJ.FirstName
         , GJ.LastName
         , M.latest_booking
         , B.ReleasedFlag
      FROM GlobalJacket AS GJ
    INNER
      JOIN ( SELECT JacketID
                  , MAX(BookingDateTime) AS latest_booking
               FROM Booking
              WHERE BookingDateTime >= '2005-08-01' 
             GROUP
                 BY JacketID ) AS M
        ON M.JacketID = GJ.JacketID
    INNER
      JOIN Booking AS B
        ON B.JacketID = GJ.JacketID
       AND B.BookingDateTime = M.latest_booking
    for each jacketID, the subquery finds the latest booking (beyond the date given)

    the result set of the subquery forms a derived table (an internal temporary table), and this table is joined to on jacketID

    then the results of that join are joined to the bookings table, such that only the row with the latest booking is matched for the join

    and the released flag is taken from that row

    simple, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Does that mean that the inner selects are constrained in their results by the outer select's WHERE clause?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in general, i think so, although in the example above, there actually is no WHERE clause in the outer SELECT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I threw all my other code in there (including the outermost select's where clause) half-expecting something to go massively wrong.

    But I did a search on 'mart' being in their names, and 174 people came back (this sounds about right!) Then I did some cross-referencing to ensure that the JacketID displayed belonged to them and bingo bango, good to go.

    Rudy, I wish we had someone with your SQL skill here at work. I'd be happy even if I had 1/10th your skill.

  6. #6
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, one snafu. Here's my code:

    Code:
                 SELECT
                    GJ.JacketID,
                    GJ.FirstName,
                    GJ.LastName,
                    GJ.DateOfBirth,
                    M.LatestBookingDateTime,
                    B.ReleasedFlag,
                    VSE.EntryValue AS NameSuffix
                FROM
                    GlobalJacket AS GJ
                LEFT JOIN JacketType AS JT
                    ON GJ.JacketType = JT.JacketType
                LEFT JOIN ValidationSetEntry AS VSE
                    ON GJ.vsNameSuffix = VSE.EntryID
                INNER JOIN ( 
                    SELECT
                        JacketID,                    
                        MAX(BookingDateTime) AS LatestBookingDateTime    
                    FROM
                        Booking
                    WHERE
                        BookingDateTime >= '2005-08-01'
                    GROUP BY
                        JacketID ) AS M
                    ON M.JacketID = GJ.JacketID
                INNER JOIN Booking AS B
                    ON B.JacketID = GJ.JacketID
                    AND B.BookingDateTime = M.LatestBookingDateTime
                WHERE
                    ( GJ.FirstName LIKE '%#FORM.txtPanelInmateSearchName#%'
                        OR
                    GJ.LastName LIKE '%#FORM.txtPanelInmateSearchName#%' )
                AND JT.JacketType = 1 -- Adult record only, no juveniles
                ORDER BY
                    GJ.LastName ASC,
                    GJ.FirstName ASC,
                    VSE.EntryValue ASC
    I noted that everyone who came back in the results showed up as being incarcerated (and this isn't true) Just to make sure, we needed the ReleasedFlag (a field in the BOOKING table) of the LATEST BOOKING that person had, but we also need to know the BOOKINGNUMBER (a field in the BOOKING table) too.

    This is the full code with the join on JACKETTYPE table in order to ensure that the only TYPE of jacket returned is an ADULT and not JUVENILE as well as the FORM.txtPanelInmateSearchName variable from ColdFusion, which is what the user searched on. So what do we need to do in order to get their latest booking's number (BOOKINGNUMBER) returned and that same booking's released flag (RELEASEDFLAG) field?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think your problems are a result of mixing LEFT JOINs and INNER JOINs

    it seems to me that you don't really want LEFT JOINs at all

    try this --
    Code:
    SELECT GJ.JacketID
         , GJ.FirstName
         , GJ.LastName
         , GJ.DateOfBirth
         , M.LatestBookingDateTime
         , B.ReleasedFlag
         , VSE.EntryValue AS NameSuffix
      FROM GlobalJacket AS GJ
    INNER
      JOIN JacketType AS JT
        ON JT.JacketType = GJ.JacketType
       AND JT.JacketType = 1 -- Adult record only, no juveniles
    INNER
      JOIN ValidationSetEntry AS VSE
        ON VSE.EntryID = GJ.vsNameSuffix
    INNER 
      JOIN ( SELECT JacketID
                  , MAX(BookingDateTime) AS LatestBookingDateTime    
               FROM Booking
              WHERE BookingDateTime >= '2005-08-01'
             GROUP 
                 BY JacketID ) AS M
        ON M.JacketID = GJ.JacketID
    INNER 
      JOIN Booking AS B
        ON B.JacketID = GJ.JacketID
       AND B.BookingDateTime = M.LatestBookingDateTime
     WHERE GJ.FirstName LIKE '%#FORM.txtPanelInmateSearchName#%'
        OR GJ.LastName LIKE '%#FORM.txtPanelInmateSearchName#%' 
    ORDER 
        BY GJ.LastName ASC
         , GJ.FirstName ASC
         , VSE.EntryValue ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hmm. The code "works", but I'm not getting back expected results. I typed in 'mart' (looking for 'mart' being part of the first/last name) and I got back 6 people, all showing red flags (incarcerated)

    Basically, we're looking for anyone who :

    1. Must be an adult (via JACKETTYPE = 1)
    2. Must be booked on or after 8/1/2005
    3. Has their FIRST or LAST NAME contain the SEARCH CRITERIA

    Though 1 person returned can have MULTIPLE BOOKINGS, we only want to know what their LATEST booking's number is (BOOKINGNUMBER), the Date/Time they were booked in (BOOKINGDATETIME) and whether they have been released or are still incarcerated (RELEASEDFLAG) on that last most particular booking.

    For example.

    We search for "Mart" and get back

    Johnson, Martin (dob: 1/2/1960) JacketID (-12345) Booking Number : 2009-00001234 (Booked on 2/1/2009) marked as incarcerated on this booking

    Johnson, Martin (dob: 1/2/1960) JacketID (-12345) Booking Number : 2008-00000032 (Booked on 1/20/2008) marked as released on this booking

    If these were the 2 of the X records returned, we know it's the same guy because aside from the same name and DOB, we can see the JacketID is the same (The unique identifier). But we see he has 2 bookings. The one he was booked on in 2008, he has been released (he did his time) But the one in 2009, he was booked in and is still incarcerated, so in the search results, we'd want only 1 record of him that says:

    Johnson, Martin (Incarcerated) JacketID: -12345, BookingNumber: 2009-00001234

    (Whent he user clicks on him, it sends them to a page with his jacketID in the URL,a nd from that jacketID it can pull the bookings and the page will let the user choose the bookingnumber and it'll load that bookingnumber's information.

    I hope I wasn't confusing.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't see how that's possible

    you can't be getting those rows back, because if it's the same jacketID, then the subquery will return only one row per jacketID

    now, it ~is~ possible to get two booking rows back from the query with the same jacketID, but this would mean that they both had the same (i.e. max) bookingdatetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here's some additional input.

    On the frontend, they call people who have not been released as ACTIVE bookings. Well, I did a search through the frontend (rather than through the SQL backend) and:

    Inactive (Released) people with MART in LAST NAME : 294
    Inactive (Released) people with MART in FIRST NAME : 159

    Total : 453

    Active (Incarcerated) people with MART in LAST NAME : 2
    Active (Incarcerated) people with MART in FIRST NAME : 4

    Total : 6

    I think that means I'm expecting a count of 6 + 453 = 459 total.


Bookmarks

Posting Permissions

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