Solved

SQL Server MIN Funtion

Posted on 2013-12-26
6
334 Views
Last Modified: 2013-12-30
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]
0
Comment
Question by:rustypoot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39741056
The only Min() I see here is in a subquery, which is on the right side of a LEFT JOIN, which means all rows on the LEFT side (VISITPT_ALL_DIS AS DIS) will be returned in the result set.

So ... if you only want to return the one row, for starters change the LEFT JOIN to an INNER JOIN (or just JOIN).

Also, if DIS has a date that is the equivalent of MED.LastAdminDateTime, then you'll have to add that relationship to the JOIN clause, so the line below needs to change to the line below that:

             ON DIS.Account = Meds.Account

Open in new window

             ON DIS.Account = Meds.Account AND DIS.??Date = Meds.[First Abx Admin Date'] 

Open in new window

0
 
LVL 13

Expert Comment

by:AielloJ
ID: 39741751
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39742414
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
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

Author Comment

by:rustypoot
ID: 39746541
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..
0
 
LVL 32

Expert Comment

by:awking00
ID: 39746636
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.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 39746642
Since you are getting multiple rows, they are probably coming from the JOIN'd table.  So you need to ask yourself
1. Why you are including Med.EID when you are not using it anywhere, either in the SELECT or JOIN.
2. Since you are selecting DrugName, which one do you want returned if there are multiple rows with a different DrugName?  The one with the oldest LastAdminDateTime?  If so what happens if there are two with the same LastAdminDateTime?
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 64
T-SQL: problem comparing datetime 4 81
How to trim leading zeroes from values in a column 2 38
How do I partition this table on date? 5 54
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question