Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
LOAN_ID	Customer_CNIC	Installment_no	Date	        Amount
1234-55	12345-1234567-8	        1	2013-02-04	648700
	12345-1234567-8	        2	2013-03-04	648700
	12345-1234567-8	        3	2013-04-04	648700
	12345-1234567-8	        4	2013-05-04	648700
	
 
but i want as
 
LOAN_ID    Customer_CNIC      Installment_no  Date        Amount
1234-55    12345-1234567-8          1         2013-02-04  648700
                                    2         2013-03-04  648700
                                    3         2013-04-04  648700
                                    4         2013-05-04  648700
    
 
here is query
 
DECLARE @MinInstallments VARCHAR(100)
 
SELECT @MinInstallments = MIN(Installment_No)
            FROM Installments WHERE Loan_id IN 
            (SELECT Loan_id FROM Customer WHERE Customer_CNIC = '12345-1234567-8');
             Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID,c.Customer_CNIC, 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 = '12345-1234567-8' order by l.Loan_id,i.Installment_no;
Posted 9-Jan-13 10:40am
Comments
richcb at 9-Jan-13 16:46pm
   
What is the error you are getting?
zeshanazam at 9-Jan-13 16:53pm
   
no error but i m getting results as
 
LOAN_ID Customer_CNIC Installment_no Date Amount
1234-55 12345-1234567-8 1 2013-02-04 648700
12345-1234567-8 2 2013-03-04 648700
12345-1234567-8 3 2013-04-04 648700
12345-1234567-8 4 2013-05-04 648700
 
instead of
LOAN_ID Customer_CNIC Installment_no Date Amount
1234-55 12345-1234567-8 1 2013-02-04 648700
2 2013-03-04 648700
3 2013-04-04 648700
4 2013-05-04 648700
Sergey Alexandrovich Kryukov at 9-Jan-13 16:52pm
   
First, you are not explaining what do you want to achieve. Not a valid question.
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

It should works, but it's not optimized:
DECLARE @MinInstallments VARCHAR(100)
DECLARE @CustCNIC VARCHAR(100)
 
SET @CustCNIC = '12345-1234567-8'
 
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;
 
In my opinion, never use CASE ... WHEN ... END to make output query more 'readible' or for 'visualize' data. There are many tools to do it, for example: ReportViewer[^]. Final resultset should looks like:
LOAN_ID	Customer_CNIC	Installment_no	Date	        Amount
1234-55	12345-1234567-8	        1	2013-02-04	648700
1234-55 12345-1234567-8	        2	2013-03-04	648700
1234-55 12345-1234567-8	        3	2013-04-04	648700
1234-55 12345-1234567-8	        4	2013-05-04	648700
and it's totally corect! There is nothing wrong with your query.
  Permalink  
v3
Comments
zeshanazam at 9-Jan-13 17:49pm
   
SELECT CASE i.Installment_no
WHEN @MinInstallments THEN i.Loan_id
ELSE ''
END AS LOAN_ID, CASE 'Customer_CNIC'
WHEN i.Loan_id = @MinInstallMents THEN @CustCNIC
 

Incorrect syntax near '='.
Maciej Los at 9-Jan-13 17:54pm
   
Check it now!
Replace CASE 'Customer_CNIC' = with 'Customer_CNIC' = CASE
Sorry, my mistake.
zeshanazam at 9-Jan-13 17:57pm
   
Customer_CNIC column is appearing but blank..
Maciej Los at 9-Jan-13 18:01pm
   
Replace:
WHEN Loan_id = @MinInstallMents THEN @CustCNIC
with
WHEN i.Installment_no = @MinInstallMents THEN @CustCNIC statement.
zeshanazam at 9-Jan-13 18:05pm
   
its working, thanks a lot :)
Maciej Los at 9-Jan-13 18:15pm
   
You're welcome.
zeshanazam at 9-Jan-13 18:26pm
   
but what if i want to retrieve all records...
Maciej Los at 10-Jan-13 16:38pm
   
How about PIVOT tables?
Example data:
LOAN_ID Customer_CNIC 2013-02-04 2013-03-04 2013-04-04 2013-05-04
1234-55 12345-1234567-8 648700 648700 648700 648700
Mika Wendelius at 11-Jan-13 13:22pm
   
Good answer, 5.
Maciej Los at 11-Jan-13 17:12pm
   
Thank you, Mika ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Quote:
Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID
Probably (I am not an expert) you have to replicate the above logic on Customer_CNIC field. Namely replace
c.Customer_CNIC
with
Select CASE i.Installment_no WHEN @MinInstallments THEN c.Customer_CNIC ELSE '' END AS CUSTOMER_CNIC
  Permalink  
Comments
zeshanazam at 9-Jan-13 17:13pm
   
i have done it. but only c.customer_CNIC appears only once not loan_id, i want both Customer_CNIC,Loan_id should appear once
CPallini at 9-Jan-13 17:34pm
   
Did you enter two SELECT CASE statements, namely:
Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID, Select CASE i.Installment_no WHEN @MinInstallments THEN c.Customer_CNIC ELSE '' END AS CUSTOMER_CNIC
?
zeshanazam at 9-Jan-13 17:39pm
   
when i enter two select, two tables appears, but i want to get data only in one table...

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 6,805
1 OriginalGriff 6,696
2 CPallini 5,350
3 George Jonsson 3,609
4 Gihan Liyanage 2,792


Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2014
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