There is chequedate denotes the date cheque to be settled and the settled date is the actual settled date.
If a customer is not settled 3 cheques continuously and then he will be a non-performing customer(here processingId). ie,in between two settlement dates if there is 3 cheque dates he will be non-performer
How can I do this using an cte.I am using sql 2005.Pls help me.
You have not given us much to go on but this may be what you are looking for.
IF object_id('checks') > 0
BEGIN
TRUNCATE TABLE checks
END
ELSE
BEGIN
CREATE TABLE checks
(
customerid INT,
checkdate DATETIME,
settledate DATETIME null
)
END
INSERT INTO checks
SELECT 1,
'2010-apr-20',
'2010-apr-23'
union
SELECT 2,
'2010-apr-20',
'2010-apr-21'
union
SELECT 1,
'2010-apr-21',
null
union
SELECT 3,
'2010-apr-20',
'2010-apr-23'
union
SELECT 3,
'2010-apr-22',
null
union
SELECT 2,
'2010-apr-20',
null
union
SELECT 1,
'2010-apr-25',
null
union
SELECT 3,
'2010-apr-20',
'2010-apr-23'
union
SELECT 1,
'2010-apr-28',
null
union
SELECT 2,
'2010-apr-20',
null
union
SELECT 2,
'2010-apr-20',
null
union
SELECT 3,
'2010-apr-20',
NULL
union
SELECT 3,
'2010-apr-20',
'2010-apr-23' ;
WITH defaulters
AS ( SELECT customerid,
ROW_NUMBER() OVER ( PARTITION BY customerid,
COALESCE(settledate, '01-01-01') ORDER BY settledate ) AS Unsettled
FROM checks
)
SELECT *
FROM [defaulters]
WHERE [defaulters].[Unsettled] >= 3
