latest order date

Hi,
I have an order table which contains the following fielde: 1). orderid (this
is the order number 2). clientid 3). orderdate.
I need to script so that I can find out those clientids which do not have
place an order for at least 90 days.
Can you tell me how to program it?Read the documentation. You want to select clientids which are NOT IN a
subquery that selects all clients that have ordered in the last 90 days. You
could also use a join where max order date is less than 90 days ago.
RR
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
(this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Something like this should give you the client and their last order date.
declare @.DaysSinceOrder as numeric
set @.DaysSinceOrder = 90
select
clientid ,
max(orderdate)
from "YourTableHere"
group by clientid
having max(orderdate) < getdate()-@.DaysSinceOrder
"qjlee" wrote:

> Hi,
> I have an order table which contains the following fielde: 1). orderid (th
is
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>|||Please post DDL, sample data and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006 )
Since you have a ClientID column in your Orders table, I am guessing that
you have a Clients table somewhere. Here is a complete guess:
SELECT ClientID, ClientName
FROM Clients
WHERE ClientID NOT IN (SELECT ClientID FROM Orders WHERE DATEDIFF(d,
OrderDate, CURRENT_TIMESTAMP) <= 90)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:B84E46A2-9291-4D17-A625-3DBADD9746B1@.microsoft.com...
> Hi,
> I have an order table which contains the following fielde: 1). orderid
> (this
> is the order number 2). clientid 3). orderdate.
> I need to script so that I can find out those clientids which do not have
> place an order for at least 90 days.
> Can you tell me how to program it?
>
>