Trying to create a dynamic view

Databases
1

Hi,
Using MariaDb on xojo cloud. This is the query for the view:

SELECT DISTINCT
	tblpeoplechapterofficers.PersonID AS PersonID, 
	tblpeoplechapterofficers.Term AS Term, 
	tblpeoplechapterofficers.Removed AS Removed, 
	tblpeoplechapterofficers.Pending AS Pending
FROM
	tblpeoplechapterofficers
WHERE
	(
		tblpeoplechapterofficers.Term = '2021-2022'
	)

Problem is Starting July 1 the where term needs to change to ‘2022-2023’

I created Sql to do that:

SELECT
	Concat(
		IF
			(
				MONTH ( CURRENT_DATE ) > 6,   
				YEAR ( CURRENT_DATE )+1,
			YEAR ( CURRENT_DATE )-1),
		"-",
		IF
			(
				MONTH ( CURRENT_DATE ) < 7,    
				YEAR ( CURRENT_DATE )+ 1,
			YEAR ( CURRENT_DATE ) + 2 
	))

The problem is putting that code in the where doesn’t seem to work.
I thought of adding a function but am having trouble with the syntax.

What’s the solution.
TIA

2

Try turning ‘2021-2022’ into two fields

Term = “2021-2022”
Date1 = "1 JUL " + left(term,4)
Date2 = "30 JUN " + right(term,4)

For that term you get 1 JUL 2021 and 30 JUN 2022

Your original query can be something like this:

SELECT DISTINCT
 PersonID, 
Term, 
Removed, 
 Pending
FROM
	tblpeoplechapterofficers
WHERE
     Today  BETWEEN  ( "1 JUL " + left(term,4)  , "30 JUN " + right(term,4))

(note… I do not know how to obtain ‘today’ in this RDMBS, or the correct syntax for whatever ‘between’ looks like.
But hopefully you do.
So I have used ‘Today’ where I might have used GetDate() in SQL Server, or SYSDATE in Oracle)

3

The field Term is a VarChar not a date.

4

Have you tried HAVING instead of WHERE?

5

I did try having, maybe I didn’t use correctly though. How would I use it.

6

Got the solution:

SELECT DISTINCT
	tblpeoplechapterofficers.PersonID AS PersonID, 
	tblpeoplechapterofficers.Term AS Term, 
	tblpeoplechapterofficers.Removed AS Removed, 
	tblpeoplechapterofficers.Pending AS Pending
FROM
	tblpeoplechapterofficers
WHERE
	(
		tblpeoplechapterofficers.Term = (SELECT
	Concat(
		IF
			(
				MONTH ( CURRENT_DATE ) > 6,   
				YEAR ( CURRENT_DATE ),
			YEAR ( CURRENT_DATE )-1),
		"-",
		IF
			(
				MONTH ( CURRENT_DATE ) > 6,    
				YEAR ( CURRENT_DATE )+ 1,
			YEAR ( CURRENT_DATE )  
	)))
	)

Damn Parens!

7

RDBMS all have something that converts a varchar to a date, often automatically.
Pleased that your own solution works.