Help with query

• Rick Hodder

  Old Hand

  Points: 359

  More actions

  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

• Rick Hodder

  Old Hand

  Points: 359

  More actions

  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

• noeld

  SSC Guru

  Points: 96590

  More actions

  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

• Rick Hodder

  Old Hand

  Points: 359

  More actions

  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

• Rick Hodder

  Old Hand

  Points: 359

  More actions

  April 8, 2005 at 7:50 pm

  #551728

  Worked great, Noel - Thanks!

  Rick

  ---

  Thanks,

  Rick Hodder