Can I perform the same functionality as this loop in sql?

user_2495305 0
Viewed 64

What I'm doing is checking for a gap between several dates, and calling the result a duplicate if there is not a 14 day gap from the last non-duplicate result.

The code looks like this in coldfusion:

<cfset date_check = data.date_to_check />
    <cfset counted = 1 />
    <cfloop query="data" > 

        #dateformat( date_to_check )# <br> 

        <cfif abs( dateDiff('d', date_check , data.date_to_check ) ) gt 14 > 
            <cfset counted ++ />
            <cfset date_check = data.date_to_check />
            Not Duplicate
        <cfelseif currentrow gt 1> 
            Duplicate
        <cfelse> 
            Not Duplicate
        </cfif>

    </cfloop>

    > #counted#

And the output, for example will be:

19-Jan-18 Not Duplicate
16-Jan-18 Duplicate
21-Oct-16 Not Duplicate
12-Oct-16 Duplicate
06-Oct-16 Not Duplicate
22-Sep-16 Duplicate
09-Aug-16 Not Duplicate
11-Jul-16 Not Duplicate
> 5

I tried using outer apply and joining the next row that is 14 days from the current row. But the problem with this approach is if I have a cluster, it "resets" the date each row, like this:

19-Jan-18 - not duplicate
16-Jan-18 - duplicate 
10-Jan-18 - will give false duplicate ( compares itself to Jan 16 instead of Jan 19 )

The query for this is something like this:

SELECT 
    count(*)
FROM @item T1
    OUTER APPLY ( 
        SELECT TOP 1 * 
        FROM @item T2 
        WHERE T2.[index] < T1.[index]  
        ORDER BY T2.[index] DESC) T
WHERE DATEDIFF(DAY, T.[date], T1.[date])  > 14
coldfusion sql-server-2016
1 Answers

I think an EXISTS subquery would be the most straightforward way to do it. If that isn't performant enough, then you could do a join for it, but I find that EXISTS is generally fast enough and much easier to read.

 SELECT     ID,
            Name,
            Date_To_Check,
            CASE
                WHEN        EXISTS (
                                SELECT  1
                                FROM    Table1 b
                                WHERE   b.Date_To_Check < a.Date_To_Check
                                    AND DATEDIFF(d,b.Date_To_Check,a.Date_To_Check) <= 14
                            )
                    THEN    1
                ELSE        0
            END AS isDuplicate
 FROM       Table1 AS a
user_12307430
Related