Page 1 of 1

[Solved] Need to change calculations per 01/01/19

PostPosted: Sat Dec 29, 2018 7:03 am
by dreamquartz
Hi All,

Need to change a calculation by a Query per 01/01/2019, keeping history.
Our Client is using a method to calculate a ProjectNumber, based on year, service provided, and sequence number (YYxx##).
Our Client uses it to keep records, and invoicing.

The Query is based on info provided by eremmel (https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69523#p313138.

Because of the fact that both the Services and the sequence number are getting over 70% usage, it is determined that the numbering must be updated.

The calculation is to be changed from 6 digits to 10 digits, consisting of: YYYYxxx###, which is easy to create, by modifying the info provided by eremmel.
The problem is that the old calculus must be used until the end of 2018, and the new one as of 01/01/2019.
The database must be able to to show the results of the old calculus to show the historical data, and the new calculus.

I want to be able to select a date in something like a Query for 2018 and earlier and get the old results, and a date after 01/01/2019 the new results.

Dream

Re: Need to change calculations per 01/01/19, keeping histor

PostPosted: Sat Dec 29, 2018 1:59 pm
by Villeroy
You come up with this 2 days before new year's eve.
You don't tell us the databsae engine nor field types.
"getting over 70% usage" means what? Are you approaching the upper limit of 32-bit integers? Use bigint.

Re: Need to change calculations per 01/01/19, keeping histor

PostPosted: Sat Dec 29, 2018 5:46 pm
by eremmel
Assume you have a reference date called D to determine before 2019 and you have a query Q1 to give you the old format and Q2 to give the new format, you might do something like:
SELECT X.* FROM ( Q1 ) AS X WHERE YEAR(D) < 2019
UNION ALL
SELECT Y.* FROM (Q2) AS Y WHERE YEAR(D) >= 2019
Maybe you have to spell out * and play more tricks to let it work with Base.

Re: Need to change calculations per 01/01/19, keeping histor

PostPosted: Tue Jan 01, 2019 10:25 am
by dreamquartz
I had the same thought. It took me a while to figure it out, but it worked.
Thanks, Dream