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
SELECT *
FROM items
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:
SELECT *
FROM items
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
end)
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 201505
. it says my SQL is valid but it returns no values yet when I look in the database there is data for that ccyymm
.
SELECT * FROM ITEMS WHERE CCYYMM = (year(now()) * 100 + month(now())) - 100
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments