Counting number of occurences in a time period

Question

I have a view that has 4 columns, an order date, machine reference number, item number, and quantity. How would I generate a list of machines that have consumed an item more than once in a given time period?

For example, I have 5 cars. on car #1, I changed the spark plugs on the 21st and again on the 29th, meaning I changed them more than once in a given period (2 weeks for this example). car 3 had its plugs changed on the 1st and then on the 21st, but the previous month it had its plugs changed on the 1st and the 4th, so it shows up on the list.

order_date    machine_reference_number    item_number    quantity
-----------------------------------------------------------------
'2016-05-21'     1                          21              1
'2016-05-29'     1                          21              2
'2016-04-01'     3                          13              4
'2016-04-04'     3                          13              1
'2016-05-01'     3                          13              4
'2016-05-21'     3                          13              4

I'm fairly certain this will have to be a stored procedure that compares each row, but I was hoping there would be a way to do it in plain-jane SQL without all the loops.

Answer 1

Edited based on your sample data.

For an item number to have a total quantity of two or more in a given date range:

 Select MachineRefNo, ItemNumber, Sum(Quantity) NumberOfItems
       From Table
       Where OrderDate Between DateRangeStart And DateRangeEnd        
       Group By MachineRefNo, ItemNumber
       Having Sum(Quantity) > 1

For an item number to have two or more records in a given data range:

Select MachineRefNo, ItemNumber, Count(*) NumberOfOccurances
   From Table
   Where OrderDate Between DateRangeStart And DateRangeEnd        
   Group By MachineRefNo, ItemNumber
   Having Count(*) > 1

Answer 2

A self join will allow comparison to any other row arbitrarily related to the first.

Select
  ..
from table as a
inner join table as b
  on a.machine_reference_number = b.machine_reference_number
  and a.item_number = b.item_number
  and a.order_date <= b.order_date
  and b.order_date <= dateadd (day, 14, a.order_date);

This will allow aggreagtes in th result. A purely existential check can be performed with EXISTS() and a correlated sub query.