Jump to content
TNG Community
Sign in to follow this  
Jan Bastiaanssen

Sorting report problem

Recommended Posts

Jan Bastiaanssen

I used the Generic SQL for Notes Reports (CUSTOM Events for Individuals), from the TNG Wiki Reports – Notes by Graham Chamberlain.

It gave me the results that i wanted, but i would like it to be sorted by the dates in the Verdict-colum (which is a char-string).

Anyone an idea how to do that ? Any help would be very much appreciated, Jan

SQL: SELECT P1.personID, P1.lastname, P1.firstname, X1.note AS Verdict, E1.eventdate, E1.eventplace FROM tng_events E1 INNER JOIN tng_people P1 ON E1.gedcom = P1.gedcom AND E1.persfamID = P1.personID LEFT OUTER JOIN tng_notelinks N1 ON E1.gedcom = N1.gedcom AND E1.eventID = N1.eventID LEFT OUTER JOIN tng_xnotes X1 ON N1.gedcom = X1.gedcom AND N1.xnoteID =X1.ID WHERE E1.eventtypeID = "38" ORDER BY X1.note

Matches 1 to 33 of 33   » Comma-delimited CSV file

#

Person ID

Last Name

First Name

Verdict

eventdate

eventplace

1

I23748 

Hermus 

Antonetta 

01-02-1854, Prov. Gerechtshof Noord-Brabant, Diefstal, 1 maand 

From 7 Mar 1854 to 6 Apr 1854 

 

2

I23744 

Hermus 

Goverdina (Henrica) 

01-02-1854, Prov. Gerechtshof Noord-Brabant, Diefstal, 14 dagen 

From 6 Mar 1854 to 4 Jun 1854 

 

3

I23749 

Hermus 

Antonia 

01-02-1854, Rechtbank Breda, Diefstal, 1 maand 

From 7 Mar 1854 to 6 Apr 1854 

 

4

I23744 

Hermus 

Goverdina (Henrica) 

01-12-1851, Rechtbank Breda, Mishandeling, 14 dagen 

From 25 Dec 1851 to 29 Dec 1851 

 

5

I23744 

Hermus 

Goverdina (Henrica) 

03-05-1847, Rechtbank Breda, Diefstal van Boschgrint, 8 dagen 

From 15 May 1847 to 23 May 1847 

 

6

I24120 

Hermus 

Cornelia 

06-07-1892, Kantongerecht Zevenbergen, Stroperij van onbewerkt hout bij herhaling, 12 dagen 

From 8 Nov 1892 to 20 Nov 1892 

 

7

I23741 

Hermus 

Joannes Cornelius 

07-01-1856, Rechtbank Breda, Diefstal, 14 dagen 

From 7 Jan 1856 to 21 Jan 1856 

 

Share this post


Link to post
Share on other sites
Bernard

I would change all the dates in "Verdict" to: YY-MM-DD

Share this post


Link to post
Share on other sites
Jan Bastiaanssen

Bernard, this solution works very well, but i would rather like a solution on SQL-level so that i don't have to change all my records in Aldfaer.

Jan

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×