April 22, 2012 at 10:40 am
Hi friends, i have small doubt in sql plz tell me
supose
1st table data like billno, amount
1 ,15000
2 ,25000
3 ,35000
1st table is howmuch amount he will payed
2nd table data like billno ,amount
1 , 8000
2 , 2500
1 , 3600
2 ,2400
2 ,900
3 ,2500
1 ,550
3 , 800
2nd table is refer to he paid amount this many time
so i want output how much amount he paid remaing amount
output like bill no ,amount
1 ,2850
2 ,19,200
3 ,31,700
April 22, 2012 at 12:07 pm
First, you are fairly new here, so one thing you should do is read the first article I reference below in my signature block. It will show you what you need to post and how to post it to get the best help possible.
You did okay with what you posted, but it required extra work on my part to get things setup so that I could actually to some work.
Here is what I did, including setting up a test environment based on what you provided (with a few minor changes). I hope the code I wrote works for you, but be sure to look it over and if you have any questions, be sure tocome back and ask.
-- Create the tables to be used
create table dbo.Table1(
billno int,
amount decimal(10,2)
);
create table dbo.Table2(
billno int,
amount decimal(10,2)
);
-- Insert sample data into the tables
insert into dbo.Table1(billno,amount)
select 1, 15000.00 union all
select 2, 25000.00 union all
select 3, 35000.00;
insert into dbo.Table2(billno,amount)
select 1, 8000.00 union all
select 2, 2500.00 union all
select 1, 3600.00 union all
select 2, 2400.00 union all
select 2, 900.00 union all
select 3, 2500.00 union all
select 1, 550.00 union all
select 3, 800.00;
-- Here is the query that generates the requested information, I am showing both
-- the Original Amount and Remaining Amount. There is no checks for over payment,
-- which would simple show here as a negative amount.
select
t1.billno,
t1.amount OriginalAmount,
t1.amount - dv.AmountPaid CurrentAmount
from
dbo.Table1 t1
cross apply (select SUM(t2.amount) as AmountPaid from dbo.Table2 t2 where t2.billno = t1.billno) dv(AmountPaid)
order by
t1.billno
;
-- Drop the tables, we want to leave the Sandbox environment as we found it.
drop table dbo.Table1;
drop table dbo.table2;
April 22, 2012 at 11:52 pm
This works too.
--Create Table1
Create Table Ex
(billno int,
amount int )
--Insert Data into Table1
Insert Into Ex
Select 1 ,15000
Union All
Select 2 ,25000
Union All
Select 3 ,35000
--Create Table2
Create Table Ex1
(billno int,
amount int )
--Insert Data into Table2
Insert Into Ex1
Select 1 , 8000
Union All
Select 2 , 2500
Union All
Select 1 , 3600
Union All
Select 2 ,2400
Union All
Select 2 ,900
Union All
Select 3 ,2500
Union All
Select 1 ,550
Union All
Select 3 , 800
--Query to get required data
;With CTE
As
(Select billno, SUM(Amount) As Total From Ex1 Group By billno)
Select a.billno, b.amount as Original_Amount ,(b.amount - a.Total) As Balance From CTE as a
JOIN Ex as b ON a.billno = b.billno
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply