Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
DECLARE @MinInstallments VARCHAR(100) 
DECLARE @CustCNIC VARCHAR(100) 
 
Select @CustCNIC =Customer_CNIC from Customer 
 
SELECT @MinInstallments = MIN(Installment_No) 
FROM Installments AS i 
WHERE Loan_id IN 
(SELECT Loan_id FROM Customer WHERE Customer_CNIC = @CustCNIC);
 
SELECT CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID, 
'Customer_CNIC' = CASE WHEN i.Installment_no = @MinInstallMents THEN @CustCNIC ELSE ''END, i.Installment_no, i.Date, i.Amount 
FROM Installments i, Loan l, Customer c 
WHERE l.Loan_id = c.Loan_id and c.Loan_id = i.Loan_id and c.Customer_CNIC=
@CustCNIC ORDER BY l.Loan_id,i.Installment_no
Posted 9-Jan-13 13:39pm
Edited 9-Jan-13 14:11pm
v3
Comments
PIEBALDconsult at 9-Jan-13 18:58pm
   
"Last one" what? How many should there be? What database system?
jibesh at 9-Jan-13 19:49pm
   
Check this http://www.codeproject.com/Questions/524904/whereplusisplusproblemplusinplusqueryplus-3fplushe
for more :)
PIEBALDconsult at 9-Jan-13 21:07pm
   
Nah.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this
 
DECLARE @CustMinLoanInstallments AS TABLE(Customer_CNIC VARCHAR(100),Loan_Id VARCHAR(100), 
MinInstallments BIGINT) 
 
INSERT INTO @CustMinLoanInstallments 
SELECT i.Loan_id , C.Customer_CNIC MIN(Installment_No)
FROM Installments AS i , Customer AS C 
WHERE i.Loan_id =C.Loan_id
 
SELECT CASE i.Installment_no WHEN cmli.MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID,
'Customer_CNIC' = CASE WHEN i.Installment_no = cmli.MinInstallments THEN @CustCNIC ELSE ''END, i.Installment_no, i.Date, i.Amount
FROM Installments i, Loan l, Customer c ,@CustMinLoanInstallments cmli 
WHERE l.Loan_id = c.Loan_id and c.Loan_id = i.Loan_id 
  AND cmli.Loan_id = i.Loan_id and cmli.Customer_CNIC = c.Customer_CNIC
 ORDER BY l.Loan_id,i.Installment_no
 
Hope this helps
--RDBurmon
  Permalink  

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

  Print Answers RSS
0 Peter Leow 295
1 Mika Wendelius 240
2 CHill60 195
3 TheRealSteveJudge 180
4 Zoltán Zörgő 178
0 Sergey Alexandrovich Kryukov 8,698
1 OriginalGriff 6,591
2 Peter Leow 3,982
3 Zoltán Zörgő 3,634
4 Richard MacCutchan 2,535


Advertise | Privacy | Mobile
Web04 | 2.8.150123.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100