Get Previuos unpaid Customer id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get Previuos unpaid Customer id

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2013-05-09 : 09:36:07

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

549 Posts

Posted - 2013-05-09 : 10:53:24

Here is a solution:
[CODE]
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;
[/CODE]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-09 : 23:01:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources