CODEWITHSUNDEEP
sqloracle-database
meower
meower

Reputation: 23

SQL pull values from "last month" when dates have already been declared

i'm using oracle 11 and i need to display the "orders" from last month, where the date of each order has already been set.

tables are:
name: Store order

OrderNo  OrderDate
-------  ----------
ST1      03-MAY-12
ST2      03-APR-13
ST3      15-APR-13

so ideally, what it should return is both ST2 and ST3, as they're both april 2013 however now we're 3 days into june and there's no values entered for may 2013. so let's just ignore that.

how would i write a code that would get the "last month" from the user-inputted dates?

Upvotes: 0

Views: 116

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

This should be able to use indexes well since it does no per row calculations on the rows in orders;

WITH cte AS ( SELECT MAX(OrderDate) orderdate FROM orders )
SELECT * FROM orders o, cte
WHERE o.OrderDate >= TRUNC(cte.orderdate,'MM') 
  AND o.OrderDate < LAST_DAY(cte.orderdate)+1

An SQLfiddle to test with

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

This allows you to use indexes on orderdate.

select *
from   orders
where  orderdate >= add_months(trunc(sysdate,'MM')-1) and 
       orderdate <  trunc(sysdate,'MM')

Upvotes: 0

haki
haki

Reputation: 9759

using @Grisha's answer

select * 
from orders
where to_char(OrderDate,'MM') = to_char(add_months(sysdate,-1),'MM')

Upvotes: 1

Related Questions