CODEWITHSUNDEEP
sqlsql-server-2012
Jess8766
Jess8766

Reputation: 417

Calculate rows that haven't been updated for 30 days

Hopefully someone can help with this. I have a table that looks like the below

CaseID      ClientID      ContactDate
23722         8283         01-JAN-2018
2322           233         04-FEB-2018
23382          833         06-FEB-2018
2324          8283         08-FEB-2018

What I want to do is create a stored procedure that contains a parameter called @DT (this will be a specified date). Once run, the stored procedure will get me a list of all ClientIDs that have no ContactDate prior to 30 days of the @DT parameter. This will be called contactflag. I also want it to check the table again to see if the Client had a ContactDate within 7 days AFTER the @DT parameter, this will be called HadNextContact flag. An ideal output would look like this, based on @DT being allocated the date of 7 Feb 2018;

 ClientID      ContactFlag      HadNextContact
 8283               1                1
 233                0                0
 833                0                0

The ContactFlag is set to 1 for ClientID = 8283 because they hadn't had a ContactDate within the 30 days before 7 Feb 2018. The HadNextContact flag was also set to was as they had a ContactDate within 7 days after 7 Feb 2018.

Hopefully someone can help? Appreciated as always!

Jess

Upvotes: 0

Views: 61

Answers (2)

D-Shih
D-Shih

Reputation: 46239

You can try to use condition aggregate function with DATEDIFF function.

SELECT ClientID,
       MAX(CASE WHEN DATEDIFF(DAY,ContactDate,@DT) > 30 THEN 1 ELSE 0 END) ContactFlag,
       MAX(CASE WHEN ContactDate BETWEEN @DT AND DATEADD(DAY,7,@DT) THEN 1 ELSE 0 END) HadNextContact
FROM T
GROUP BY ClientID

sqlfiddle

Upvotes: 2

George Menoutis
George Menoutis

Reputation: 7250

Try this:

declare @dt datetime='20180207'

select
    c.ClientID,
    case
        when exists 
        (
            select 1 
            from ClientCaseHistory tb -- TableBefore
            where tb.ClientID=c.ClientID and tb.ContactDate between dateadd(day,-30,@dt) and @dt
        ) 
    then 0 else 1 end as ContactFlag,
    case
        when exists 
        (
            select 1 
            from ClientCaseHistory ta -- TableAfter 
            where ta.ClientID=c.ClientID and ta.ContactDate between @dt and dateadd(day,7,@dt)
        ) 
     then 1 else 0 end as HadNextContact
from 
    --Get all ClientIDs. Maybe you have a better table to do this, a client table?
    (
        select distinct ClientID 
        from ClientCaseHistory t
    )c

Upvotes: 1

Related Questions