Click here to Skip to main content
15,888,461 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
I have two tables loan(custno,acno & clearbal) & customer (custno,pan & name). Custno is common in both tables.I want to generate PAN wise data.For which I have build below mentioned query to get pan wise loan outstanding, I want to have customer name in output, but several pans are mentioned in different customer IDs, So

if i add name in same query it splits the record.
Is possible to incorporate customer name which is having lower id like
if "aaa" pan in in cusid 1 & 2 so cistomer nume in 1 should be displayed & group by should be done on "aaa"
sample details

Customer Table
Custno Pan Name
1 aaa Kevin
2 bbb peter
3 aaa Josh

Loan Table
Custno acno clearbal
1 20 100
2 21 50
3 22 200

Output I am getting through Query
Pan Count Bal
aaa 2 300
bbb 1 50

What I want is

Pan Name Count Bal
aaa Kevin 2 300
bbb peter 1 50

select c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal from LOAN as L inner join customer as C
on L.CustNo=c.CustNo where l.clearbal<0 group by c.PAN order by bal desc


I have tried below mentioned query but output skips records having same pan card.

What I have tried:

select c.name,c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal from LON20190629 as L inner join customer as C
on L.CustNo=c.CustNo inner joiN
  (
    SELECT pan, MIN(CustNo) MinPoint
    FROM customer
	where len(pan)>6
    GROUP BY PAN
  ) tbl1
  ON tbl1.PAN = C.PAN
WHERE tbl1.MinPoint = C.CustNo and and l.ClearBal<0 group by c.PAN,c.Name order by bal desc
Posted
Updated 1-Aug-20 21:58pm
v3
Comments
OriginalGriff 26-Jul-20 4:08am    
We are going to need a small input sample for each table, together with both the output you get and what you expect - and that small sample must show the problem. If that sample input also contains the SQL to create and populate the tables that would be appreciated!

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. And we have no access to your data so we can't test anything we might suggest!

Use the "Improve question" widget to edit your question and provide better information.
Hemil Gandhi 26-Jul-20 7:07am    
Thanks for the replying , I have updated the question. Hope I have covered all the missing details.
OriginalGriff 26-Jul-20 7:32am    
And what is "Code" when it's at home?

When you provide samples, THEY HAVE TO WORK.
If they don't run, or they don't provide the output you specify, they are no use to us at all ...
Hemil Gandhi 27-Jul-20 12:57pm    
CustNo in customer table in named code

1 solution

Here's a strategy - not the most elegant but simple to understand.

To get the second highest value, with or without multiple entries of any of the values you can do something like selecting the top 1 from your query where the value is less than the MAX for the return set (as a subquery), ordering the top 1 query by that value in descending order.


The only special case you need to consider is what if all of the returned values have the same value so there is now 'second highest' value.
 
Share this answer
 
Comments
Hemil Gandhi 30-Jul-20 13:06pm    
Sir, i am not expert in sql queries.so i dont get your solution, can you please help me with query??
W Balboos, GHB 30-Jul-20 13:13pm    
I have to assume you can do the following:
Write a SELECT query requesting the MAX() of some value
Write a query that can ORDER BY some value in DESCending order
Write a query with a SUBQUERY and understand the comparison operators such as <,>, =, != (etc).

You may wish to look into a tutorial as all of these are pretty basic:
https://www.w3schools.com/sql/default.asp
Hemil Gandhi 30-Jul-20 13:20pm    
I can write all this queries individually but how to combined them all to get solution of my problem.
W Balboos, GHB 30-Jul-20 13:53pm    
I gave you a link for tutorial help. Also, the major hint "subquery" which can be used to control what you see in, for example, your WHERE clause. SUBQUERY - a lovely thing sometimes.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900