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

Re: Find No payments made in 90 days 80 80

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
)
Re: Find No payments made in 90 days 80 80

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.

Re: Find No payments made in 90 days 80 80
Select *
From Clients (NOLOCK)
Where NOT EXISTS
(
  Select *
  From Receipts (NOLOCK)
  Where Clients.ClientId = Receipts.ClientID and Receipts.DateReceived >= GetDate() - 90
)
Re: Find No payments made in 90 days 80 80

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.

Re: Find No payments made in 90 days 80 80

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!

Re: Find No payments made in 90 days 80 80

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.

Re: Find No payments made in 90 days 80 80

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
)
Re: Find No payments made in 90 days 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.