My problem occurs with a specific table with 3 columns.
itemnr --item number (int) 7 chars like 1111111
ccyymm --century year month (int) 6 chars like 201605
amount --amount of the specific item for that year month combo.
Basically I want to create a table that shows the amount of the past 12 months.
I created 12 virtual tables using the following code
WHERE ccyymm = year(now())||right('00'||month(now()),2) -1
That shows me all the items with
where ccyymm = 201604
and it works perfectly.
The problem is that when the month I am subtracting is more than the current month i have to subtract 1 from the year as well so I used the following:
WHERE ccyymm = (case
when month(now()) < 12
then year(now())- 1||right('00'||month(now()),2)
else year(now())||right('00'||month(now()),2) -12
So if i want to get 12 months ago's data the month is less than 12 it just subtracts 1 from the year so basically it has to give me
. it says my SQL is valid but it returns no values yet when I look in the database there is data for that