CODEWITHSUNDEEP
sqlcommon-table-expression
Raj Jayaswal
Raj Jayaswal

Reputation: 478

t-sql to find repeated callers in customer care

I have situation where I need to find out the numbers which called in a customer care by same number within 2 days just for the current month. For example:

Number      dateTime
0987654321  2015-06-16 16:16:13.877
0987654321  2015-06-15 12:16:13.877
0789386834  2015-06-01 16:16:13.877
0789386834  2015-06-16 16:16:13.877
0987654321  2015-06-01 12:16:13.877
0123456789  2015-06-01 12:16:13.877
0123456789  2015-06-06 12:16:13.877
0123456789  2015-06-16 12:16:13.877

Here in this, I need to capture the number 0987654321 as he called on 15th and 16th (Less 2 days gap). Rest, 0123456789 and 0789386834 will not be included in the result as their calls were not made within 2 days. I am trying using CTE but it says memory out of exception.

PS: It has more than 2 millions of records.

Upvotes: 0

Views: 367

Answers (1)

user4622594
user4622594

Reputation:

I get the expected result with this query:

-- dummy table   
DECLARE @tab TABLE
    (
        number VARCHAR(100),
        timeofcall DATETIME
    )

--insert sample values
    INSERT INTO @tab 
    VALUES
    ('0987654321','2015-06-16 16:16:13.877'),
    ('0987654321','2015-06-15 12:16:13.877'),
    ('0789386834','2015-06-01 16:16:13.877'),
    ('0789386834','2015-06-16 16:16:13.877'),
    ('0987654321','2015-06-01 12:16:13.877'),
    ('0123456789','2015-06-01 12:16:13.877'),
    ('0123456789','2015-06-06 12:16:13.877'),
    ('0123456789','2015-06-16 12:16:13.877')

--get records who are present in the table within 2 days
    SELECT  *
    FROM    @tab t
    WHERE   EXISTS(SELECT   TOP 1 1 
                    FROM    @tab t2 
                    WHERE   t2.number = t.number 
                            AND t2.timeofcall <> t.timeofcall
                            AND ABS(DATEDIFF(DAY,t2.timeofcall,t.timeofcall)) <= 2)

Upvotes: 1

Related Questions