Base: Query to reflect the price change while keeping past records with old price

[jbyuriko]

Hello.
I am trying to make my database to capture the updated price of products , i.e. reflect the changes in price but keeping the earlier records with old price at that time. The database is attached.

This is a database of healthcare services. In each transaction, we charge by codes. Each code has a price and the price can change overtime. In this file, some of codes changed the price as of 5/9/2018.

• table: MainT – has TRX ID (transaction ID) with patients name, date of services. TRX ID 1-17 are before price change, TRX 396 and 397 are after the price change.
• table: CodeT – has Code ID and Code
• table: CodePrice Table - has Code ID, price, effective date, End_ Date – the end date for the currently valid price is blank (since it is not ended yet)
• table: procedurejunkT – has TRX ID, Code ID, – this connect the transaction and the codes for each transaction
• query: code.priceQ - from “CodePrice Table”, fill the blank for ‘end date’ with “Current Date”

With these as preparation, I try to capture the price for the specific code on a specific Date of Service by conditioning ; Effective Date < Date of Service < End_Date case when the End_Date is blank, it should have [Current Date].

To execute this, I made 2 queries: PriceAppendQ and PriceAppendQ2.
PriceAppendQ refers to the table"CodePrice Table. SQL runs fine but it does not return the transactions after the price change (TRX ID 396 and 397), since the End_Date for these are blank? PriceAppend2 refers to the query: Code.PriceQ. This should be able to capture the TRX ID 396 and 397, since the End_Date is fed with Current Date, but SQL does not run.

I am puzzled here and cannot figure out what I have done wrong. It is a lengthy explanation, but if someone can kindly take a look, I’d appreciate it very much. inquiryfile.odb

[Xoristzatziki1]

Best practice is to have only a startDate for price of the service (starting from some date in the past when the software was not used) and never an endDate. Then find the MAX(startDate) where startDate <= queringDate. This way you will always get the right corresponding startDate to use.

Or using the example inquiryfile.odb

SELECT “Price” FROM “CodePriceTable”
WHERE “Code_ID” = 0
AND “Effective Date” IN
(SELECT MAX( “Effective Date” ) FROM “CodePriceTable” WHERE “Code_ID” = 0 AND “Effective Date” <= {d ‘2018-12-06’ })

gives 150

SELECT “Price” FROM “CodePriceTable”
WHERE “Code_ID” = 0
AND “Effective Date” IN
(SELECT MAX( “Effective Date” ) FROM “CodePriceTable” WHERE “Code_ID” = 0 AND “Effective Date” <= {d ‘2017-12-06’ })

gives 200

[jbyuriko]

Hi,
This SQL works, too. I realized that this is a simpler logic than what I was trying to do. I’d appreciate for your solution!

[Ratslinger]

Hello,

You can get your current PriceAppendQ query working by changing this second to last line from:

AND   "CodePriceTable"."End_Date" > "Date of Service"

To:

AND   COALESCE("CodePriceTable"."End_Date","CodePriceTable"."End_Date","CURRENT_DATE") >= "Date of Service"

[Ratslinger]

Note: You should probably also change your “Effective Date” compare from < to <=.

[jbyuriko]

Hi, Thank you for the solution. This worked fine. I have never used “COALESCE” function. I am happy to learn the new function. This means that I did not need to create an inquiry just to feed the empty 'End_Date" cells. Thank you.

[Ratslinger]

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.