Lasr Order placed or cust acct used more than 9mths ago

  • Hello

    I am using the following query to determine customers account that have been inactive for more than 9mths or last placed an order 9tmhs ago, but doesnt seem to work right.

    SELECT T_OrderHeader.OrderID,T_OrderHeader.CustomerID,T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,T_OrderHeader.ShipSurname,

    CONVERT(NVARCHAR(20), T_OrderHeader.OrderDate, 106) AS [Order Date]

    FROM T_OrderHeader

    WHERE T_OrderHeader.OrderDate > GETDATE()-270

    ORDER BY T_OrderHeader.OrderDate DESC

  • Maybe something like this (untested since you don't have structures posted):

    SELECT T_OrderHeader.OrderID,T_OrderHeader.CustomerID,T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,T_OrderHeader.ShipSurname,

    CONVERT(NVARCHAR(20), T_OrderHeader.OrderDate, 106) AS [Order Date]

    FROM T_OrderHeader

    CROSS APPLY (SELECT TOP (1) OrderHeaderId

    FROM T_ORderHeader

    WHERE OrderDate < (GETDATE-270)) AS x

    ON T_OrderHeaderId = x.OrderHeaderId

    ORDER BY T_OrderHeader.OrderDate DESC

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I dont have the exact requirements, but if you need only the customers you should be able to do something like this:

    SELECT CUS.CustomerID

    FROM T_CustHeader CUS

    WHERE NOT EXISTS(

    SELECT * FROM T_OrderHeader ORD

    WHERE ORD.CustomerID = CUS.CustomerID

    AND ORD.OrderDate > GETDATE() - 270)

    Best Regards,

    Chris Büttner

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply