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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy