Base, Query, CASEWHEN coding problem

[JoeCastor]

I’m attempting to create column that shows a different result based on a date test. On my current code, I only show either ‘MM’ or ‘YY’, but would like to concat text to the end of ‘MM’ and text only instead of ‘YY’. I can’t seem to find the problem in the code and hoped you’d spot my error quickly. It is clearly somewhere in the CASEWHEN as when I remove it, and the last DATEDIFF, it works fine.
Thank you in advance,
Joe Castor

SELECT "MemberID" AS "Bage #", "LastName" || ', ' || "FirstName" AS "Name", "DateJoined" AS "Date Joined", "LastRenewalDate" AS "Last Renewal", "MemberThruDate" AS "MemberThru", "MemberType" AS "Type", "Family",
CASEWHEN( "MemberThruDate" =< "TODAY", DATEDIFF('mm', "MemberThruDate","TODAY"), DATEDIFF('yy', "MemberThruDate","TODAY")) AS "Mos Over Due" 
FROM "MemberMaster" WHERE "NotActive" <> 1 ORDER BY "LastName" ASC, "FirstName" ASC

[Ratslinger]

@JoeCastor Edited your question just to format SQL for clarity. You can do this with the preformatted text icon on the toolbar at top of question, comment or answer.

[Ratslinger]

I believe this may be what you are looking for:

 CASEWHEN("MemberThruDate" <= "TODAY",CASEWHEN(DATEDIFF('YY',"MemberThruDate", TODAY) > 0, 'YEARS TEXT',DATEDIFF('MM',"MemberThruDate", TODAY) || ' MONTHS'),'') AS "Mos Over Due"

Replace 'YEARS TEXT' with the text wanted for years and 'MONTHS' with the text wanted after the number of months.

One situation seen here is that “MemberThruDate” can be less than TODAY with less than 1 month. If you want replace months result with another test for 0 months:

CASEWHEN("MemberThruDate" <= "TODAY",CASEWHEN(DATEDIFF('YY',"MemberThruDate", TODAY) > 0, 'YEARS TEXT',CASEWHEN(DATEDIFF('MM',"MemberThruDate", TODAY) = 0, DATEDIFF('DD',"MemberThruDate", TODAY) || ' DAYS', DATEDIFF('MM',"MemberThruDate", TODAY) || ' MONTHS')),'') AS "Mos Over Due"

This last statement will give years, months or days.

Sorry for post deleting. The days portion threw me off for a bit.

[JoeCastor]

Great help. Thank you very much. This old septuagenarian is slow learning, and LO is not easy. Your help has advanced me on my project.

[Ratslinger]

@JoeCastor You are most certainly welcome. The following is not meant to confuse but hopefully to better understand. Your question really had nothing to do with LibreOffice. Quick explanation. Base is only a front end to a database. LO ships with the HSQLDB embedded DB but a user can actually use a variety of different databases with Base. SQL is the method to communicate with databases. Your query could be done without Base ever coming into play.

[Ratslinger]

Base is actually a layer between the user and the database. So when you state LO is not easy, you are actually dealing with three different elements: Database, SQL and interface (Base). This is why so many have a difficult time with Base. There is a lot of ground to cover.