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
[Solved] Need to change calculations per 01/01/19
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] Need to change calculations per 01/01/19
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.
Re: Need to change calculations per 01/01/19, keeping histor
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Need to change calculations per 01/01/19, keeping histor
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.
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Need to change calculations per 01/01/19, keeping histor
I had the same thought. It took me a while to figure it out, but it worked.
Thanks, Dream
Thanks, Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.