We help IT Professionals succeed at work.

Pulling specific rows from a database

JamesNT
JamesNT asked
on
I have a table called transactions that is as follows?

ID         PatientID        BillID       Date         TransactionID       Amount
1              5                    2          1/1/2011            D                    $50
2              5                    2           2/3/2011           D                     $45
3               10                 6           1/4/2011           D                       $50
4              11                 8             2/1/2011          D                       $50
5               11                 8            2/28/2011        D                       $45
6               24                 12          1/25/2011        D                        $50

Notice that some patient/bills have more than one record, but not all.

I need to see the D record for each patient, if there is more than one, I need to see the one with the EARLIEST date.  So the result set in this case should be:

ID         PatientID        BillID       Date         TransactionID       Amount
1              5                    2          1/1/2011            D                    $50
3               10                 6           1/4/2011           D                       $50
4              11                 8             2/1/2011          D                       $50
6               24                 12          1/25/2011        D                        $50

Ideas?

JamesNT
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013

Commented:
Try this:

SELECT ID , PatientID , BillID , Min([Date]),TransactionID , Amount
FROM YourTable
WHERE TransactionID="D"
GROUP BY  ID , PatientID , BillID , TransactionID , Amount

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry - this might work better:

SELECT  PatientID , BillID , Min([Date]),TransactionID , Amount
FROM YourTable
WHERE TransactionID="D"
GROUP BY  PatientID , BillID , TransactionID , Amount

Open in new window

Database Developer
Commented:
Hi all.

Oh I do hate writing something out and then an accidental Back and it's all gone! :-s
There's nothing quite so unsatisfying as redoing something you've only just done. (Well, I suppose I can think of exceptions to that life axiom - but let's think online forum posts. ;-)

The statement will need a bit more to it yet.
The fact that the ID can't be included in the solution statement is a clue that something is amiss.  (That and the related Amount value won't necessarily be that for the Min(Date) value.)

SELECT ID, PatientID, BillID, [Date], TransactionID, Amount
FROM Transactions T
WHERE TransactionID='D'
  AND EXISTS (SELECT Null FROM Transactions T2
               WHERE T.BillID = T2.BillID AND TransactionID='D'
               GROUP BY T2.BillID
               HAVING T.Date = Min(T2.Date)
              )

Open in new window


Or ANSI joined...
SELECT ID, PatientID, BillID, [Date], TransactionID, Amount
FROM Transactions T
INNER JOIN 
    (SELECT T2.BillID, Min(T2.Date) MinDate FROM Transactions T2 
     WHERE T2.TransactionID='D' 
     GROUP BY T2.BillID
    ) X
ON T.BillID = X.BillID AND T.Date = X.MinDate 
WHERE TransactionID='D'

Open in new window



Trying both for bet performance would be advisable.
Both are assuming that the BillID is specific to a given Patient. (Which is, of course, then actually denormalised, redundant data to include the PatientID also... :-s)
If not - then you need to match on PatientID too.
(And Date is a reserved word so shouldn't be used as a field name. :-)

Cheers.
Top Expert 2014

Commented:
I don't think that will get the correct results.

Create a MinBillDate query like so:
SELECT BillID , Min([Date]) As MinOfBillDate
FROM Transactions
GROUP BY  BillID

Open in new window


Then execute the following query:
SELECT  PatientID , BillID , [Date], TransactionID , Amount
FROM TransactionsInner Join MinBillDate On Transactions.BillID = MinBillDate.BillID 
And  Transactions.Date = MinBillDate.MinOfBillDate

Open in new window


Note: Try to avoid using keywords, such as Date, as column names.

Author

Commented:
I hate to say this as I'm sure it will frustrate some, but I need the ID column to show.

Also, I can assure you all that I do not use reserved words such as date, transaction, first, last, etc. as field names in my tables.  Unfortunately, a lot of software out there does (and we are talking software that costs thousands of dollars).  I'm not the one who made this thing, I'm just the one who has to deal with it.  :(

Each patient can have many bills but each bill may have one and only one patient.

Let me know if your queries preserve the rows I need and I'll try them out.

Thank you all for your help - and for taking time out of your Sunday!

JamesNT
Leigh PurvisDatabase Developer

Commented:
Can't you just try them out?
I can't speak for everyone (though I strongly suspect the same), but I put at least some effort into matching your object names, so they should parse and execute verbatim.

Just giving them a try to determine if they work doesn't seem too much to do on spec (even just based on the fact that the ID is shown in those queries, if not the assumption that our queries will have had the effort put in to just execute as they stand).  
It's surely worth a shot and not too much trouble to go to - compared to, say, accurately writing up two (hopefully) syntactically correct queries (on a Sunday no less ;-p)

Cheers.

(P.S. Yes, they do. :-)
Top Expert 2014

Commented:
Here you go:
SELECT  ID, PatientID , BillID , [Date], TransactionID , Amount
FROM TransactionsInner Join MinBillDate On Transactions.BillID = MinBillDate.BillID 
And  Transactions.Date = MinBillDate.MinOfBillDate

Open in new window

Author

Commented:
Thank you all for your help.

JamesNT
Leigh PurvisDatabase Developer

Commented:
Glad you got a solution that worked. :-s
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry for the late post here.

Why was the question closed like this?

From testing this in Access 2010, Leigh's first query in http:#a37234364 was syntactically perfect and produced the desired results -- exactly as shown in the original post, right off the bat with no modification.

The Accepted Answer doesn't work - syntactical issues.  The problems with it are straight forward to fix, but it seems that the first correct answer (http:#a37234364)  should have been awarded the points.

Author

Commented:
mbizup,

The question was closed like this because it solved MY problem.  Yes, I did have to fix a few trivial (at least to me) syntactical issues but it worked - FOR ME.  And, it was the first answer I got to.  I didn't try all the other answers because I just didn't have the time.  I needed the report that uses this query done before close of business Monday (one of those difficult clients we all know and love), which is why I offered 500 points so people would put time in on Sunday.  Therefore, spending a few hours trying out everyone's answer wasn't ever on the table.  

This situation is truly a far cry from ideal.  I shouldn't have clients calling me at 7pm on Friday demanding a complicated report by the following Monday thereby ruining my weekend.  You guys shouldn't have to worry about posters that don't give your answers a fair shake.  But then again, when we all signed up to work in IT/Software Development, we certainly knew what we were getting ourselves into.  At least I hope we did.

Thank you for your time and consideration.

JamesNT
Top Expert 2014

Commented:
LPurvis really should have received the accepted answer here - I've asked a Mod to fix it.

When I commented "I don't think that will get the correct results.", I was referring to the mbizup comments.  The http:#37234364 solution comment arrived nine minutes before my comment and takes the same approach as my SQL.  I broke up my solution into two queries and LPurvis included the second Select statement inside his From clause.

Author

Commented:
If everyone is in consensus, then the decision to change points is fine by me.

We should, now that I think about it, consider the fact that others will be seeing this post six months from now and may not have anywhere near the skill we all have and may wind up being further confused.

Thank you all for clearing this up.  

JamesNT

Author

Commented:
It's understandable that things can get hectic when you have demanding clients and stressful deadlines.  If you don't have the time to test all responses to your questions before meeting a deadline, you don't have to close it immediately.  A good alternative is to post a comment to that effect and then return to your question after your deadline - when you have more time and are under less pressure.  That would let you close the question when you are better situated to test the comments posted.

And if that is simply not feasible for whatever reason, you can also use the Request Attention button and ask for a technical review of your question.    

_aliass99 your comment was very fair and balanced, thank you for that.

I honestly wish I had thought of the two suggestions you mentioned above.  I'll be certain to for future questions, rest assured.

Again, thank you all for your help.  I'm glad we got this resolved to everyone's satisfaction.  

I hope and pray none of you have crazy deadlines like the ones I sometimes get.  It's just too easy to sometimes get "tunnel vision" and let other things slip through.

JamesNT

Author

Commented:
Last, and certainly not least, my apologies to mbizup.  mbizup did not deserve the attitude of the comment I made to her.  Yes, I was under some pressure at the time, but that doesn't matter.  Common Courtesy is never a waste of time.

JamesNT
Top Expert 2014

Commented:
>>Common Courtesy is never a waste of time
amen