question

nidheesh avatar image
nidheesh asked

How can I do this using an cte.I am using sql 2005.Pls help me.

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.

t-sqlcte
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

may help if you could provide some table definitions and some sample data
0 Likes 0 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered

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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - good stab given the starting point... nearly on that 6k now - congrats for when you get your next 7 rep! :)
0 Likes 0 ·
@Matt, cheers. Looks like someone just tipped me over the edge. :D
0 Likes 0 ·
I agree with Matt - this is a good first stab. +1
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.