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

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

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

Post 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 (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
Last edited by dreamquartz on Tue Jan 01, 2019 10:26 am, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

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

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

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

Post by dreamquartz »

I had the same thought. It took me a while to figure it out, but it worked.
Thanks, Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply