Get top one record of same FK on date difference.

Question

I need to get top 1 record from same customer inserted within one hour. If record is inserted after one hour then don't need that one. Please see following table. This is just a sample of 1000s of records. I am using SQL Server 2005.

Answer 1

select Top 1 * 
from (select orderid, customerid, orderdate, parentorderid from customer where customerid=@customerid)
where DATEDIFF(mi, orderdate, GETDATE()) > 60

order by orderdate

Note I have used a sub query here to filter on the customerid first so that you get better performance. You should try to avoid using functions (DATEDIFF) in a restriction clause over large sets of date

Answer 2

Considering the following table:

CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [OrderDate] [datetime] NULL,
     CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC
    )
)

the following statement shows the last order placed by a customer within the time frame followed by the other orders.

;With OrderList As
(
Select Top 100 Percent * From dbo.Orders
Where OrderDate >= DateAdd(hh, -1, GetDate())
Order By OrderDate Desc
)
Select 'First' As DataType, 
    CustomerID, Min(OrderID) As OrderID, Min(OrderDate) As OrderDate
From OrderList
Group By CustomerID
Union All
Select 'Second' As DataType,
    CustomerID, OrderID, OrderDate
From OrderList
Where OrderID Not In
(
    Select Min(OrderID) As OrderID
    From OrderList
    Group By CustomerID
)
--Union All
--Select 'Raw' As DataType, 
--    CustomerID, OrderID, OrderDate
--From Orders

The last part is commented out as I used it to test whether I actually got the right rows.

In short the With statement limits the orders from the table to the ones placed within the last hour based on the current system date and orders them by order date. The first statement (Select 'First') then extracts just the first orders by customer. The second statement (Select 'Second') then extracts all other orders which are not in the first statement.

This should work as you expected, Muhammed, however I don't have 1000s of rows to test this with. Performance should be ok as the With part will create a temporary table to work with.

Answer 3

The idea is as follows

• Select all child orders within one hour with its minimum possible (Parent)ID. (I am assuming here that the lowest OrderID will also be the oldest OrderID).
• Join these results with the original table.
• Use these results as the basis of the update statement.

SQL Statement

UPDATE  Orders
SET     ParentOrderID = p.ParentOrderID
FROM    Orders o
        INNER JOIN (
          SELECT  ParentOrderID = MIN(o1.OrderID), OrderID = o2.OrderID
          FROM    Orders o1
                  LEFT OUTER JOIN Orders o2 ON 
                    o2.CustomerID = o1.CustomerID
                    AND o2.OrderDate > o1.OrderDate
                    AND DATEADD(hh, -1, o2.OrderDate) < o1.OrderDate
          GROUP BY o2.OrderID
        ) p ON p.OrderID = o.OrderID