Marnus Norval Marnus Norval - 6 months ago 10
SQL Question

SQL statement not returning any values

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
.

Answer
SELECT * FROM ITEMS WHERE CCYYMM = (year(now()) * 100 + month(now())) - 100
Comments