Post reply

Help with query

  • April 8, 2005 at 3:05 pm

    #66182

    I have three tables: Invoice, InvoiceCharge, and InvoiceResponse

    Invoice

    pk name
    I1 Dan
    I2

    Rick
    I3 John

    InvoiceCharge

    pk fk_invoice chrgdate chrgtype chrgamt
    IC1 I1 1/1/05 Bill 50
    IC2

    I1

    		 1/2/05 Rebill 20

    InvoiceResponse

    pk fk_chrg respdate resptype respamt
    IR1 IC1 1/2/05 Check 30
    IR2

    IC2

    		 1/3/05 Cash 20

    and here's a table of invoice ids that will drive a subquery:

    InvoiceList

    invoicepk
    I1
    ..

     

    I need to find the charge type of the latest invoice charge and the response type of the latest response for the latest invoice charge.

    invoiceid total respamount for invoice latest charge type latest response type
    I1 50 Rebill Cash

    How can I change the following query to get the above results?

    SELECT Invoice.pk as InvoiceId,

               SUM(InvoiceResponse.respamt) as respamt

    FROM Invoice

           LEFT JOIN InvoiceCharge ON Invoice.pk = InvoiceCharge.fk_Invoice

           LEFT JOIN InvoiceResponse ON InvoiceCharge.pk = InvoiceResponse.fk_chrg

    WHERE Invoice.pk IN (select invoicepk from InvoiceList)

    Hope this is clear,

    Thanks,

    Rick

    Thanks,

    Rick Hodder

  • April 8, 2005 at 3:53 pm

    #551721

    Hi Joe,

    Thanks for the suggestions on posting, I will try to follow them on my next post: you're right, they do things much clearer

    Thanks for the query I will try it out.

    Rick

    Thanks,

    Rick Hodder

  • April 8, 2005 at 4:14 pm

    #551723

    try:

     

    SELECT Invoice.pk as InvoiceId, LastCharges.chrgtype, LastCharges.chrgamt, LastResponses.respamt, LastResponses.resptype

    FROM Invoice

           LEFT JOIN  (select fk_Invoice, chrgtype, chrgamt

               from InvoiceCharge ic1

                      where chrgdate = (select Max(chrgdate)

           from  InvoiceCharge ic2 

           where ic2.fk_invoice = ic1.fk_invoice) )  LastCharges

     ON Invoice.pk = LastCharges.fk_Invoice

           LEFT JOIN (select fk_chrg, resptype, respamt

               from InvoiceResponse ir1

                      where chrgdate = (select Max(respdate )

           from  InvoiceResponse ir2 

           where ir2.fk_invoice = ir1.fk_invoice) )  LastResponses

     ON LastCharges.pk = LastResponses.fk_chrg

    WHERE Invoice.pk IN (select invoicepk from InvoiceList)

    hth


    * Noel

  • April 8, 2005 at 5:30 pm

    #551726

    Thanks Noel!

    That's just what I was looking for!

    I'll try it out!

    Thanks!

    Rick

    Thanks,

    Rick Hodder

  • April 8, 2005 at 7:50 pm

    #551728

    Worked great, Noel - Thanks!

    Rick

    Thanks,

    Rick Hodder

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply