dcsimg
Results 1 to 3 of 3

Thread: SQL 2008 - Query MAX on integer column and date specific

  1. #1

    Thread Starter
    Addicted Member Smartacus's Avatar
    Join Date
    Oct 2009
    Location
    Deep South, USA
    Posts
    196

    SQL 2008 - Query MAX on integer column and date specific

    I am working on a report query where I need only the highest most recent row using a date range, as well as patientid, and Type_Name for filters . The int column 'ednum' can have many rows on any particular date, i just need the row with the highest ednum for that patient in the date range.

    I am using a single table attempting to drill down to show one ednum value per date (ednum value being MAX) Here is my attempt, it runs but is not giving the MAX(ednum) value but including additional rows of the same date.

    MS SQL 2008

    Code:
    SELECT TP2.ednum, TP2.BackgroundID, TP2.Patient_No, TP2.Last_Name, TP2.Visit_Name, 
    TP2.SessionDT
    FROM  TypePatient AS TP1 INNER JOIN
          (SELECT ednum, BackgroundID, CONVERT(varchar,  DATE_, 101) AS SessionDT, Patient_No, 
              Last_Name, Visit_Name
           FROM TypePatient
           WHERE      (Visit_Name = 'Progress Note')) AS TP2  ON TP1.BackgroundID = TP2.BackgroundID AND
           TP1.ednum =
                  (SELECT    MAX(ednum) AS ednum
                  FROM        TypePatient
                  WHERE      (BackgroundID = 3304) AND (TP2.SessionDT
                  BETWEEN '09/20/2015' AND '09/26/2015') AND (Visit_Name = 'Progress Note')
                  ORDER BY TP2.SessionDT)
    GROUP BY TP2.SessionDT, TP2.ednum, TP2.BackgroundID, TP2.Patient_No, TP2.Last_Name, TP2.Visit_Name, TP2.ednum
    ***************************************************
    Smartacus comes packaged "As Is With No Warranty"

    ************* Useful Links ******************
    FAQs: Index / Database Development / .NET CodeBank /
    Before Posting Here...MSDN

    MZTools (I love this tool when using VB6 - Free) /

  2. #2

    Thread Starter
    Addicted Member Smartacus's Avatar
    Join Date
    Oct 2009
    Location
    Deep South, USA
    Posts
    196

    Re: SQL 2008 - Query MAX on integer column and date specific

    I had a suggestion to use the following but I am getting the same results. It is returning three records, two with the same date.

    Code:
    WITH X AS 
    (
        SELECT ednum
             , BackgroundID AS BID
             , Patient_No AS PatientID
             , Last_Name AS lName
             , Date_ AS SessionDT
             , ROW_NUMBER () OVER (PARTITION BY Patient_No ORDER BY ednum DESC) rn 
        FROM  dbo.TypePatient 
        WHERE Visit_Name = 'Progress Note'
        AND BackgroundID = 3304
         AND Date_ BETWEEN '20150920' AND '20150926'
         )
    SELECT * FROM X 
    --WHERE rn = 1
    ednum BID PatientID lName SessionDT rn
    258879 3304 118955X Smith 2015-09-24 11:07:58.000 1
    258863 3304 118955X Smith 2015-09-24 10:24:59.000 2
    258556 3304 118955X Smith 2015-09-23 13:02:20.000 3
    Last edited by Smartacus; Oct 5th, 2015 at 09:21 PM. Reason: added data results
    ***************************************************
    Smartacus comes packaged "As Is With No Warranty"

    ************* Useful Links ******************
    FAQs: Index / Database Development / .NET CodeBank /
    Before Posting Here...MSDN

    MZTools (I love this tool when using VB6 - Free) /

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,864

    Re: SQL 2008 - Query MAX on integer column and date specific

    The MAX for the EDNUM is simple

    Code:
    Select Max(EdNum) From dbo.TypePatient
    Highest for a date is

    Code:
    Select Max(EdNum) From dbo.TypePatient Where SessionDt=xyz
    Let's leave those simple queries for a second...

    Now we want a row for each Patient and Date - right?

    Code:
    Select Patient_No, SessionDt
       From dbo.TypePatient
       Group by Patient_No, SessionDt
    And now we want to see those rows with the highest EdNum for that patient and date - right?

    Code:
    Select Patient_No, SessionDt
       ,(Select Max(T2.EdNum) From dbo.TypePatient T2
            Where T2.SessionDt=T1.SessionDt
            and T2.Patient_No=T1.Patient_No) "MaxEdNum"
       From dbo.TypePatient T1
       Group by Patient_No, SessionDt

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width