Implement FIFO logic in business rules

See more:

SQL-Server-2008R2

Hi All,

We are looking for a solution based on FIFO algorithm. We have a table having following data:

create table temp_table
(
row_id  int,
[object]  varchar(20),
obj_use  varchar(20),
start varchar(20),
[end] varchar(20),
startd  datetime,
endd datetime,
starttime  varchar(20),
endtime  varchar(20),
id int,
[objid]  int,
Mgt int,
nextstarttime  varchar(20),
EqpRef  varchar(20) 
)
GO
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(1,'19J','0786','ERS','AAR','2015/08/01','2015/08/01','0810','2315','1866',7,0,'2315',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(1,'19O','7845','ERS','AAL','2015/08/01','2015/08/01','0100','0200','1876',6,70,'0310',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(2,'19O','6584','AAL','AAR','2015/08/01','2015/08/01','0355','0400','1810',6,70,'0510',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(3,'19O','0575','AAL','ERS','2015/08/01','2015/08/01','2000','0900','1853',6,70,'1010',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(4,'19O','0575','AAR','ERS','2015/08/01','2015/08/01','0800','0900','1853',6,70,'1010',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(5,'19O','0575','ERS','ABC','2015/08/01','2015/08/01','1100','1200','1853',6,70,'1310',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(6,'19O','0575','def','ABC','2015/08/01','2015/08/01','1200','1310','1853',6,70,'1410',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(2,'320','0555','ERS','AAL','2015/08/01','2015/08/01','1310','2210','1838',4,70,'2320',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(1,'320','0457','AAR','ERS','2015/08/01','2015/08/01','0500','2345','1842',4,70,'0055',null)
insert into temp_table(row_id,[object],obj_use,start,[end],startd,endd,starttime,endtime,id,[objid],Mgt,nextstarttime,EqpRef)values(1,'764','0123','IXM','ERS','2015/08/01','2015/08/01','0200','2200','1858',5,70,'2310',null)

row_id object obj_use start end startd endd starttime endtime id objid Mgt nextstarttime EqpRef
1 19J 786 ERS AAR 2015/08/01 2015/08/01 0810 2315 1866 7 0 2315 null
1 19O 7845 ERS AAL 2015/08/01 2015/08/01 0100 0200 1876 6 70 0310 null
2 19O 6584 AAL AAR 2015/08/01 2015/08/01 0355 0400 1810 6 70 0510 null
3 19O 575 AAL ERS 2015/08/01 2015/08/01 2000 0900 1853 6 70 1010 null
4 19O 575 AAR ERS 2015/08/01 2015/08/01 0800 0900 1853 6 70 1010 null
5 19O 575 ERS ABC 2015/08/01 2015/08/01 1100 1200 1853 6 70 1310 null
6 19O 575 ERS ABC 2015/08/01 2015/08/01 1200 1310 1853 6 70 1410 null
2 320 555 ERS AAL 2015/08/01 2015/08/01 1310 2210 1838 4 70 2320 null
1 320 457 AAR ERS 2015/08/01 2015/08/01 0500 2345 1842 4 70 55 null
1 764 123 IXM ERS 2015/08/01 2015/08/01 0200 2200 1858 5 70 2310 null


We need to perform FIFO on this table, and assign 'object' as data items to other rows based on following conditions:

1. first we have to group the rows based on 'object' column

2. then we have to traverse each group from the row having minimum start time of the object group e.g: row id 1 for object group for '19O'

2.1 Assign a 'EqpREf' as 'object' + <an integer="" value=""> , where integer value changes when the start and end chain finishes. Start - end chain is explained in step 2.2

2.2 then we have to pick the 'nextstarttime' of the picked row and compare it against the closest 'starttime' of the rows having 'start' as same as 'end' of the same row we are picking for 'nextstarttime' e.g: row id 2 of object 19O is having 'nextstarttime' 0310 closest to 'starttime' 0355 of row id 2 of object 19O , and rowid 2 is having 'start' AAL which is similar to 'end' of rowid 1

2.3 We have to perform this chain till we find end of same and allocate each row in a chain same 'Eqpref'

hence the output we need to generate will come as:

row_id object obj_use start end startd endd starttime endtime id objid Mgt nextstarttime EqpRef
1 19J 786 ERS AAR 2015/08/01 2015/08/01 0810 2315 1866 7 0 2315 19-J
1 19O 7845 ERS AAL 2015/08/01 2015/08/01 0100 0200 1876 6 70 0310 19O-1
2 19O 6584 AAL AAR 2015/08/01 2015/08/01 0355 0400 1810 6 70 0510 19O-1
3 19O 575 AAL ERS 2015/08/01 2015/08/01 2000 0900 1853 6 70 1010 19O-2
4 19O 575 AAR ERS 2015/08/01 2015/08/01 0800 0900 1853 6 70 1010 19O-1
5 19O 575 ERS ABC 2015/08/01 2015/08/01 1100 1200 1853 6 70 1310 19O-1
6 19O 575 ERS ABC 2015/08/01 2015/08/01 1200 1310 1853 6 70 1410 19O-2
2 320 555 ERS AAL 2015/08/01 2015/08/01 1310 2210 1838 4 70 2320 320-1
1 320 457 AAR ERS 2015/08/01 2015/08/01 0500 2345 1842 4 70 55 320-2
1 764 123 IXM ERS 2015/08/01 2015/08/01 0200 2200 1858 5 70 2310 764-1


Kindly help on same.

Thanks in advance