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

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
)

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.

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

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.

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!

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.

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
)

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.