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

Discuss the database features

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

Postby dreamquartz » Sat Dec 29, 2018 7:03 am

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
Last edited by dreamquartz on Tue Jan 01, 2019 10:26 am, edited 1 time in total.
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 671
Joined: Mon May 30, 2011 4:02 am

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

Postby Villeroy » Sat Dec 29, 2018 1:59 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26551
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby eremmel » Sat Dec 29, 2018 5:46 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1032
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

Postby dreamquartz » Tue Jan 01, 2019 10:25 am

I had the same thought. It took me a while to figure it out, but it worked.
Thanks, Dream
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 671
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests