Click here to Skip to main content
15,561,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

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

SQL
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
Posted
Comments
Shweta N Mishra 19-Jan-15 7:21am    
what have you tried ?
KumudVaish 20-Jan-15 0:37am    
We have tried LAG and CTE to develop the logic. But still it is failing to achieve the business logic. It is giving the 'object + 1' for first row in FIFO but still not achieving the FIFO chain.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900