Lynn Pettis (11/9/2011)
Just a shot in the dark, but did you try this:
UPDATE TELEHEALTH SET
INVOICE_COST = CONSULT_MBS_COST.MBS_ITEM_FEE * 0.50,
ITEM_STATUS_CODE = CONSULT.ITEM_STATUS_CODE
FROM
dbo.INVOICE INVOICE
INNER JOIN dbo.INVOICE_ITEM TELEHEALTH
ON INVOICE.INVOICE_ID = TELEHEALTH.INVOICE_ID
INNER JOIN dbo.MBS_ITEM MBS
ON TELEHEALTH.MBS_ITEM_NUMBER = MBS.MBS_ITEM_NUMBER AND
MBS.ITEM_TYPE_CODE = 'M' AND
MBS.TELEHEALTH_ITEM = 'Y'
INNER JOIN dbo.INVOICE_ITEM CONSULT
ON INVOICE.INVOICE_ID = CONSULT.INVOICE_ID
INNER JOIN dbo.MBS_ITEM CONSULT_MBS
ON CONSULT.MBS_ITEM_NUMBER = CONSULT_MBS.MBS_ITEM_NUMBER AND
CONSULT_MBS.ITEM_TYPE_CODE = 'M' AND
ISNULL(CONSULT_MBS.TELEHEALTH_ITEM,'N') = 'N'
INNER JOIN dbo.MBS_ITEM_COST CONSULT_MBS_COST
ON CONSULT_MBS.MBS_ITEM_NUMBER = CONSULT_MBS_COST.MBS_ITEM_NUMBER AND
CONSULT.SERVICE_DATE BETWEEN CONSULT_MBS_COST.MBS_COST_START AND
ISNULL(CONSULT_MBS_COST.MBS_COST_END, GETDATE() ) AND
CONSULT_MBS_COST.COST_TYPE_ID = 10
WHERE
INVOICE.INVOICE_DATE IS NULL
The solution given by Lynn Pettis worked for me too. But i don't see anything wrong in OP Questions(I too had made the query in same manner). Would like to know from the expert over here why does the OP Query not worked.