May 9, 2013 at 7:38 am
DECLARE @TABLE TABLE (CUSTOMERID INT,INSTALMENTNO INT,AMOUNT INT,PAIDAMT INT)
INSERT INTO @TABLE
VALUES (1000000001,1,100,100),(1000000001,2,100,0),(1000000001,3,200,200),(1000000001,4,300,300),
(1000000002,1,200,0),(1000000002,2,250,0),(1000000002,3,300,0),(1000000002,4,400,0),
(1000000003,1,100,0),(1000000003,2,250,250),(1000000003,3,300,0),(1000000003,4,400,400),
(1000000004,1,200,200),(1000000004,2,250,250),(1000000004,3,300,300),(1000000004,4,400,400)
SELECT * FROM @TABLE
Hi All,
I have a table which have data like mention in script .
i want to get only those customer Id whose PAIDAMT = 0 but previous installmentno PAIDAMT is not 0
So If U Run the above script
then i want to get only customer id : 1000000001,1000000003
because on 1000000001 id INSTALMENTNO = 1 and PAIDAMT = 100 But INSTALMENTNO = 2 and PAIDAMT = 0
thanks in advance.....
May 9, 2013 at 7:55 am
I think you just need to join the table to itself on t1.Customer = t2.Customer and t1.InvoiceNo = t2.InvoiceNo + 1.
John
May 9, 2013 at 8:10 am
Hi John,
Please provide in proper query to this problem ,
because i am new to sql server so please help to solve problem.....
May 9, 2013 at 8:17 am
I'm happy to help to solve the problem, but I'm not going to solve it for you. The best way for you to learn is to try it out yourself. Here's a link that you might find helpful. Have a go at writing the query, and post back if there's anything in particular that you don't understand.
John
May 9, 2013 at 9:52 am
Hi jhon,
Actually my problem is something different ,if you have any solution so please see problem and provide exact solution above provide query is not a solution pls check it........
May 9, 2013 at 10:22 am
anuj12paritosh (5/9/2013)
Hi jhon,Actually my problem is something different ,if you have any solution so please see problem and provide exact solution above provide query is not a solution pls check it........
Post what you have tried first and also post the results. It may be a simple fix.
May 9, 2013 at 12:27 pm
Is this what you want?
SELECT *
FROM @table AS t1
INNER JOIN @table AS t2
ON t1.customerid = t2.customerid
AND t2.INSTALMENTNO = t1.INSTALMENTNO-1
WHERE t2.PAIDAMT =0
AND t1.PAIDAMT <> 0
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply