How to display data using sql query [Resolved]

Posted by Sampath1750 under Sql Server on 7/18/2013 | Points: 10 | Views : 828 | Status : [Member] | Replies : 2
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,




Responses

Posted by: Allemahesh on: 7/18/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Dear Sampath, First I will thanks to you for posting this question.
The solution for this question is as follows:- I have tested this query and working fine as per your requirement.

Here you need to pass @YEAR and @MONTH and you will get required result.


DECLARE @YEAR INT = 2013,
@MONTH INT = 2

SELECT
UserId,
MonthId AS 'Month',
YearId AS 'Year',
Installment AS 'Installmentamt',
ISNULL(
(
SELECT SUM(LoanAmt) - SUM(Installment)
FROM dbo.LoanDetails
WHERE CAST(CAST(YearId AS VARCHAR(10)) + '-' + CAST(MonthId AS VARCHAR(10)) + '-' + '01' AS DATETIME) <
CAST(CAST(@YEAR AS VARCHAR(10)) + '-' + CAST(@MONTH AS VARCHAR(10)) + '-' + '01' AS DATETIME)
GROUP BY UserId
HAVING UserId = l.UserId
), 0) AS PrevLoanAmt,

(
SELECT SUM(LoanAmt) - SUM(Installment)
FROM dbo.LoanDetails
WHERE CAST(CAST(YearId as VARCHAR(10)) + '-' + CAST(MonthId as VARCHAR(10)) + '-' + '01' AS DATETIME) <=
CAST(CAST(@YEAR AS VARCHAR(10)) + '-' + CAST(@MONTH as VARCHAR(10)) + '-' + '01' AS DATETIME)
GROUP BY UserId
HAVING UserId = l.UserId
) AS PendingLoanAmt

FROM dbo.LoanDetails l
WHERE CAST(CAST(YearId AS VARCHAR(10)) + '-' + CAST(MonthId as VARCHAR(10)) + '-' + '01' AS DATETIME) =
CAST(CAST(@YEAR AS VARCHAR(10)) + '-' + CAST(@MONTH as VARCHAR(10)) + '-' + '01' AS DATETIME)
GROUP BY UserId, MonthId, YearId, Installment
HAVING Installment <> 0


Enjoy coding.


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

Posted by: Phagu007 on: 7/18/2013 [Member] Starter | Points: 25

Up
0
Down
You can use this given statement for display query : SELECT UserId , MonthId,YearId ,LoanAmount,Indtallment FROM example GROUP BY MonthId,Yearid
Please refer to this link for more detail
http://databases.about.com/od/sql/a/Group_By_SQL.htm

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

Login to post response