Club Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Club Query

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2012-03-14 : 03:52:02

My Table Details

1] Table Name : APPMAST
Fields :

AppMstID
AppMstName
MainID

2] Table Name : PayReports
Fields :

AppMstID
NetPay

3] Table Name : IncExpMast
Fields :

AppMstID
Amount

I've member details in APPMAST
Member's Payout in PAYREPORTS table
& Amount Paid in INCEXPMAST table.

Actually, there are many members with same name
e.g. there is a Member with Name 'abc' and his ID is say 2,10,23,45,105. Means 'abc' has 5 IDs.
Upto Now, to view all Payments - Paid = Balance Details I Used query like :

Select *,(Payout-Paid) as Balance from (
Select a.appmstid,
(Select appmstname from appmast where appmstid = a.appmstid) as ClientName,
sum(a.NetPay) as Payout,
(Select coalesce(sum(Amount),0) from IncExpMast where appmstid = a.appmstid) as Paid
from payreports a group by a.appmstid) Test Order by balance;

But, by using this method in My statement I get 'abc's record for 5 times and I need it at once.
So, I thought about to club his ID from Main Table and then Added new column as MAINID in APPMAST TABLE.
In Appmast I update all MAIN ID of 'abc' with 2. Means 2 is the Main ID of all 5 IDs.
Now the task is to modify above query and to show Payment of 'abc' at once only.

How to do this.????

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-14 : 07:43:10

Would this work for you?

SELECT
	a.appmstid,
	a.appmstname AS ClientName,
	ISNULL(SUM(p.NetPay),0) AS Payout,
	ISNULL(SUM(i.Amount),0) AS Paid,
	ISNULL(SUM(p.NetPay),0) -  ISNULL(SUM(i.Amount),0) AS Balance

FROM
	APPMAST a
	LEFT JOIN PayReports p ON p.appmstid = a.appmstid
	LEFT JOIN IncExpMast i ON i.appmstid = a.appmstid
GROUP BY
	a.appmstid,
	a.appmstname
ORDER BY
	Balance;

ravininave
Posting Yak Master

111 Posts

Posted - 2012-03-14 : 07:54:13

quote:

Originally posted by sunitabeck

Would this work for you?

SELECT
	a.appmstid,
	a.appmstname AS ClientName,
	ISNULL(SUM(p.NetPay),0) AS Payout,
	ISNULL(SUM(i.Amount),0) AS Paid,
	ISNULL(SUM(p.NetPay),0) -  ISNULL(SUM(i.Amount),0) AS Balance

FROM
	APPMAST a
	LEFT JOIN PayReports p ON p.appmstid = a.appmstid
	LEFT JOIN IncExpMast i ON i.appmstid = a.appmstid
GROUP BY
	a.appmstid,
	a.appmstname
ORDER BY
	Balance;

The main problem is to show payment using MAINID

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

ravininave
Posting Yak Master

111 Posts

Posted - 2012-03-22 : 15:03:12

hey no one is replying.......

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-22 : 20:00:42

quote:

The main problem is to show payment using MAINID

Without some sample data, it is hard for me to visualize what you are after. If you post your typical input and the expected output, you would get better and faster responses.

Regardless, is it one of these?

SELECT
	a.appmstid,
	a.MainID,
	a.appmstname AS ClientName,
	ISNULL(SUM(p.NetPay),0) AS Payout,
	ISNULL(SUM(i.Amount),0) AS Paid,
	ISNULL(SUM(p.NetPay),0) -  ISNULL(SUM(i.Amount),0) AS Balance

FROM
	APPMAST a
	LEFT JOIN PayReports p ON p.appmstid = a.appmstid
	LEFT JOIN IncExpMast i ON i.appmstid = a.appmstid
GROUP BY
	a.appmstid,
	a.MainID,
	a.appmstname
ORDER BY
	Balance;

Or perhaps this?

SELECT
	a.MainID,
	a.appmstname AS ClientName,
	ISNULL(SUM(p.NetPay),0) AS Payout,
	ISNULL(SUM(i.Amount),0) AS Paid,
	ISNULL(SUM(p.NetPay),0) -  ISNULL(SUM(i.Amount),0) AS Balance

FROM
	APPMAST a
	LEFT JOIN PayReports p ON p.appmstid = a.appmstid
	LEFT JOIN IncExpMast i ON i.appmstid = a.appmstid
GROUP BY
	a.MainID,
	a.appmstname
ORDER BY
	Balance;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources