Solved

Using MIN() -- unable to figure how and where to structure a subquery properly

Posted on 2011-09-09
9
301 Views
Last Modified: 2012-05-12
Hello,

A common issue I'm having is how to use the MIN() function properly in a subquery so that I only return ONE record per row/ID.

I'm trying to find the earliest claim date that a member may have been diagnosed with COPD or emphysema during a specified time period and currently have:

SELECT DISTINCT t.n, c.CLAIM_NUM, c.ICD1, MIN(c.DOS)as MinDxDate, c.ICD2, c.ICD3, c.ICD4
-- n is a unique member id
INTO #temp_pcp_claims
FROM #temp_pcp t --temp table that has member and their respective PCPs in it
inner join dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID
WHERE                                             (c.ICD1 IN('491','492', '496')
      or c.ICD2 IN('491','492', '496')
      or c.ICD3 IN('491','492', '496')
      or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
GROUP BY t.n, DOS, CLAIM_NUM, c.ICD1, c.ICD2, c.ICD3, c.ICD4
ORDER BY t.N

It seem there has to be a subquery along the lines of (SELECT MIN(c.DOS) FROM claims); however, I cannot seem to fit it around the WHERE clause without causing errors and/or logic issues.

Any help much appreciated!!!      
0
Comment
Question by:karen1974
[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
  • 5
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36510070
please check this article I wrote, it should help:
http://www.experts-exchange.com/A_3203.html
note: it's not only for MySQL, so whatch out what applies to MySQL
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 36510081
Remove DOS from your Group by.
0
 

Author Comment

by:karen1974
ID: 36510196
@ angelll -- this is a great article, I've actually read and referenced it before

@ time_cs -- removing the DOS from the GROUP BY decreases the number of records but it is still showing multiple DOS
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 15

Expert Comment

by:tim_cs
ID: 36510252
Try this out.  
;WITH CTE AS (
SELECT t.n, c.CLAIM_NUM, c.ICD1, c.DOS as MinDxDate, c.ICD2, c.ICD3, c.ICD4, ROW_NUMBER() OVER (PARTITION BY t.n ORDER BY C.DOS) RN
FROM #temp_pcp t 
inner join dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID 
WHERE                                             (c.ICD1 IN('491','492', '496') 
      or c.ICD2 IN('491','492', '496') 
      or c.ICD3 IN('491','492', '496')
      or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
ORDER BY t.N
)

SELECT n, Claim_Num, ICD1, MinDxDate, ICD2, ICD3, ICD4
INTO #temp_pcp_claims
FROM CTE
WHERE RN = 1

Open in new window

0
 

Author Comment

by:karen1974
ID: 36510369
Tim, I'm getting errors...
If I run the whole code, I get:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 125 total points
ID: 36510385
Oops, removed the group by but forgot the order by.  Remove that from inside the CTE.
0
 

Author Comment

by:karen1974
ID: 36510480
Tim, thanks -- I comment this out (below):

;WITH CTE AS (
SELECT t.n, c.CLAIM_NUM, c.ICD1, c.DOS as MinDxDate, c.ICD2, c.ICD3, c.ICD4,
      ROW_NUMBER() OVER (PARTITION BY t.n ORDER BY C.DOS) RN
FROM #temp_pcp t
inner join [nwhconn-beta].datawarehouse.dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID
WHERE  (c.ICD1 IN('491','492', '496')
      or c.ICD2 IN('491','492', '496')
      or c.ICD3 IN('491','492', '496')
      or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
--ORDER BY t.N
)

but I'm getting error about Incorrect syntax near ')'. It seems like all the parentheses are matched; sorry if I am overlooking something obvious!
0
 

Accepted Solution

by:
karen1974 earned 0 total points
ID: 36510595
My big mistake -- I hadn't run the entire program -- it works beautifully!!

Thank you very much for your assistance; much appreciation!!
0
 

Author Closing Comment

by:karen1974
ID: 36597717
Works in my code; thank you very much
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

627 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