August 15, 2008 at 10:10 am
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
August 15, 2008 at 10:50 am
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
August 15, 2008 at 2:41 pm
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