How to set Flag based on days rage and order in SQL

Question

Sample table (incident) have the following data,

+-----+-------------+------+----------+
| Ref |  In_Date    | Item | Customer |
+-----+-------------+------+----------+
|   1 | 5-Apr-2018  | DELL | ABC      |
|   2 | 11-Apr-2018 | DELL | ABC      |
|   3 | 13-Apr-2018 | DELL | ABC      |
|   4 | 19-Apr-2018 | DELL | ABC      |
|   5 | 25-Apr-2018 | DELL | ABC      |
|   6 | 27-Apr-2018 | DELL | ABC      |
|   7 | 29-Apr-2018 | DELL | ABC      |
|   8 | 10-Apr-2018 | HP   | XYZ      |
|   9 | 12-Apr-2018 | HP   | XYZ      |
|  10 | 30-Apr-2018 | IBM  | JKL      |
+-----+-------------+------+----------+

As per below query,will get the similar incidents (By Item & Customer) of count > 1.

SELECT * FROM
(
    SELECT Ref, In_Date, Item, Customer,
    COUNT(*) OVER (PARTITION BY Customer, Item) AS cnt
    FROM Incident
) t
WHERE cnt > 1
ORDER BY Item, Customer;

I want to set a Flag for each result row with the following condition,

Flag "0" when the day difference with its nearest date with more than 3 days in similar incident group (eg: Ref 1 & 4 result table)
Flag "2" for the last date in similar incident group if it have nearest 3 days(eg: Ref : 3,7 & 9)
Flag "1" for 3 days difference with latest date (eg: ref: 2, 5 ,6 & 8)

Expected Result:

+-----+-------------+------+----------+------+
| Ref |   In_Date    | Item | Customer | Flag |
+-----+-------------+------+----------+------+
|   1 | 5-Apr-2018  | DELL | ABC      |    0 |
|   2 | 11-Apr-2018 | DELL | ABC      |    1 |
|   3 | 13-Apr-2018 | DELL | ABC      |    2 |
|   4 | 19-Apr-2018 | DELL | ABC      |    0 |
|   5 | 25-Apr-2018 | DELL | ABC      |    1 |
|   6 | 27-Apr-2018 | DELL | ABC      |    1 |
|   7 | 29-Apr-2018 | DELL | ABC      |    2 |
|   8 | 10-Apr-2018 | HP   | XYZ      |    1 |
|   9 | 12-Apr-2018 | HP   | XYZ      |    2 |
+-----+-------------+------+----------+------+

Look at LEAD. https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017 — Sean Lange, Aug 08 '18 at 16:49
Well then you have to jump through some extra hoops. You can use ROW_NUMBER in a cte and join it to itself on rownum + 1 — Sean Lange, Aug 08 '18 at 17:05
You have to explain the rules more clearly. What does "incident group" mean? Why is ref 3 not returning 0? The next date is 6 days later. Or do you have to check it is within 3 days before and after? The logic isn't too difficult here but the rules are not making sense to me. — Sean Lange, Aug 08 '18 at 18:49
incident group: Same Item and Customer, Why is ref 3 not returning 0: 2 days difference between the 2 & 3 and 3 the last value in that group (We have have to check it is within 3 days with latest date) — Sam Bin Ham, Aug 09 '18 at 04:28
@SamBinHam Why Ref 2 is not Flag 2. It has Ref 3 within 3 days. — AB_87, Aug 09 '18 at 04:34
@AB_87: I have to check the days within 3 days before and after, If Not found then it should be 2 — Sam Bin Ham, Aug 09 '18 at 04:36
Sorry but your rules are not making any sense to me. Rules are not matching with expected output. — AB_87, Aug 09 '18 at 05:02

Dominic Hemken · Answer 1 · 2018-08-08T18:03:24.937

As Sean said in the comments, join the table to itself using a join

something like this:

SELECT
        THISRECORD.Ref,
        THISRECORD.In_Date,
        THISRECORD.Item,
        THISRECORD.Customer,
        CASE WHEN (datediff(day,lASTRECORD.In_Date,THISRECORD.In_Date) > 3)  THEN 0
        WHEN (datediff(day,lASTRECORD.In_Date,THISRECORD.In_Date) = 3)  THEN 1
        ELSE 2 END AS Flag

FROM 
    MYTABLE AS THISRECORD
LEFT JOIN MYTABLE AS lASTRECORD
ON THISRECORD.REF-1 = lASTRECORD.REF

score 0 · Answer 2 · answered Aug 10 '18 at 10:35

How about this query:

;with cte as 
(SELECT Ref,E_date,
Item,Customer
FROM
       (SELECT Ref, E_Date, Item, Customer,
       COUNT(*) OVER (PARTITION BY Customer, Item) AS cnt
       FROM incident
       ) t
WHERE cnt > 1
)

select a.Ref,a.E_Date,a.Item, a.Customer,
case when ((abs(DATEDIFF(dd,b.E_Date,a.E_Date))>3 or DATEDIFF(dd,b.E_Date,a.E_Date) is null ) and abs(DATEDIFF(dd,a.E_Date,c.E_Date))<=3)
          or (abs(DATEDIFF(dd,b.E_Date,a.E_Date))<=3 and abs(DATEDIFF(dd,a.E_Date,c.E_Date))<=3) then 1
when (abs(DATEDIFF(dd,b.E_Date,a.E_Date))<=3 and (abs(DATEDIFF(dd,a.E_Date,c.E_Date))>3 or abs(DATEDIFF(dd,a.E_Date,c.E_Date)) is null  )) then 2
else 0 end as Flag
from cte a  
left join cte b on a.Ref=b.Ref+1 
left join cte c on a.Ref=c.Ref-1
group by a.Item,a.Customer,a.REf,a.E_Date,b.E_Date,c.E_Date

How to set Flag based on days rage and order in SQL

2 Answers2