Click here to Skip to main content
11,638,764 members (72,845 online)
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 12:39pm
Edited 9-Jan-13 13: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 Sergey Alexandrovich Kryukov 314
1 stibee 233
2 Suvendu Shekhar Giri 230
3 Richard Deeming 190
4 Mika Wendelius 178
0 OriginalGriff 9,061
1 Sergey Alexandrovich Kryukov 8,773
2 Mika Wendelius 7,027
3 Suvendu Shekhar Giri 2,494
4 F-ES Sitecore 2,438


Advertise | Privacy | Mobile
Web03 | 2.8.150728.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