How to display data using sql query [Resolved]

Posted by Sampath1750 under Sql Server on 7/18/2013 | Points: 10 | Views : 1625 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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,

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

Latest Posts