Query if a customer ID has a previous order in the last week: How To?

I am using SQLite 3.7.4 and have a table that includes:

TABLE: 'Orders'
ID     OrderDate
1234   2011-06-01 00:00:00
1245   2011-06-04 00:00:00
1234   2011-06-05 00:00:00

I'd like to be able to 开发者_StackOverflow中文版select the 'OrderDate' & 'ID' where, for a given OrderDate there is a previous order in the last 1 week.

So, for the data above:

• The first record ID 1234 has an order on 2011-06-01 00:00:00, but has none previous - so isn't selected.

• The next record ID 1245 has an order on 2011-06-04 00:00:00 but none in week prior to 2011-06-04, so not selected.

• The 3rd record ID 1234 order made on 2011-06-05 00:00:00 has a previous order on 2011-06-01, so this record is selected.

I have just about managed to get my head around using strftime('%s',OrderDate) for date differences,but can't work out how to query by taking the yyyy-mm-dd part of the OrderDate record and looking back '-7 days' to see if there are 1 or more records within that range ?

Any guideance appreciated :)

Not certain I understand, but this gives the output you're looking for, given the sample data.

sqlite> select o1.id, o1.orderdate from orders o1
   ...> inner join orders o2 on (o1.id = o2.id
   ...> and o2.orderdate >= date(o1.orderdate, '-7 day')
   ...> and o2.orderdate < o1.orderdate);
1234|2011-06-05

You should probably look at SQLite Date and Time Functions.