Get the first record of the same FK as the date difference

Question

I need to get the first 1 record from the same client inserted within one hour. If the record is inserted after one hour, then it is not needed. See the following table. This is just a sample of 1000 entries. I am using SQL Server 2005.

alt text http://img651.imageshack.us/img651/3990/customershavingmultiple.png

Answer 1

The idea is as follows

• Select all child orders within one hour with the minimum possible (parent) identifier. (I assume that the youngest OrderID will also be the oldest OrderID).
• Attach these results to the source table.
• Use these results as the basis for 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 

Answer 2

Given 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 operator shows the last order placed by the customer over a period of time, followed by 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 check if I really got the correct lines.

In short, the With statement restricts orders from the table to those that were installed in the last hour based on the current system date, and orders them by order date. The first expression (Select "First") retrieves only the first sales orders. The second statement (Select "Second") retrieves all other orders that are not in the first expression.

This should work as you expected, Mohammed, however I do not have 1000 lines to check this. Performance should be in order, as part "C" will create a temporary table for work.

Answer 3

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

order order

Note. I used an additional query here to filter the client first so that you get the best performance. You should try to avoid using the (DATEDIFF) functions in suggesting restrictions on large date sets.