How to disaply data using sql query

Posted by Sampath1750 under Sql Server on 6/11/2013 | Points: 10 | Views : 879 | Status : [Member] | Replies : 5
Hi all,

I have the 2 tables EmpLoans and EmpPayments, tables Data like below

EmpLoans
UserId LoanAmt
229 15000
229 3000
229 12500

EmpPayments

UserId Date LoanInstallment RemainingAmt
229 1/31/2013 1500 29000
229 2/28/2013 2500 26500
229 3/31/2013 3500 23000
229 4/30/2013 3000 20000

I want the data like below using sql query

UserId Date Sum(LoanAmt) LoanInstallment RemainingAmt
229 1/31/2013 30500 1500 29000
229 2/28/2013 30500 2500 26500
229 3/31/2013 30500 3500 23000
229 4/30/2013 30500 3000 20000

Can anybody help me the query display like above.
Thanks,




Responses

Posted by: Niladri.Biswas on: 6/11/2013 [Member] Platinum | Points: 25

Up
0
Down
Try this

Declare @EmpLoans table(UserId int, LoanAmt int)

Insert into @EmpLoans Values(229,15000),(229,3000),(229,12500)

Declare @EmpPayments table(UserId int,[Date] Datetime, LoanInstallment int, RemainingAmt int)
Insert into @EmpPayments values
(229,'1/31/2013',1500,29000),(229,'2/28/2013',2500,26500),
(229,'3/31/2013',3500,23000),(229,'4/30/2013',3000,20000)

Select
ep.UserId
,ep.[Date]
,x.[Sum(LoanAmt)]
,ep.LoanInstallment
,ep.RemainingAmt
From @EmpPayments ep
Join( Select UserID,[Sum(LoanAmt)]= SUM(LoanAmt)
From @EmpLoans
Group By UserID)x
On ep.UserId = x.UserId


Best Regards,
Niladri Biswas

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 6/12/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Sampth,

Try this query for your required output

create table #EmpLoans(UserId int,LoanAmt int)

insert into #EmpLoans values (229,15000),(229, 3000),(229, 12500)

create table #EmpPayments(UserId int, [Date] datetime, LoanInstallment int, RemainingAmt int)
insert into #EmpPayments values
(229, '1/31/2013', 1500, 29000),
(229, '2/28/2013', 2500, 26500),
(229, '3/31/2013', 3500, 23000),
(229, '4/30/2013', 3000, 20000)

--Output
;with Emp as(
select UserId,SUM(LoanAmt)LoanAmt from #EmpLoans group by UserId
)select b.UserId,b.Date,a.LoanAmt,b.LoanInstallment,b.RemainingAmt
from Emp a join #EmpPayments b on a.UserId=b.UserId


Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/26/2013 [Member] Starter | Points: 25

Up
0
Down
http://www.aspdotnet-suresh.com/2012/05/aspnet-gridview-examples-and-samples-in.html

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nathanmannar on: 8/30/2014 [Member] Starter | Points: 25

Up
0
Down
This gridview tutorial will help you...

http://asp.net-informations.com/gridview/asp-gridview.htm

Nathan


Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

If you want to achieve this then you need to sum the total amount by joining 2 tables.

EX:
select t1.UserId, Date, Sum(LoanAmt), LoanInstallment, RemainingAmt 
from table1 as t1,
table2 as t2
where t1.UserId=t2.UserId
group by UserId,Date,LoanInstallment, RemainingAmt
order by remainingamt desc


Try like above to achieve your goal.



Sampath1750, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response