SQL statement not returning any values

Marnus Norval

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.

P.Salmon
SELECT * FROM ITEMS WHERE CCYYMM = (year(now()) * 100 + month(now())) - 100

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related