SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table V_Machine_State_Window (
  Local_Datetime datetime,
  Machine varchar(100),
  State int,
  Duration bigint,
  State_Text varchar(100),
  State_Date date,
  State_Time time,
  Prev_State int,
  Prev_State_Text varchar(100),
  Prev_State_Datestamp datetime,
  Next_State int,
  Next_State_Text varchar(100),
  Next_State_Datestamp datetime)
insert into V_Machine_State_Window values (
  cast('2023-06-01 00:00' as datetime),
  'PC1',
  0,
  300,
  'Idle',
  cast('2023-06-01 00:00' as date),
  cast('2023-06-01 00:00' as time),
  null,
  null,
  null,
  1,
  'Active',
  cast('2023-06-01 00:05' as datetime))
  
1 rows affected

insert into V_Machine_State_Window values (
  cast('2023-06-01 00:05' as datetime),
  'PC1',
  1,
  600,
  'Active',
  cast('2023-06-01 00:05' as date),
  cast('2023-06-01 00:05' as time),
  0,
  'Idle',
  cast('2023-06-01 00:00' as datetime),
  0,
  'Idle',
  cast('2023-06-01 00:15' as datetime))
1 rows affected

insert into V_Machine_State_Window values (
  cast('2023-06-01 00:15' as datetime),
  'PC1',
  0,
  373500,
  'Idle',
  cast('2023-06-01 00:15' as date),
  cast('2023-06-01 00:15' as time),
  1,
  'Active',
  cast('2023-06-01 00:05' as datetime),
  1,
  'Active',
  cast('2023-06-05 08:00' as datetime))
1 rows affected

insert into V_Machine_State_Window values (
  cast('2023-06-05 08:00' as datetime),
  'PC1',
  1,
  600,
  'Active',
  cast('2023-06-05 08:00' as date),
  cast('2023-06-05 08:00' as time),
  0,
  'Idle',
  cast('2023-06-01 00:15' as datetime),
  0,
  'Idle',
  cast('2023-06-05 08:10' as datetime))
1 rows affected

select * from V_Machine_State_Window


Local_Datetime
Machine
State
Duration
State_Text
State_Date
State_Time
Prev_State
Prev_State_Text
Prev_State_Datestamp
Next_State
Next_State_Text
Next_State_Datestamp


2023-06-01 00:00:00.000
PC1
0
300
Idle
2023-06-01
00:00:00.0000000
null
null
null
1
Active
2023-06-01 00:05:00.000

2023-06-01 00:05:00.000
PC1
1
600
Active
2023-06-01
00:05:00.0000000
0
Idle
2023-06-01 00:00:00.000
0
Idle
2023-06-01 00:15:00.000

2023-06-01 00:15:00.000
PC1
0
373500
Idle
2023-06-01
00:15:00.0000000
1
Active
2023-06-01 00:05:00.000
1
Active
2023-06-05 08:00:00.000

2023-06-05 08:00:00.000
PC1
1
600
Active
2023-06-05
08:00:00.0000000
0
Idle
2023-06-01 00:15:00.000
0
Idle
2023-06-05 08:10:00.000


create table V_Machine_State_Desired (
  Local_Datetime datetime,
  Machine varchar(100),
  State int,
  Duration bigint,
  State_Text varchar(100),
  State_Date date,
  State_Time time,
  Prev_State int,
  Prev_State_Text varchar(100),
  Prev_State_Datestamp datetime,
  Next_State int,
  Next_State_Text varchar(100),
  Next_State_Datestamp datetime)
