Ask Your Question
0

Base, Query, CASEWHEN coding problem [closed]

asked 2017-07-18 00:10:29 +0100

Joe Castor gravatar image

updated 2017-07-18 06:48:02 +0100

Ratslinger gravatar image

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
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-07-24 03:32:33.202220

Comments

@Joe Castor 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 gravatar imageRatslinger ( 2017-07-18 06:49:56 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-07-18 03:17:15 +0100

Ratslinger gravatar image

updated 2017-07-18 03:18:16 +0100

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.

edit flag offensive delete link more

Comments

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.

Joe Castor gravatar imageJoe Castor ( 2017-07-18 14:30:33 +0100 )edit

@Joe Castor 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 gravatar imageRatslinger ( 2017-07-18 15:43:55 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-07-18 15:48:18 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-07-18 00:10:29 +0100

Seen: 49 times

Last updated: Jul 18 '17