Find No payments made in 90 days

[_taz_]

I need help with a query. Looking though the forum and other online resources I can usually figure out what I need, but I'm drawing a blank on this one.

I have a clients table which has client info in it, and a receipts table with receipt info. what I need is to get a list of clients that have NOT had a receipt in the past 90 days.

I can get the ones that have a receipt older than 90, ones that have no receipt at all, but just can't seem to figure out how to get the ones without a receipt in the last 90 days

[sknake]

Try this approach:

--No payment in last 90 days
Select *
From Invoice
Where NOT EXISTS
(
  Select *
  From InvPayment (NOLOCK)
  Where Invoice.InvNumber = InvPayment.InvNumber and InvPayment.PayDate >= GetDate()- 90
)

--Has a payement in the last 90 days
Select *
From Invoice
Where EXISTS
(
  Select *
  From InvPayment (NOLOCK)
  Where Invoice.InvNumber = InvPayment.InvNumber and InvPayment.PayDate >= GetDate()- 90
)

[_taz_]

i tried that but I get all receipts over 90 days old.

SELECT     *
FROM         Receipts
WHERE     (NOT EXISTS
                          (SELECT     *
                            FROM          Clients(NOLOCK)
                            WHERE      Clients.ClientID = Receipts.ClientID AND Receipts.DateReceived >= GetDate() - 90))
ORDER BY ClientID

I originally had something similar but I was using a right outer join
because I figured I would need all clients whether or not they had a receipt yet.

[sknake]

Select *
From Clients (NOLOCK)
Where NOT EXISTS
(
  Select *
  From Receipts (NOLOCK)
  Where Clients.ClientId = Receipts.ClientID and Receipts.DateReceived >= GetDate() - 90
)

[_taz_]

Ok, I tried that and it wouldn't let me do it without a join. So I did a join and it returns every payment older than 90 days

here's what I changed it to

SELECT     Clients.ClientID, Clients.WholeName, Receipts.DateReceived
FROM         Clients WITH (NoLock) LEFT OUTER JOIN
                      Receipts ON Clients.ClientID = Receipts.ClientID
WHERE     (NOT EXISTS
                          (SELECT     *
                            FROM          Clients(NOLOCK)
                            WHERE      Clients.ClientID = Receipts.ClientID AND Receipts.DateReceived >= dateadd(day, - 90, getdate())))
GROUP BY Clients.ClientID, Clients.WholeName, Receipts.DateReceived
ORDER BY Clients.ClientID

I don't really need the datereceived column I was just using that for troubleshooting.

[sknake]

So do you have it working now? If so please mark this thread as solved, if not then please let me know what the issue is. Good luck!

[_taz_]

well I was actually trying to return clientid's that did not have a payment in the last 90 days, not all payments older than 90 days.

[sknake]

I dont get it. I already posted a solution for that:

Select * --You get the client ID here, right?
From Clients (NOLOCK)
Where NOT EXISTS
(
  Select *
  From Receipts (NOLOCK)
  Where Clients.ClientId = Receipts.ClientID and Receipts.DateReceived >= GetDate() - 90
)

[_taz_]

ok, I think it was my fault, I switched the first FROM to clients like you suggested, but I didn't change the second FROM to receipts.

i guess that's why I had to do an outer join to make it work. when I changed it to receipts I did away with the join also and it works.

Thanks