Nsryjdtyk

I have some data that shows shift span details for my workers. I am trying to figure a way, if there is one, to establish the first day startdaynum of the span as 1 and then appropriately label any proceeding enddaynum or startdaynum after that to be 2 if the start date/time or end date/time is 1 day after the start, or 3 if the dates are 2 days after the start, so on and so forth.

Below I have some sample data, all of it is real data except for the startdaynum and enddaynum columns that I have manually populated to show what I am trying to achieve. The last column shiftsegmentid is unique to every row. The second to last column shiftcodeid is unique to each shift. So that would be the one where we could determine if the row we are evaluating is a continuance of the previous row or the first row of a shift, if this could be done with something analytically.

The first 2 rows are all 1 shift where all the start end dates are the same. The second 2 rows are 1 shift together where the first end date is the day after the first start date, so I want startdaynum=1 but enddate=2. Row 2 are both the day after the first start date of that shift so the should be startdaynum=2 and enddate=2, as displayed.

NAME    DESCRIPTION TYPE     STARTDAYNUM STARTTIME           ENDDAYNUM ENDTIME             SHECDHOURS SKILLSANDCERTS TRANSFER     SHIFTCODEID SHIFTSEGMENTID

------- ----------- -------- ----------- ------------------- --------- ------------------- ---------- -------------- ------------ ----------- --------------

1009ABC Pattern 1   Transfer           1 01/01/1900 05:00 am         1 01/01/1900 01:30 pm        8.5                ///800505///        3050           4052

1009ABC Pattern 1   Transfer           1 01/01/1900 01:30 pm         1 01/01/1900 02:30 pm          1                ///800855///        3050           4053

1009XYZ Pattern 2   Transfer           1 01/01/1900 05:00 pm         2 01/02/1900 01:30 am        8.5                ///800505///        3070           4072

1009XYZ Pattern 2   Transfer           2 01/02/1900 01:30 am         2 01/02/1900 02:30 am          1                ///800855///        3070           4073

Based on your sample data and comments, the start and end time columns already include the information you're looking for in the nominal date part, so you don't need to do anything complicated to find adjaacent records etc.

You can just use:

extract(day from starttime) as startdaynum

and

extract(day from endtime) as enddaynum

Demo:

with your_table (name, description, type, starttime, endtime, shecdhours, skillsandcerts, transfer, shiftcodeid, shiftsegmentid) as (

  select '1009ABC', 'Pattern 1', 'Transfer', to_date('01/01/1900 05:00 am', 'MM/DD/YYYY HH:MI am'), to_date('01/01/1900 01:30 pm', 'MM/DD/YYYY HH:MI am'), 8.5, null, '///800505///', 3050, 4052 from dual

  union all

  select '1009ABC', 'Pattern 1', 'Transfer', to_date('01/01/1900 01:30 pm', 'MM/DD/YYYY HH:MI am'), to_date('01/01/1900 02:30 pm', 'MM/DD/YYYY HH:MI am'), 1, null, '///800855///', 3050, 4053 from dual

  union all

  select '1009XYZ', 'Pattern 2', 'Transfer', to_date('01/01/1900 05:00 pm', 'MM/DD/YYYY HH:MI am'), to_date('01/02/1900 01:30 am', 'MM/DD/YYYY HH:MI am'), 8.5, null, '///800505///', 3070, 4072 from dual

  union all

  select '1009XYZ', 'Pattern 2', 'Transfer', to_date('01/02/1900 01:30 am', 'MM/DD/YYYY HH:MI am'), to_date('01/02/1900 02:30 am', 'MM/DD/YYYY HH:MI am'), 1, null, '///800855///', 3070, 4073 from dual

)

select name,

  description,

  extract(day from starttime) as startdaynum,

  to_char(starttime, 'HH:MI am') as starttime,

  extract(day from endtime) as enddaynum,

  to_char(endtime, 'HH:MI am') as endtime,

  shecdhours,

  skillsandcerts,

  transfer,

  shiftcodeid,

  shiftsegmentid

from your_table;



NAME    DESCRIPTI STARTDAYNUM STARTTIM  ENDDAYNUM ENDTIME  SHECDHOURS SKILLSANDCERTS TRANSFER     SHIFTCODEID SHIFTSEGMENTID

------- --------- ----------- -------- ---------- -------- ---------- -------------- ------------ ----------- --------------

1009ABC Pattern 1           1 05:00 AM          1 01:30 PM        8.5                ///800505///        3050           4052

1009ABC Pattern 1           1 01:30 PM          1 02:30 PM          1                ///800855///        3050           4053

1009XYZ Pattern 2           1 05:00 PM          2 01:30 AM        8.5                ///800505///        3070           4072

1009XYZ Pattern 2           2 01:30 AM          2 02:30 AM          1                ///800855///        3070           4073