SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get Previuos unpaid Customer id
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paritosh
Starting Member

India
42 Posts

Posted - 05/09/2013 :  09:36:07  Show Profile  Reply with Quote
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.....

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/09/2013 :  10:53:24  Show Profile  Reply with Quote
Here is a solution:

SELECT DISTINCT T.CUSTOMERID from @TABLE T, @TABLE T1 where 
		T.CUSTOMERID = T1.CUSTOMERID and T.PAIDAMT = 0 and 
                (T1.PAIDAMT <> 0 and T.INSTALMENTNO = T1.INSTALMENTNO -1)
	order by T.CUSTOMERID;
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/09/2013 :  23:01:58  Show Profile  Reply with Quote
to get latest customer that does not pay current installment buy paid previous installment.

select customerid
from (
	select * 
	from (
		select *, 1-INSTALMENTNO+max(INSTALMENTNO) over (partition by CUSTOMERID)rn
		from @TABLE
	)a
	where rn in (1, 2)
	and ((rn = 1 and PAIDAMT = 0) or (rn = 2 and PAIDAMT <> 0))
)a
group by CUSTOMERID
having count(*) = 2

modify from khtan

; with cte as
(
 select *, 
  rn = row_number() over (partition by CUSTOMERID order by INSTALMENTNO desc)
 from @TABLE
)
select CUSTOMERID
from cte
where rn in (1, 2)
and (
  (rn = 1 and PAIDAMT = 0)
 or (rn = 2 and PAIDAMT <> 0)
 )
group by CUSTOMERID
having count(*) = 2
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000