How to display the data using sql query

Question

1.00/5 (1 vote)

See more:

Hi,
How to display data using sql query

My Table name is LoanDetails and my Data Looks like below

UserId, MonthId, YearId, LoanAmount, Installment
1, 12, 2012, 15000, 0
2, 12, 2012, 25000, 0
3, 12, 2012, 20000, 0
1, 12 2012 0, 1000
2, 12, 2012, 0, 2500
3, 12, 2012, 0, 1500
1, 1, 2013, 0, 1000
2, 1, 2013, 0, 1500
3, 1, 2013, 0, 2000
1, 2, 2013, 0, 2000
2, 2, 2013, 0, 2500
3, 2, 2013, 0, 2000
1, 3, 2013, 0, 1500
2, 3, 2013, 0, 2000
3, 3, 2013, 0, 2000

can anybody tell me select statement to display data like below using above data, where monthid and YearId

If i give MOnthId 12 and YearId 2012 data like below

UserId, MonthId, Year , InstallmentAmt, PrevLoanAmt ,PendingLoanAmt
1 , 12 , 2012 ,1000, 0 , 14000
2 , 12 , 2012 ,2500, 0 , 22500
3 , 12 , 2012 ,1500, 0 , 18500

If i give MOnthId 1 and YearId 2013 data display like below

UserId, MonthId, Year ,Installmentamt, PrevLoanAmt, PendingLoanAmt
1 , 1 , 2013 ,1000, 14000 , 13000
2 , 1 , 2013 ,1500, 22500 , 21000
3 , 1 , 2013 ,2000, 18500 , 16500

If i give MOnthId 2 and YearId 2013 data display like below

UserId, MonthId, Year,InstallemtAmt, PrevLoanAmt, PendingLoanAmt
1 , 2 , 2013 , 2000, 13000 , 11000
2 , 2 , 2013 , 2500, 21000 , 18500
3 , 2 , 2013 , 2000, 16500 , 14500

If i give MOnthId 3 and YearId 2013 data display like below

UserId, MonthId, Year, Installment, PrevLoanAmt, PendingLoanAmt
1 , 3 , 2013 , 1500, 11000 , 9500
2 , 3 , 2013 , 2000, 18500 , 16500
3 , 3 , 2013 , 2000, 14500 , 12500

Thanks,

Posted 18-Jul-13 5:15am

sampath1750

Updated 18-Jul-13 8:52am

v4

Add a Solution

Comments

OriginalGriff 18-Jul-13 11:21am

And? What help do you need? What have you tried? Where are you stuck?

[no name] 18-Jul-13 11:38am

Do you mean "Will someone do my job for me"?

sampath1750 18-Jul-13 12:43pm

I want the userId, MonthId, YearId, Previous loan amount and Remaining loan amount using MohId and YearId using My table data

ridoy 18-Jul-13 13:37pm

unclear

sampath1750 18-Jul-13 14:26pm

My table is LoanDetails( UserId, MonthId, YearId, LoanAmt, Installment)
using above columns, i want the userId, MonthId, YearId, Previous loan amount and Remaining loan amount, where condition monthid and yearid. Previous Loan amount means Last months pending amount, Remaining loan amount means Loan balance amount

Arpit Shrivastava 18-Jul-13 15:09pm

Use Group By syntax to solve your query, if you stuck there then post a question here.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mahesh_Bhosale · Answer 1 · 2013-07-18T05:42:00

Solution 1

You need to write procedure
try these

SQL

create procedure usp_getLoanDetails
(
    @MonthId [int],
    @YearId [int]
)
 as
 begin
	select
	  L.UserId,
	  L.MonthId,   
	  L.YearId, 
	  L.LoanAmt,    
	  L.Installment
        from  LoanDetails L    
	WHERE      
	     L.MonthId =@MonthId AND L.YearId= @YearId
 end

Posted 18-Jul-13 5:42am

Mahesh_Bhosale

Comments

sampath1750 18-Jul-13 12:46pm

I want Prevoius loan amount and remaining Loan amount using my table data, if i enter MOnthId and YearId

NPSSR · Answer 2 · 2013-07-18T05:43:00

Solution 2

You need to write your select query like this:

SQL

Select * from LoanDetails where UserId=1 and MonthId=12 and Year=2012

Please mark this as a answer if your purpose is solved.
Thanks

Posted 18-Jul-13 5:43am

NPSSR