Search for birthdays

Creating tables and queries

Search for birthdays

Postby jcs02 » Sun Jan 06, 2008 12:28 am

Hi,
i have created a simple adress database
now im looking for a function / macro that looks for the next birthdays in my database

-> search for all entries with (persons) birthday within the next 3 days


fieldname fieldtype
birthday date[date]

Regards

Juergen
jcs02
 
Posts: 3
Joined: Sun Jan 06, 2008 12:19 am

Re: search for birthdays

Postby Villeroy » Sun Jan 06, 2008 4:00 pm

Hi Jürgen,
Are we talking about an embedded database of type hsqldb? See status bar of the datebase view.
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: 26406
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: search for birthdays

Postby jcs02 » Sun Jan 06, 2008 4:20 pm

Hello Villeroy,
yes its an internal database, HSQL database engine.

greets

Juergen
jcs02
 
Posts: 3
Joined: Sun Jan 06, 2008 12:19 am

Re: search for birthdays

Postby Villeroy » Sun Jan 06, 2008 5:43 pm

Fine. Let's go. It is near impossible because the current version of hsqldb (used in OOo2.3.1) lacks some important functions for date calculation. hsqldb as a stand-alone database engine is documented here: http://hsqldb.org/web/hsqlDocsFrame.html
Hsqldb provides a function DATEDIFF returning the count of days/months/years between two dates and functions DAY, MONTH, YEAR returning the day/month/year number, and then there is a function DAYOFYEAR. But there is no function like DATEADD for adding some amount of time units to a given date.
A string value which represents some date in ISO format ('1999-12-31') can be converted to a date value. We get this year's anniversary from a given date 1999-12-31 by concatenation of 3 numbers with two dashes, utilizing functions YEAR(CURDATE()) and the concatenation operator ||:
Let "Date" be 1999-12-31 and CURDATE() = 2008-01-06
YEAR(CURDATE())||'-'||MONTH("Date")||'-'||DAY("Date") ==> string '2008-12-31'
CONVERT('2008-12-31', DATE) ==> date value 2008-12-31
DATEDIFF('dd', 2008-12-31, CURDATE()) ==> 360 days ('dd' specifies that we want the number of days)
If this year's anniversary has passed, the next anniversary will happen next year, so we have to add one year to the current year:
YEAR(CURDATE()) + CASEWHEN( DAYOFYEAR("Date")<DAYOFYEAR(CURDATE()), 1, 0 )

Having a test table "Dates" with a field "Date" I can get the next anniversary of "Date" and filter by some amount of days. Replace "Date" and "Dates" with your names of field and table, create a query in SQL view and check the "SQL" button for "direct SQL" mode (the query will be interpreted by the hsqldb engine rather than the office):
Code: Select all   Expand viewCollapse view
SELECT
   "Date" AS "Day Of Birth",
   CONVERT(
      YEAR(CURDATE())
      +CASEWHEN(
         DAYOFYEAR("Date")<DAYOFYEAR(CURDATE()),
         1,
         0
      )
      ||'-'||MONTH("Date")||'-'||DAY("Date"),
      DATE
   ) AS "Next Birthday",
   YEAR(CURDATE())
   +CASEWHEN(
      DAYOFYEAR("Date")<DAYOFYEAR(CURDATE()),
      1,
      0
      )
   -YEAR("Date") AS "Age"
FROM "Dates"
WHERE DATEDIFF('dd', CURDATE(), "Next Birthday")<60


Important Note: This method of adding years, concatenation to string and conversion back to date value works well with years and months, even with negative values and months >12, but unfortunately it does not work reliably with days. So do not use this method for calculation of day-offsets. Appropriate date/time functions are announced for future versions of hsqldb.
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: 26406
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: search for birthdays

Postby DrewJensen » Sun Jan 06, 2008 6:56 pm

You can create a query as:

Code: Select all   Expand viewCollapse view

SELECT 
"datetable".* FROM "datetable"
WHERE  DAYOFYEAR"birthday " ) >= DAYOFYEARCURRENT_DATE )
AND  
DAYOFYEAR"birthday " ) - DAYOFYEARCURRENT_DATE )  <= 3


To see how that would look in the GUI designer:
birthday_1.png
Query_designer_GUI
birthday_1.png (9.27 KiB) Viewed 3782 times


You could replace the number of days with a parameter so the query is
Code: Select all   Expand viewCollapse view

SELECT 
"datetable".* FROM "datetable"
WHERE  DAYOFYEAR"birthday " ) >= DAYOFYEARCURRENT_DATE )
AND  
DAYOFYEAR"birthday " ) - DAYOFYEARCURRENT_DATE )  <= :NumberOfDays


Now whenever the query is run a dialog box will open and allow you to enter the number of days you want to use.

HTH

Drew

 Edit: NOTE - there is a bug in OO.o 2.3.1 ( maybe older ) such that you can not turn the GUI design view off with this type of query. Not the worst bug but it is there none the less. 
Last edited by DrewJensen on Sun Jan 06, 2008 6:59 pm, edited 1 time in total.
Reason: Note about bug
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: search for birthdays

Postby Villeroy » Sun Jan 06, 2008 7:16 pm

DrewJensen wrote:You can create a query as:

Code: Select all   Expand viewCollapse view

SELECT 
"datetable".* FROM "datetable"
WHERE  DAYOFYEAR"birthday " ) >= DAYOFYEARCURRENT_DATE )
AND  
DAYOFYEAR"birthday " ) - DAYOFYEARCURRENT_DATE )  <= 3


Looks promising but fails if CURRENT_DATE is near the end of year.
Code: Select all   Expand viewCollapse view

SELECT 
"datetable".* FROM "datetable"
WHERE  DAYOFYEAR"birthday " ) >= DAYOFYEAR( {'2007-12-31'} )
AND  
DAYOFYEAR"birthday " ) - DAYOFYEAR( {'2007-12-31'} )  <= 3
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: 26406
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: search for birthdays

Postby DrewJensen » Sun Jan 06, 2008 7:25 pm

hmmm- well yes and I realized it would also fail for Leap Year babies...
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: search for birthdays

Postby jcs02 » Sat Jan 12, 2008 6:07 pm

Hi all,
thanks for your answers.
I tried a lot ... but I think the best method is to change completely to a mysql database

It should work with windows too, i test it only on some linix boxes

So, thanks again for your great help !! :D

Regards

Juergen
jcs02
 
Posts: 3
Joined: Sun Jan 06, 2008 12:19 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests