Based on SQL cte query results, count total no. Of customers who have paid the installment

Question

0.00/5 (No votes)

See more:

below query is giving me customer id's. what i want is: i want to count total number of customers who have paid the installments from these id's. there is another table named as installments, there is a status column with values like 'paid' and 'not paid'. we have to count from installments table.

C#

declare @nodeid int = '1';
with cte as ( 
select  cust_ID, name,null lnode, null rnode from  user_detail where 
cust_ID = @nodeid 

union all select t.cust_ID,t.name, ISNULL(cte.lnode, CASE WHEN t.joinside = 
0 THEN 1 ELSE 0 END) 
lnode, ISNULL(cte.rnode, CASE WHEN t.joinside = 1 THEN 1 ELSE 0 END) 
rnode from  user_detail t inner join cte on cte.cust_ID = t.parentid )

select cust_id from cte 
where rnode='0'  option (maxrecursion 0)

What I have tried:

declare @nodeid int = '1';
with cte as ( 
select  cust_ID, name,null lnode, null rnode from  user_detail where 
cust_ID = @nodeid 

union all select t.cust_ID,t.name, ISNULL(cte.lnode, CASE WHEN t.joinside = 
0 THEN 1 ELSE 0 END) 
lnode, ISNULL(cte.rnode, CASE WHEN t.joinside = 1 THEN 1 ELSE 0 END) 
rnode from  user_detail t inner join cte on cte.cust_ID = t.parentid )

select Count(lnode) total_left,Count(status) total_paid from cte c
inner join installments i on c.cust_id=i.cust_id
where rnode='0' and status='paid'
option (maxrecursion 0)

but this query is not giving accurate results.

Posted 23-Dec-18 23:35pm

Harpreet_125

Updated 24-Dec-18 1:27am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-12-24T01:28:00

It would help if you've posted some sample data. But let's try a few variations.

If only a single payment can exist per customer then to list customers with payment, perhaps something like

SQL

SELECT c.Cust_Id
FROM user_detail c
INNER JOIN installments i ON i.Cust_Id = c.Cust_Id
WHERE c.Status = 'paid'

If multiple payments can exists and one payment will suffice then something like

SQL

SELECT c.Cust_Id
FROM user_detail c
WHERE EXISTS (SELECT 1
              FROM installments i 
              WHERE i.Cust_Id = c.Cust_Id
              c.Status = 'paid')

If all payments must be paid then perhaps

SQL

SELECT c.Cust_Id
FROM user_detail c
WHERE NOT EXISTS (SELECT 1
                  FROM installments i 
                  WHERE i.Cust_Id = c.Cust_Id
                  c.Status = 'not paid')

And if you want the total count of customers you can change the SELECT clause in previous examples to

SQL

SELECT COUNT(*)
...