dear ArunRajendra,
i have tried your code but error
Quote:
The multi-part identifier "Main.SO_CustID" could not be bound.
so i have made some changes. for now i can get result but i hope there will be no error in the future.below is the new query.
SELECT distinct (Master_Customer.Cust_No +' '+Master_Customer.Cust_Name) AS Customer,Master_Customer.Cust_Creditlimit-Master_Customer.Cust_Outstanding-(SELECT SUM(SO_Detail.SOD_Cost*SO_Detail.SOD_Qty) AS [Total Cost]
FROM SO_Main JOIN Master_Customer ON Master_Customer.Cust_ID=SO_Main.SO_CustID
JOIN SO_Detail ON SO_Detail.SOD_SOID=SO_Main.SO_ID
GROUP BY SO_Main.SO_CustID,master_customer.Cust_ID
having Master_Customer.Cust_ID='d05f3ecf-a548-44fb-aba5-5838bc713fc4') AS [Balance]
FROM SO_Main JOIN Master_Customer ON Master_Customer.Cust_ID=SO_Main.SO_CustID
JOIN SO_Detail ON SO_Detail.SOD_SOID=SO_Main.SO_ID
GROUP BY Master_Customer.Cust_Outstanding, Master_Customer.Cust_CreditLimit,Master_Customer.Cust_Name,Master_Customer.Cust_No,SO_Main.SO_Status
HAVING Master_Customer.Cust_CreditLimit-Master_Customer.Cust_Outstanding-SUM(SO_Detail.SOD_Cost*SO_Detail.SOD_Qty)>0
AND (SO_Main.SO_Status<>'Partial Invoiced' OR SO_Main.SO_Status<>'Invoiced')