ARP - Migrate Shift data from ARP to FSM

Hi @Shneor Cheshin 

I’m pretty sure ‘Back in the day’ is reserved from the period about 20 to 25 years ago 😂.

I’ve kind of managed to do this using a SQL Insert statement. Which gives me the current shift plan from ARP into FSM. So at least the shifts are now showing on the schedule board. In my DEV environment i’m going to see if I can generate the shifts when ARP produces them on a daily basis through a Scheduled SQL job and see if that does the trick.

Thanks for the reply though.

Ady

Hi @Shneor Cheshin 

I’m sure there could be a more graceful way.

But I have left this to run over the weekend and daily shifts are being created in the FSM database.

SET ANSI_WARNINGS OFF;

INSERT INTO [Metrix_DEV].[dbo].[shift]
(shift_id, start_dttm, end_dttm, calendar_id, resource_id, person_id, team_id)

select   (select coalesce(max([Metrix_DEV].[dbo].[shift].[shift_id]),0) from [Metrix_DEV].[dbo].[shift]) + row_number() over (order by 1/0) as shift_id 
, s2.start_datetime
, s2.end_datetime
, s2.description
, s2.resource_id
, s2.parent_key
, s2.team_id
from [360_v5_Database_DEV].[dbo].[Shift] s1
join 
(
  select
  max(input_reference_internal_id) as max_input,
   s.id, start_datetime, s.end_datetime, s.description, s.resource_id, t.parent_key, tm.team_id
  from [360_v5_Database_DEV].[dbo].[Shift] s
  join [Metrix_DEV].[dbo].[threesixty_resource] t on t.resource_id = s.resource_id
  join [Metrix_DEV].[dbo].[team_member] tm on tm.person_id = t.parent_key
  left outer join [Metrix_DEV].[dbo].[shift] fsms on fsms.start_dttm = s.start_datetime and s.resource_id = fsms.resource_id
   where convert(date,s.start_datetime) >= convert(date,GETDATE()) 
   and fsms.shift_id is NULL
   --and tm.team_id = 'NORTH WEST'
   group by start_datetime, s.id, s.end_datetime, s.description, s.resource_id, t.parent_key, tm.team_id
   ) as s2 on s1.id = s2.id
   where s1.input_reference_internal_id = s2.max_input
   
 
 order by s1.resource_id, s1.start_datetime
 
 SET ANSI_WARNINGS ON;

@Shneor Cheshin 

Yes, that’s basically it.

I have set this a SQL Job to run each morning, and it will take any shifts from PSO that aren’t in FSM and create them.

Ady