insert into V_Machine_State_Desired values (
  cast('2023-06-01 00:00' as datetime),
  'PC1',
  0,
  300,
  'Idle',
  cast('2023-06-01 00:00' as date),
  cast('2023-06-01 00:00' as time),
  null,
  null,
  null,
  1,
  'Active',
  cast('2023-06-01 00:05' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-01 00:05' as datetime),
  'PC1',
  1,
  600,
  'Active',
  cast('2023-06-01 00:05' as date),
  cast('2023-06-01 00:05' as time),
  0,
  'Idle',
  cast('2023-06-01 00:00' as datetime),
  0,
  'Idle',
  cast('2023-06-01 00:15' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-01 00:15' as datetime),
  'PC1',
  0,
  85500,
  'Idle',
  cast('2023-06-01 00:15' as date),
  cast('2023-06-01 00:15' as time),
  1,
  'Active',
  cast('2023-06-01 00:05' as datetime),
  0,
  'Idle',
  cast('2023-06-02 00:00' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-02 00:00' as datetime),
  'PC1',
  0,
  86400,
  'Idle',
  cast('2023-06-02 00:00' as date),
  cast('2023-06-02 00:00' as time),
  0,
  'Idle',
  cast('2023-06-01 00:15' as datetime),
  0,
  'Idle',
  cast('2023-06-03 00:00' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-03 00:00' as datetime),
  'PC1',
  0,
  86400,
  'Idle',
  cast('2023-06-03 00:00' as date),
  cast('2023-06-03 00:00' as time),
  0,
  'Idle',
  cast('2023-06-02 00:00' as datetime),
  0,
  'Idle',
  cast('2023-06-04 00:00' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-04 00:00' as datetime),
  'PC1',
  0,
  86400,
  'Idle',
  cast('2023-06-04 00:00' as date),
  cast('2023-06-04 00:00' as time),
  0,
  'Idle',
  cast('2023-06-03 00:00' as datetime),
  0,
  'Idle',
  cast('2023-06-05 00:00' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-05 00:00' as datetime),
  'PC1',
  0,
  28800,
  'Idle',
  cast('2023-06-05 00:00' as date),
  cast('2023-06-05 00:00' as time),
  0,
  'Idle',
  cast('2023-06-04 00:00' as datetime),
  1,
  'Active',
  cast('2023-06-05 08:00' as datetime))
1 rows affected

insert into V_Machine_State_Desired values (
  cast('2023-06-05 08:00' as datetime),
  'PC1',
  1,
  600,
  'Active',
  cast('2023-06-05 08:00' as date),
  cast('2023-06-05 08:00' as time),
  0,
  'Idle',
  cast('2023-06-01 00:15' as datetime),
  0,
  'Idle',
  cast('2023-06-05 08:10' as datetime))
1 rows affected

select * from V_Machine_State_Desired


Local_Datetime
Machine
State
Duration
State_Text
State_Date
State_Time
Prev_State
Prev_State_Text
Prev_State_Datestamp
Next_State
Next_State_Text
Next_State_Datestamp


2023-06-01 00:00:00.000
PC1
0
300
Idle
2023-06-01
00:00:00.0000000
null
null
null
1
Active
2023-06-01 00:05:00.000

2023-06-01 00:05:00.000
PC1
1
600
Active
2023-06-01
00:05:00.0000000
0
Idle
2023-06-01 00:00:00.000
0
Idle
2023-06-01 00:15:00.000

2023-06-01 00:15:00.000
PC1
0
85500
Idle
2023-06-01
00:15:00.0000000
1
Active
2023-06-01 00:05:00.000
0
Idle
2023-06-02 00:00:00.000

2023-06-02 00:00:00.000
PC1
0
86400
Idle
2023-06-02
00:00:00.0000000
0
Idle
2023-06-01 00:15:00.000
0
Idle
2023-06-03 00:00:00.000

2023-06-03 00:00:00.000
PC1
0
86400
Idle
2023-06-03
00:00:00.0000000
0
Idle
2023-06-02 00:00:00.000
0
Idle
2023-06-04 00:00:00.000

2023-06-04 00:00:00.000
PC1
0
86400
Idle
2023-06-04
00:00:00.0000000
0
Idle
2023-06-03 00:00:00.000
0
Idle
2023-06-05 00:00:00.000

2023-06-05 00:00:00.000
PC1
0
28800
Idle
2023-06-05
00:00:00.0000000
0
Idle
2023-06-04 00:00:00.000
1
Active
2023-06-05 08:00:00.000

2023-06-05 08:00:00.000
PC1
1
600
Active
2023-06-05
08:00:00.0000000
0
Idle
2023-06-01 00:15:00.000
0
Idle
2023-06-05 08:10:00.000

Local_Datetime	Machine	State	Duration	State_Text	State_Date	State_Time	Prev_State	Prev_State_Text	Prev_State_Datestamp	Next_State	Next_State_Text	Next_State_Datestamp
2023-06-01 00:00:00.000	PC1	0	300	Idle	2023-06-01	00:00:00.0000000	null	null	null	1	Active	2023-06-01 00:05:00.000
2023-06-01 00:05:00.000	PC1	1	600	Active	2023-06-01	00:05:00.0000000	0	Idle	2023-06-01 00:00:00.000	0	Idle	2023-06-01 00:15:00.000
2023-06-01 00:15:00.000	PC1	0	373500	Idle	2023-06-01	00:15:00.0000000	1	Active	2023-06-01 00:05:00.000	1	Active	2023-06-05 08:00:00.000
2023-06-05 08:00:00.000	PC1	1	600	Active	2023-06-05	08:00:00.0000000	0	Idle	2023-06-01 00:15:00.000	0	Idle	2023-06-05 08:10:00.000