0

I am currently working on data extract from Kronos Leave Module. The data is about Employee, which dates he took leaves what is the reason and status. So the dates are where the challenge is as I need to convert the dates to a range of dates instead day by day which is currently been pulled.

Current File: 

Absence_ID  |Person_num  |Work_type   |Leave_type  |Segement_Start_Date  |Segement_End_Date   |Status
------------|------------|------------|------------|---------------------|--------------------|-----------------------
11617       |200146      |C           |VERMOUNT    |01/28/2019 0:00      |01/28/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |01/29/2019 0:00      |01/29/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |01/30/2019 0:00      |01/30/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |01/31/2019 0:00      |01/31/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |02/01/2019 0:00      |02/01/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |02/04/2019 0:00      |02/04/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |02/05/2019 0:00      |02/05/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |02/06/2019 0:00      |02/06/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |02/07/2019 0:00      |02/07/2019 0:00     |Closed-Returned to Work
11617       |200146      |C           |VERMOUNT    |02/08/2019 0:00      |02/08/2019 0:00     |Closed-Returned to Work

Corrected: 

Absence_ID  |Work_type   |Leave_type  |Segement_Start_Date |Segement_End_Date   |Status  
------------|------------|------------|--------------------|--------------------|--------
11617       |C           |VT-FML      |01/28/2019          |02/08/2019          |Approved
Maddy Badsheshi
  • 1
  • 2
    Have a look for "gaps and islands" or "contiguous dates to range" - this kind of question is asked quite frequently. https://stackoverflow.com/questions/24289958/find-start-and-stop-date-for-contiguous-dates-in-multiple-rows , https://stackoverflow.com/questions/27471514/group-continuous-date-ranges-from-same-table-sql-server , https://stackoverflow.com/questions/20402089/detect-consecutive-dates-ranges-using-sql For example – Caius Jard Oct 08 '19 at 20:14
  • I think you forgot to ask a question here, along with explain your goals and show your attempts. I can't see how that data related at all (especially as they have different values for `Status`). – Thom A Oct 08 '19 at 20:15
  • My first guess would be a group by Absence_ID, work type, leave type, selecting min start date and max end date. But I bet there are more important details that you did no mention. For example, when is the status "approved"? – heringer Oct 08 '19 at 20:23
  • @heringer your idea worked for me with min and max ..thank you – Maddy Badsheshi Oct 11 '19 at 05:49
  • Nice! I will post is an answer for further reference. – heringer Oct 11 '19 at 12:18

1 Answers1

1

Group by Absence_ID, work type, leave type, selecting min start date and max end date.

heringer
  • 2,698
  • 1
  • 20
  • 33