CODEWITHSUNDEEP
sqloracle-database
Ray J Tong
Ray J Tong

Reputation: 897

SQL Query to show all results before current month

I have a table in Oracle with columns: [DATEID date, COUNT_OF_PHOTOS int] This table basically represents how many photos were uploaded per day.

I have a query that summarizes the number of photos uploaded per month:

select extract(year from dateid) as year, extract(month from dateid) as month, count(1) as Photos
from picture_table
group by extract(year from dateid), extract(month from dateid)
order by 1, 2

This does what I want, but I would like to run this query at the beginning of each month, lets say 07-02-2012, and have all data EXCLUDING the current month. How would I add a WHERE clause that ignores all entries that have a date equal to the current year+month?

Upvotes: 0

Views: 3032

Answers (2)

Tebbe
Tebbe

Reputation: 1372

To preserve any indexing strategy you may have on dateid:

select extract(year from dateid) as year, extract(month from dateid) as month, count(1) as Photos
from picture_table
WHERE (dateid < TRUNC(SYSDATE,'MM') OR dateid >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),1))
group by extract(year from dateid), extract(month from dateid)
order by 1, 2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is one way:

where to_char(dateid, 'YYYY-MM') <> to_char(sysdate, 'YYYY-MM')

Upvotes: 1

Related Questions