Solved

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

Posted on 2011-09-09
9
295 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
  • 5
  • 3
9 Comments
 
LVL 142

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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now