For example lets say we were wanting to allocate transactions to a financial year but allow the users to adjust that financial year depending on their circumstances. How could we do that.
Here we have a table called
whose structure is
pkid taxyear startdate enddate
And a table of transactions called t023finance
CREATE VIEW v0004financeyear AS SELECT a.pkid pkidt0023, a.banked, a.transactiondate, a.description, a.category, a.Direction, a.hmrcint, a.pkidt0001, a.pkidt0007, a.vamount, a.invoiceno, a.pkidt0011, a.dupdated, a.dcreated, b.pkid pkidt00165, b.taxyear from t0023finance a, t00165taxyears b where a.banked between b.startdate and b.enddate
Remarkably simple – I suspect that we might get cartesian join issues if your start date and end dates in the t00165 table overlap.