Ask Your Question

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

asked 2018-05-23 15:12:47 +0100

jbyuriko gravatar image

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. C:\fakepath\inquiryfile.odb

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-05-23 17:14:10 +0100

Ratslinger gravatar image


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

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


AND   COALESCE("CodePriceTable"."End_Date","CodePriceTable"."End_Date","CURRENT_DATE") >= "Date of Service"
edit flag offensive delete link more


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

Ratslinger gravatar imageRatslinger ( 2018-05-23 17:27:01 +0100 )edit

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.

jbyuriko gravatar imagejbyuriko ( 2018-05-24 05:45:56 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-05-24 05:47:14 +0100 )edit

answered 2018-05-23 15:23:33 +0100

Xoristzatziki gravatar image

updated 2018-05-23 16:26:40 +0100

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

edit flag offensive delete link more


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!

jbyuriko gravatar imagejbyuriko ( 2018-05-24 05:47:50 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-23 15:12:47 +0100

Seen: 47 times

Last updated: May 23 '18