No Payments over 90Days

[_taz_]

Hi, A while back you guys helped me make a query that returns all clients that have not made a payment in 90 days. this works good.
I now want to exclude those who have never made a payment.

I tried to add in

Exists (Select Receipts.DateReceived from Receipts)

but no matter where I insert it I can't get it to work. I don't get any errors, but people that have never made a payment still show up. I run that in a query by itself and it works.
here's my current query

SELECT     ClientID, WholeName,ClientStatus
FROM         Clients WITH (NoLock)
WHERE     (NOT EXISTS
                          (SELECT     *
                            FROM          Receipts(NOLOCK)
                            WHERE      Clients.ClientID = Receipts.ClientID AND Receipts.DateReceived >= getdate() - 90)) AND (ClientStatus LIKE 'active%' )
                      
GROUP BY ClientID, WholeName, ClientStatus
Order By Clientid

[sknake]

This should clear it up for you. I did my best to mimic your table structure and values. I think the query should explain itself but let me know if you have any questions:

IF OBJECT_ID('tempdb..#Client', 'U') IS NOT NULL DROP TABLE #Client
IF OBJECT_ID('tempdb..#Receipts', 'U') IS NOT NULL DROP TABLE #Receipts
Create Table #Client
(
  ClientId int identity(1000, 1) PRIMARY KEY,
  WholeName varchar(50),
  ClientStatus varchar(10)
)
Create Table #Receipts
(
  ReceiptId int identity(1000, 1) PRIMARY KEY,
  ClientId int NOT NULL,
  DateReceived datetime,
  Amount money
)

SET NOCOUNT ON

DECLARE @id int
INSERT INTO #Client (WholeName, ClientStatus) Values ('Inactive Client', 'Inactive')
SET @id = Cast(SCOPE_IDENTITY() as int)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-500, 5.00)

INSERT INTO #Client (WholeName, ClientStatus) Values ('Payment in 90 days', 'Active')
SET @id = Cast(SCOPE_IDENTITY() as int)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-200, 5.00)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-100, 5.00)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-90, 5.00)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-20, 5.00)

INSERT INTO #Client (WholeName, ClientStatus) Values ('No payment in 90 days', 'Active')
SET @id = Cast(SCOPE_IDENTITY() as int)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-200, 5.00)
INSERT INTO #Receipts (ClientId, DateReceived, Amount) Values (@id, GetDate()-100, 5.00)

INSERT INTO #Client (WholeName, ClientStatus) Values ('No payment', 'Active')
SET @id = Cast(SCOPE_IDENTITY() as int)

SET NOCOUNT OFF

--OK, now we have the environment set up

Select *
From #Client (NOLOCK)
Where 
ClientStatus Like 'Active%' --Active clients only
and
NOT EXISTS --No payment withing 90 days
(
  Select *
  From #Receipts (NOLOCK)
  Where #Receipts.ClientId = #Client.ClientId and #Receipts.DateReceived >= GetDate()-90
)
and
EXISTS --Has made at least one payment
(
  Select *
  From #Receipts (NOLOCK)
  Where #Receipts.ClientId = #Client.ClientId 
)

[_taz_]

I understand 1-18 and 38-60. I do not understand all your inserts (19-37) new to me I am trying to read up on that now.

when I run the code i get

ClientId WholeName ClientStatus
1002 No payment in 90 days Active

(client 1002 is not an active client)

[sknake]

Those inserts were just populating different scenarios for test data. Line #46 in my post excludes Inactive clients, by only fetching Active% clients, so it is not possible you saw an inactive client unless something was changed in the original query.

[_taz_]

I assure you I did not change anything (want to make it work before I tweak it)
this is from simple query of clientid and clientstatus
ClientID ClientStatus
1002 SHREDDED NO PAY

I am also curious as to why it put "No Payment in 90 Days" as Clients Wholename

(line 31)

thanks for your help

[sknake]

Please post your _entire_ query. You have something wrong with it. It is impossible for you to be seeing those results if you properly modified the query I gave you to reference your tables.

[_taz_]

well I guess that's part of the problem then. I didn't modify it at all, I copied and pasted it. I couldn't figure out how it is supposed to pull from my tables. I see where you created temp tables and inserted values but I don't see how you pulled it from my tables into the temps.

sorry..

[_taz_]

Scott, thanks for your help.
I think we are right on the edge here if anyone else has any ideas, thanks

[sknake]

I still don't understand how my last post didn't answer your question. Post the query you're using, table structure, sample data, etc and outline what is not working for you.

[_taz_]

I am guessing the problem is in your last post, you said "if I properly modified it" which I did not modify it. I see where you are creating the temp tables and putting in values, but I don't see where it is pulling from my tables. I'm think that's where it's failing because it doesn't know where to look in my database.
my original query is in my first post. the query I'm using now is copy and paste of yours.
When I run it this is what returns:

ClientId	WholeName	ClientStatus
1002	No payment in 90 days	Active

When I run mine I get:

ClientID	WholeName	ClientStatus
415	        Debra	        ACTIVE MO
1108	        Joy	        ACTIVE-MO-NOFEES
1534	        Timothy	        ACTIVE-INFO-INCOMP
1581	        Sharen	        ACTIVE 
1902	        Brett	        ACTIVE-INFO-INCOMP
2038	        Courtney	        ACTIVE 
2194	        Peggy	        ACTIVE-INFO-INCOMP
2240	        Sherri	        ACTIVE 
2318	        Anthony	        ACTIVE-INFO-INCOMP
2332	        Sequana	        ACTIVE-INFO-INCOMP
2346	        Jason	        ACTIVE-INFO-INCOMP
2351	        Ruby	        ACTIVE-INFO-INCOMP

When I look up clientid 1002:

ClientID	WholeName	ClientStatus
1002         Jeanna             SHREDDED

sorry if I'm missing something stupid. I'm sure it's because I didn't modify it correctly, but this if my first time using temp tables. (or trying to anyway)

[sknake]

You shouldn't use temp tables at all for this. Look at line #41 from my original post:

--OK, now we have the environment set up

All of the code above line 41 was simulating your environment since I did not have your tables. Just modify the select query below that to reference your tables.

[_taz_]

Thanks a bunch.