Search code examples
sqlsql-server

Using Lag / Lead to highlight records that have repeating data

I have data that records movements, such as

ID Name       DateDone             Moved_To     By
01 John Smith 2023-08-03T15:30:23  Left         JP --EG1
01 John Smith 2023-08-03T17:41:45  Right        KS --EG2

01 John Smith 2023-08-03T18:58:42  Still        KS 
01 John Smith 2023-08-03T20:59:55  Still        KS --EG2

02 Tina Platt 2023-08-04T03:11:34  Left         PK
02 Tina Platt 2023-08-04T03:19:10  Left         MO --EG3

02 Tina Platt 2023-08-04T06:19:49  Right        MO  
02 Tina Platt 2023-08-04T08:57:14  Left         MO --EG4

02 Tina Platt 2023-08-04T14:05:12  Right        PK 
02 Tina Platt 2023-08-04T14:11:52  Right        MO --EG5

02 Tina Platt 2023-08-04T16:43:02  Right        MO 
02 Tina Platt 2023-08-04T16:45:49  Right        PK --EG6

02 Tina Platt 2023-08-05T13:21:10  -            SO --EG7

Customer John Smith (01) needs one person to move his item, whereas Tina Platt (02) needs two. This is derived from the [Customers] table, and the field [num_of_ppl].

I want to highlight certain rows based on the following rules:

  • if a customer's product needs to be moved by 2 people, then two staff must both log the movement within 15mins of one another. Therefore, if the second entry (with the same Moved_To value) is logged more than 15mins after, flag the most recent row;
  • if the Moved_To field is the same as the previous one for that customer, whether 1 or 2 people, flag the row / most recent row for the 2 people customers), UNLESS Moved_To = 'Still', in which case ignore;
  • if a 1 person movement (or the most recent of the two 2person logs for 2person customers) is recorded more than 4hrs after the last instance, flag the most recent row;
  • if a Moved_To wasn't recorded, flag the row / most recent row.

In the data example, the results SHOULD filter as follows:

  • Eg 1 - 1person move, within 4hr of last and no repeating of Moved_To - don't flag
  • Eg 2 - 1person move, within 4hr of last and Moved_To = 'Still' - don't flag
  • Eg 3 - 2person move, within 4hr of last group and <15min of each other - don't flag
  • Eg 4 - 2person move, within 4hr of last group but only 1 logged - FLAG
  • Eg 5 - 2person move, more than 4hr since last group, but 2person log - FLAG
  • Eg 6 - 2person move, within 4hr of last group, logged by 2 but same as last group - FLAG
  • Eg 7 - 2person move, no Moved_To recorded - FLAG.

My code is still at its early stage at the moment, but essentially:

select
case when ID = lag(ID) over(order by DateDone)
   and moved_to = lag(moved_to) over(order by DateDone) 
   and moved_to != 'Still'
or 
   moved_to = lead(ID) over(order by DateDone)
   and moved_to = lean(moved_to) over(order by DateDone)
   and moved_to != 'Still'
then 'Repeated Move_To' end as "Flag", *
from [Movements]

Any help offered would be appreciated. Thanks.

The expected results should be:

ID Name       DateDone             Moved_To    By  Issue
02 Tina Platt 2023-08-04T14:15:11  Right       MO  No 2nd
02 Tina Platt 2023-08-04T14:11:52  Right       MO  >4hrs
02 Tina Platt 2023-08-04T16:45:49  Right       PK  Repeat
02 Tina Platt 2023-08-05T13:21:10  -           SO  No Pos
Solution

  • In the end I did it myself, and it does seem to work so far. This is my complete code, not just the simplified version I posted above...

     DROP TABLE IF EXISTS #Results999
                CREATE TABLE #Results999
            (
                Name nvarchar(100),
                id nvarchar(10), 
                DateDone nvarchar(20), 
                [Moved To] nvarchar(100), 
                [By] nvarchar(100)
            )

            INSERT #Results999 EXEC sp_get_movementslast60d @home_name = @home_name

 select 
     * 
 from 
 (
     select 
         case 
         when 
              r99.id = lag(r99.id) over(order by r99.id, datedone) 
         and 
         (
              [moved to] = lag([moved to]) over(order by r99.id, datedone) 
         )
         and [moved to] not in ('S', 'I') --barring those actions
    then 'Repeated Reposition'

    when
        r99.id = lag(r99.id) over(order by r99.id, datedone) 
        and datedone > dateadd(hour,4, lag(datedone) over(order by r99.id, datedone)) 

    then '> 4hrs Since Last'
  
    when 
        [moved to] is null 
    then 'No position specified' 
    
    when 
        [Outcome] = 'Declined' and [repositioned to?] =''  
    then 'Staff Refused' --I added this one later

    when 
        ras.textanswer = '1|2;' 
        and (r99.id = lag(r99.id) over(order by r99.id, datedone) 
        and datedone > dateadd(minute, 30, lag(datedone) over(order by r99.id, datedone))) 
    then 'No 2nd'

end as "Flag", r99.*

from 
#Results999 r99
left join 
[customers] r on r.id = r99.id
inner join 
[ras] on r.personid = ras.personid and ras.typename = 'OrdersInfo' and questionid = '11'

where 
action = 'Movement'
and 
 datedone >= dateadd(day,-2, getdate())
) subRP

where subRP.Flag != ''
order by subRP.lastname, subRP.datedone desc

    Probably far from perfect coding, but I've checked around 1000 entries now and it seems to pick up each of the issues in turn.