Click here to Skip to main content
15,030,320 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
tbl_Company_Payment
***************************************************************
Sl_NO	Date		Company_Name	Payment
1	   2021-06-15	Godrej		      55000	
2	   2021-06-15	Emami		      50000	
4	   2021-06-15	Hemas		      65000	
5	   2021-06-15	Emami		      500	
6	   2021-06-18	Godrej		      2500	

 tbl_purChase
******************************************************************
Sl_NO	Company_Name	Product_Name Purchase_Rate	Qty	 value	Date
1	         Godrej		fik 625ml	     300.00		25	7500.00	2021-06-11	
2	         Godrej		Fik 800ml	     350.00		20	7000.00	2021-06-11	
3	         Godrej		Fik 400ml	     200.00		20	4000.00	2021-06-11	
4	         Emami		Nro 200 ml	      170.00	10	1700.00	2021-06-11	
5	         Emami		Nro 100 ml	      85.00		12	1020.00	2021-06-11	
6	         Emami		Nro 50ml	       56.00	14	784.00	2021-06-11	
7	         Hemas		kumarika 400ml	   255.00	25	6375.00	2021-06-11	
8	         Hemas		kumarika 300ml	   245.00	20	4900.00	2021-06-11	
9	         Hemas		kumarika 200ml	   145.00	40	5800.00	2021-06-11	
result
*************************************************************************************
Date		Company_Name	Purchase 	            payment		Remaining_Blance
2021-06-15	Hemas		       0.00		             65000			65000.00
2021-06-11	Hemas		   17075.00		               0			-17075.00

But My expected Redult like below(Note: Payment-Purchase=The remaining amount)
****************************************
Date		Company_Name	Purchase 	            payment		Remaining_Blance
2021-06-15	Hemas		       0.00		             65000			65000.00
2021-06-11	Hemas		   17075.00		                  0			47925.00

What I have tried:

<pre>select
f.Date,f.Company_Name,f.Purchase,f.Payment,Remaining_Blance=f.Payment-f.Purchase
from
(select date,Company_Name,sum(Payment)as Payment, 0 as Purchase from 
tbl_Company_Payment
group by date,Company_Name
union all
select Date,Company_Name,0 as Payment,sum(value)as Purchase
from tbl_purChase
group by date,Company_Name)f

where f.Company_Name='hemas'
Posted
Updated 19-Jun-21 1:14am

1 solution

   
Comments
SaiKarthik Chandra 21-Jun-21 3:32am
   
Can you please put your exact query question.? So that we can check your query accordingly.
And pls keep in mind that it is better to provide the insert statements for the data you are working. So that it will be easy for someone who is willing to check and get back to you.
Richard Deeming 21-Jun-21 10:19am
   
Why have you posted this as a comment to the solution, when it's clearly aimed at the person who posted the question?

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



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