Re: SQL puzzle

[mbsoftwaresolutions]

I think I've got it!!!  (ClaimID is unique, btw)

SELECT A1.*
FROM Test A1
WHERE A1.ClaimID = (select TOP 1 ClaimID
                                         FROM Test B1
					 WHERE A1.PCN = B1.PCN AND A1.Admit = B1.Admit AND A1.Discharge = 
B1.Discharge AND A1.ProvNum = B1.ProvNum
                                         ORDER BY PCN, Admit, Discharge, 
Provnum, ICN Desc)




On 2014-06-05 12:25, Stephen Russell wrote:
Are you only looking for a Max() for ICN, A3 being the maximum?  Your 
data
shows A2 as the data you want but your explanation of expected results 
has
the value for A3.


Select PCN, Max(ICN)  from test
group by PCN
order by PCN

as a real quick method to do this.



On Thu, Jun 5, 2014 at 10:44 AM, <
mbsoftwaresoluti...@mbsoftwaresolutions.com> wrote:

Here's the code:

CLEAR ALL
CLOSE ALL

CREATE CURSOR Test (ClaimID i, PCN i, Admit d, Disch d, Paid d, 
ProvNum
c(9), ICN c(11))

INSERT INTO Test VALUES (56, 6, DATE(2013,1,1), DATE(2013,1,11),
DATE(2013,5,1), '9', 'A1')
INSERT INTO Test VALUES (3, 6, DATE(2013,1,1), DATE(2013,1,11),
DATE(2013,5,1), '9', 'A3')
INSERT INTO Test VALUES (7, 6, DATE(2013,1,1), DATE(2013,1,11),
DATE(2013,6,1), '9', 'A2') && we want this ClaimID
INSERT INTO Test VALUES (90, 12, DATE(2014,2,1), DATE(2014,2,2),
DATE(2014,7,1), '9', 'B1')
INSERT INTO Test VALUES (81, 12, DATE(2014,3,1), DATE(2014,3,2),
DATE(2014,8,1), '9', 'C1')

A complete claim set is defined by the PCN, Admit, Disch, and ProvNum
fields.  So the first 3 records are of the same claim set.  The 4th 
and 5th
record are claimsets unto themselves.  I want a query that grabs that
ClaimID from the unique sets, so the output would be 3 for the A 
group, 90
for B group, and 81 for C group.

I'm working on this now with my approach being a join onto itself 
("FROM
Test A1 INNER JOIN Test A2") and thinking I have to use a MAX of sorts 
or
perhaps TOP 1 with ORDER BY PCN, Admit, Disch, ProvNum, ICN.

Am I on the right track?  btw...the data actually is in SQL Server, 
NOT
FOX.  I put this code together for quick testing.

Thanks,
--Mike


[excessive quoting removed by server]

_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/705c9ce2081be5dcb07041bd1ac2c...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.