I am trying to fetch from different tables using stored procedure in MSSQL Server. here is the code
<pre lang="SQL">SELECT
ap.ApplicantName, ap.PlotSerialNo, pl.LocationTitle,
ptt.PlotTitle,pp.TotalPayment AS TotalActualPayment,ap.concession,
(pp.TotalPayment-CONVERT(INT, ap.concession)) AS TotalAfterConc,
ai.DownPayments,
SUM(CONVERT(INT, ai.Arrears)+CONVERT(INT, ai.LastPayment)) AS Downpmtlstpmt,
(CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.InstallmentAmount)), 0) END) AS TotalAmtPaidInstallments,
(CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.Arrear)), 0) END) AS TotalArrearPaidInstallments
FROM
dbo.TblApplicant AS ap
JOIN
dbo.TblApplicantInitialPayments AS ai ON ai.ApplicantId = ap.ApplicantId
INNER JOIN
dbo.TblApplicantInstallments AS aii ON aii.ApplicantId = ap.ApplicantId
INNER JOIN
dbo.PaymentType AS Pt ON pt.PaymentTypeId = ap.PaymentTypeId
INNER JOIN
dbo.TblApplicantPlotPayments AS app ON app.ApplicantId = ap.ApplicantId
INNER JOIN
dbo.TblPlotPayments AS pp ON pp.PlotPaymentID = app.PlotPaymentID
INNER JOIN
dbo.TblPlotLocation AS pl ON pl.LocationId = app.LocationId
INNER JOIN
dbo.TblPlotType as ptt on ptt.PlotTypeId = app.PlotTypeId
WHERE
pt.PaymentTypeId = 1 AND ptt.PlotTypeId = @plotsize
GROUP BY
ap.ApplicantName, ap.ApplicantId, ptt.PlotTitle,
pl.LocationTitle, aii.Status,
ap.PlotSerialNo, pp.TotalPayment, ap.concession,
ai.DownPayments, ai.Arrears, ap.RegDate, pt.Title
ORDER BY
ptt.PlotTitle, pl.LocationTitle</pre>
But it is returning duplicate records. Please tell me what I am doing wrong.
here is the out put
http://prntscr.com/adtdu0[
^]
Thanks
What I have tried:
SELECT
ap.ApplicantName, ap.PlotSerialNo, pl.LocationTitle,
ptt.PlotTitle,pp.TotalPayment AS TotalActualPayment,ap.concession,
(pp.TotalPayment-CONVERT(INT, ap.concession)) AS TotalAfterConc,
ai.DownPayments,
SUM(CONVERT(INT, ai.Arrears)+CONVERT(INT, ai.LastPayment)) AS Downpmtlstpmt,
(CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.InstallmentAmount)), 0) END) AS TotalAmtPaidInstallments,
(CASE WHEN aii.Status = 'Paid' THEN ISNULL(SUM(CONVERT(INT, aii.Arrear)), 0) END) AS TotalArrearPaidInstallments
FROM
dbo.TblApplicant AS ap
JOIN
dbo.TblApplicantInitialPayments AS ai ON ai.ApplicantId = ap.ApplicantId
INNER JOIN
dbo.TblApplicantInstallments AS aii ON aii.ApplicantId = ap.ApplicantId
INNER JOIN
dbo.PaymentType AS Pt ON pt.PaymentTypeId = ap.PaymentTypeId
INNER JOIN
dbo.TblApplicantPlotPayments AS app ON app.ApplicantId = ap.ApplicantId
INNER JOIN
dbo.TblPlotPayments AS pp ON pp.PlotPaymentID = app.PlotPaymentID
INNER JOIN
dbo.TblPlotLocation AS pl ON pl.LocationId = app.LocationId
INNER JOIN
dbo.TblPlotType as ptt on ptt.PlotTypeId = app.PlotTypeId
WHERE
pt.PaymentTypeId = 1 AND ptt.PlotTypeId = @plotsize
GROUP BY
ap.ApplicantName, ap.ApplicantId, ptt.PlotTitle,
pl.LocationTitle, aii.Status,
ap.PlotSerialNo, pp.TotalPayment, ap.concession,
ai.DownPayments, ai.Arrears, ap.RegDate, pt.Title
ORDER BY
ptt.PlotTitle, pl.LocationTitle