CODEWITHSUNDEEP
sqldatetime
jumbojs
jumbojs

Reputation: 4868

SQL - Help writing query

I'm sure how to do this and was looking for some help. In SQL, I need a query that goes like this Get all people Ids who's last order date is larger then x(a predefined date). I've tried using Max() but it doesn't seem to be working right.

thanks

Upvotes: 0

Views: 118

Answers (8)

jumbojs
jumbojs

Reputation: 4868

I've gotten it to work with this: 

SELECT PeopleId FROM Orders Group By PeopleId Having Max(OrderDate)<'9/23/2009'

Upvotes: 0

JeffO
JeffO

Reputation: 8053

I'm guessing you don't know what the last order date is?

Select people_id
    , Max(Order_Date) as last_order_date
from orders_table AS O
Group By people_id
Having Max(Order_Date) > @CutOff_Date

Upvotes: 3

Mike Tunnicliffe
Mike Tunnicliffe

Reputation: 10782

SELECT PERSON_ID FROM ORDERS WHERE ORDER_DATE>DATE('2000-01-01')

Upvotes: 0

BlaM
BlaM

Reputation: 28898

That actually depends on the table structure. If there is a "Last Order Date" column in the users table, then:

SELECT UserID
   FROM Users
   WHERE LastOrderDate > 'predefined date'

If you need to find it in an "orders" table, this might be correct

SELECT DISTINCT UserID
   FROM Orders
   WHERE OrderDate > 'predefined date'

Or maybe if you need to take an user status into account, then...

SELECT DISTINCT O.UserID
   FROM Orders O
   INNER JOIN Users U ON U.UserID = O.UserID
   WHERE O.OrderDate > 'predefined date'
     AND U.UserStatus = 1

Upvotes: 1

Paul
Paul

Reputation: 5576

Simply:

select * from People where lastOrderDate > @InputDate

Upvotes: 1

JonH
JonH

Reputation: 33183

Of course this needs more info... Are you joining to an orders table? Can you provide some ddl, otherwise with what you have above its quite simple:

SELECT blah1, blah2...FROM MyTable WHERE LastOrderDate > x

No seriously without more info that is what it is ...

Upvotes: 0

Jon Seigel
Jon Seigel

Reputation: 12401

Use the WHERE clause.

MSDN: WHERE Clause

Upvotes: 0

MattH
MattH

Reputation: 4227

Something like

SELECT ID FROM PeopleTable WHERE LAST_ORDER_DATE > '01-JUN-2009'

How the dates are handled depends on your RDBMS

Upvotes: 3

Related Questions