Trying to a Restrict Query by Max Value on Max Date

• kellycarmody

  SSC Enthusiast

  Points: 112

  More actions

  April 18, 2017 at 11:20 am

  #329571

  I already have most of the query I am working with correct

  Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.StatusFrom Clients Inner Join Status On Clients.rid = Status.ridWhere (Status.Status In (12, 19) Or Status.Status = 29) And Status.wave = 9 And Status.StatusDate = (Select Max(Status.StatusDate) From Status Where Clients.rid = Status.rid And)

  I only want results to turn up in my query for those with status 29, 19, or 12 on the most recent date, and not those with a higher value more recently i.e. 40, 42 etc. My issue is that when 40 and and 29 for example happen on the same date the person comes up in my query, even though I am only interested in that person if their most recent value is 40. So I would like to select a person in the query if their status is 29 on the most recent date, but only if 12 or 19 or 29 is their max status, not if the max status is anything higher than that. I tried this but I'm not sure of the syntax, I've been getting an error message.

  Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.StatusFrom Clients Inner Join Status On Clients.rid = Status.ridWhere (Status.Status In (12, 19) Or Status.Status = 29) And Status.wave = 9 And Status.StatusDate = (Select Max(Status.StatusDate) From Status Where Clients.rid = Status.rid And Max(Status.Status) = 29 or 19 or 12)

  Any help would be appreciated, thanks!

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  April 18, 2017 at 11:36 am

  #1938626

  Please post sample data and expected results as in the first link in my signature.

  Generally, when you are trying to pull a single record from a group of records the best approach is to use the ROW_NUMBER function in a CTE and then to select the records where that value is 1.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• kellycarmody

  SSC Enthusiast

  Points: 112

  More actions

  April 18, 2017 at 11:55 am

  #1938629

  Thank you very much for answering. I've only just started using SQL, somebody else gave me this query to work with, this is the first thing I've ever tried to do on it, so I don't know if i can manage doing all the sample data and expected results now, although I'll try next time.

  So I'm not trying to pull a single record from a group of records, sorry I realize now that I used confusing wording, I didn't mean one specific individual person. What I am trying to do is restrict my query basically to those with a value of 29 on the most recent date that a value was recorded, but only if 29 was the highest value on that day, I don't want them to show up if another value higher than 29 was also recorded on the same day.

  I think my logic would go something like this

  Select if value = 29 & date = max(latest) & value= 29= maxvalue

  or maybe

  Select value(maxdate) < maxvalue <maxvalue=29 
  I am just trying to figure out how to put that in sql.

  Thanks

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  April 18, 2017 at 12:38 pm

  #1938646

  You misunderstood.  If you define your groups on the individual persons, you will get one record FOR EACH PERSON, not one record for a specific person.

  Part of the reason that we ask for sample data and expected results is that it usually shows a much clearer picture than trying to describe it in words.  As it is, we don't have any information to even begin to help you with your problem except to say use ROW_NUMBER().

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  April 18, 2017 at 1:17 pm

  #1938657

  Because we don't have a lot of detail, I'm guessing a tad wildly here, but try this and let me know what you get:
  WITH MAX_DATES AS (

      SELECT C.rid, S.[Status], MAX(S.StatusDate) AS MAX_STATUS_DATE
    FROM Clients AS C
        INNER JOIN [Status] AS S
            ON C.rid = S.rid
    WHERE S.[Status] IN (12, 19, 29)
       AND NOT EXISTS (SELECT 1 FROM [Status] AS S2 WHERE S2.rid = C.rid AND S2.[Status] > S.[Status] AND S2.StatusDate > S.StatusDate)
    GROUP BY C.rid, S.[Status]
),
    MAX_STATUS AS (

          SELECT MD.rid, MD.MAX_STATUS_DATE, MAX(MD.[Status]) AS MAX_STATUS
        FROM MAX_DATES AS MD
        GROUP BY MD.rid, MD.MAX_STATUS_DATE
)
SELECT C.rid, C.r_fname, C.r_lname, S.[Status]
FROM MAX_STATUS AS MS
    INNER JOIN [Status] AS S
        ON MS.rid = S.rid
        AND MS.MAX_STATUS_DATE = S.StatusDate
        AND MS.MAX_STATUS = S.[Status]
    INNER JOIN Clients AS C
        ON MS.rid = C.rid
WHERE S.wave = 9;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  April 18, 2017 at 1:31 pm

  #1938663

  Please note that I've posted two corrections to the query above.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• kellycarmody

  SSC Enthusiast

  Points: 112

  More actions

  April 18, 2017 at 2:11 pm

  #1938674

  Thanks! I have been getting the same results as I was getting before. Those with a 40 and a 29 on the same day are still showing up This was the original query attached

• kellycarmody

  SSC Enthusiast

  Points: 112

  More actions

  April 18, 2017 at 2:11 pm

  #1938675

  Thanks Drew, I'll try that later!

• kellycarmody

  SSC Enthusiast

  Points: 112

  More actions

  April 18, 2017 at 5:28 pm

  #1938698

  Thanks!! I had to tweak a few things but that was my fault because I was so vague to begin with, that pretty much worked.