asked on

SQL Server MIN Funtion

Hi,

I have the SQL below. I am trying to get only one row with MiniMum Date (First Med Given Date and time). But it is returning multiple rows. Therefore, I am using Top 1 Function. Is there any other way to do this without Top Funtion? Please advise. Thanks

SELECT TOP 1
            DIS.EID,
            DIS.Institution,
            DIS.MRN,
            DIS.Account,
            DIS.AdmitDateTime,
            DIS.ERAdmitDtTm AS 'ED Received Date Time',
            Meds.[First Abx Admin Date],
            Meds.DrugName AS 'Antibiotic Name',
            (CASE
                  WHEN (Meds.[First Abx Admin Date]) <= dateadd(hh,3, (CASE WHEN DIS.ERAdmitDtTm IS NULL THEN Dis.AdmitDateTime ELSE DIS.ERAdmitDtTm END))
                  THEN 'Y'
                  Else 'N'
            END) as ArrivalAbx
FROM
            VISITPT_ALL_DIS AS DIS
            LEFT OUTER JOIN
                  (      SELECT
                              Med.EID,
                              Med.Account,
                              Med.DrugName,
                              MIN(MED.LastAdminDateTime)AS 'First Abx Admin Date'
                        FROM
                              MedAdminMAR_All_Adm AS MED
                        WHERE
                              Med.DrugID IN ( 'ACYC500I','AMIK500I','AMPH50I','AMPH50IL')
                        GROUP BY
                              Med.EID,
                              Med.Account,
                              Med.AdmitDateTime,
                              Med.DrugName
                        )Meds
            ON DIS.Account = Meds.Account
WHERE
      DIS.PatientType = 'IN'
      AND DIS.AccountStatus = 'DIS'
ORDER BY
      DIS.Institution,
      DIS.MRN,
      DIS.Account,
      DIS.AdmitDateTime ,
      Meds.[First Abx Admin Date]

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AielloJ

rustypoot

Without knowing more about your data model, it appears the root cause of the problem is a lack of the proper clauses in the WHERE clause. I'm assuming 'Account' is the patient you are trying to get the one row for.

Since 'Account' is not in the WHERE clause, the first row for some random patient is probably being returned.

Best regards,

AielloJ

awking00

select whatever from
(select whatever, row_number() over (partition by eid order by [first med given date and time] desc) rn
from yourtablejoin)
where rn = 1

rustypoot

ASKER

Jim,

Thanks for the reply. But even if I change the JOIN to INNER JOIN, I get same # of rows. Also, there is no equivalent date to join on..

awking00

Can you provide some relevant sample data (i.e. eliminate the extraneous fields and only include data meeting your filtering requirements other than the dates in question) and your expected output.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial