1

I am trying to find overlapping dates within 10-days of the FROM_DATE for the same PERSON_ID but that occurred under a different MANAGER_ID OR SHIFT_ID. This is missing a number of dates which would show because they all fall 10-days before an instance where there is a different MANAGER_ID OR SHIFT_ID.

I created a DAYS_TABLE with the SET_DATE (FROM_DATE) and TEN_DAYS, which is 10-days from the SET_DATE.

SELECT DISTINCT
F.*
FROM #MAIN_TABLE F
INNER JOIN #DAYS_TABLE O
ON (F.PERSON_ID = O.PERSON_ID)
AND(F.ORDER_NBR != O.ORDER_NBR)
AND (NOT((F.MANAGER_ID = O.MANAGER_ID) OR
         (F.SHIFT_ID = O.SHIFT_ID)))

WHERE (F.FROM_DATE BETWEEN O.SET_DATE AND O.TEN_DAYS
);

I expect to see all instances where there is a PERSON_ID with date overlaps (10 days forward) for either a different SHIFT_ID or different MANAGER_ID. This would include all dates that maybe under the same SHIFT_ID or MANAGER_ID but fall within the 10-days where there is a difference.

PER_ID  SHFT_ID MGR_ID  FROM    
1   A   U1  1/1/2019    
2   A   U1  1/1/2019    
1   A   U2  1/12/2019   
2   B   U2  1/12/2019   
1   B   U3  1/11/2019   


PER_ID  SHFT_ID MGR_ID  FROM    10_DAYS
1   A   U2  1/12/2019   1/22/2019
1   B   U3  1/11/2019   1/21/2019
  • please add some sample input and output data – DarkRob Aug 05 '19 at 04:20
  • I'm totally lost. Where do the dates 1/22/2019 and 1/21/2019 come from? They are not in your original data. – Gordon Linoff Aug 05 '19 at 11:43
  • It’s 10 days forward from the from date. I set this in the days table. Anyway I may now have an answer by gathering the order number and the overlapping order number side by side that meet the criteria mentioned above. Sorry if this was not clear. –  Aug 05 '19 at 12:56
  • The names of the columns in your sample data (PER_ID, SHFT_ID, etc.) do not match those in your question, nor is it clear what that data is. – John Aug 12 '19 at 01:16
  • John, those names refer to shift id and person id. The data in this regards is for various scenarios occurring where there overlaps which means a date occurs within a 10 day window of a prior date. The scenarios would be a different shift id or different manager id to the prior date. I have resolved the issue but I appreciate the question. Have a great day. –  Aug 13 '19 at 13:00

0 Answers0