how to calculate cumulative_on_time

[scottr]

hi friends here is my problem.
my source is hourly feed which has fields like key_off and key_on.
in that feed each vehicle may have multiple records.
the time diff between Key_Off and Key_On is ON_TIME for each vehicle.
here i need to calculate cumulative_on_time for each vehicle .
for example the vehicle A appears first time then cumulative_on_time is 0.
if vehicle A appears second time in the same feed them cumulative time is 0+on_time of current record.

if the vehicle A appears third time then cumulative_on_time= cumulative_on_time of second record+ on_time of current record.

if the same vehicle A appers in the next feed then cumulative_on_time =cumulative_on_time of previous feed+ on_time of current record

pl help me how to do this

[sonia jacob]

I think there are couple of ways of doing it depending on your Target system.

I would use a Transformer to find the TIME_ON for each record and then an aggregator to sum the TIME_ON values (group by vehicle) of the particular feed.

Then I would do a lookup to the target system (i would use a hash file with vehicle as the key and time_on as the attribute) to get the cumilative time_on already existing in the table if any. Add the same to the aggragated value and send the resultant value to target.

Hope it helps.

[scottr]

thanks. but i can't use aggregator stage becuse i have to populate each and every record for every vehicle ( I can't group vehicles i.e if vehicle A has 3 records ,i need to populate all 3 in the target ) with cumulative_on_time and my target is Oracle9.2

[sonia jacob]

hmmm that makes it a little tougher.

So tell me is the following o/p matches ur requirement

Feed 1

Vehicle Name | time_on | cumulative_time_on
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3

Feed 2
Vehicle Name | time_on | cumulative_time_on
A | 1 | 4
A | 1 | 5
A | 1 | 6
A | 1 | 7

Get the max. cumulative from Feed 1, identify these records with a flag = 'P'. Using a link collector put together the max. cumulative from Feed 1 and the data from Feed 2. Sort the data so that all records for a particular vehicle come together with the max. cumulative from Feed 1 as the first record (you could use the timestamp of the records for sorting.)
Send the data to the transformer comparing the vehicle of the current record to that of the previous record. Based on the a successful / unsuccesful comparison the cumulative time_on would be calculated.

if previous record vehicle = current record vehicle then
stagevar = stagevar + current_time_on
send the record to target
else
if previous record vehicle <> current record vehicle and
current record flag = 'P' then
stagevar = cumilative_time_on of cuurent record (max. cumulative from Feed 1)
else
if previous record vehicle <> current record vehicle and
current record flag <> 'P' then
stagevar = 0
send the record to target

Note that i am not sending the any record to the target in the case : if previous record vehicle <> current record vehicle and current record flag = 'P'

hope things are looking better. Are there any other test cases?

[scottr]

Hi sonia,
first let's look at the first feed

as u said my o/p looks like

Vehicle Name | time_on | cumulative_time_on
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3

can u expalin me how to achive this ?

thanks in advance

[sonia jacob]

Vehicle Name | time_on | cumulative_time_on
A | 1 | 0
A | 1 | 1
A | 1 | 2
A | 1 | 3

oracle 9i stage------
----Link collector------Sort-----Transformer----O/P
Sequential stage----


Oracle 9i : retrieves the max. cumulative of existing vehicle from the table. Also adds a flag "P to each of the distinct vehicle. In the first case there are no such records.

Sequential stage : retrieves the follow.
Vehicle Name | time_on
A | 1
A | 1
A | 1
A | 1

Link Collector: puts together all data.
A | 1
A | 1
A | 1
A | 1

Sort Stage: Sorts the data based on vehicle and timestamp. This will put all the records for a vehicle together and the one from the previos feed first. Once again
A | 1
A | 1
A | 1
A | 1
Transformer stage : To check between the current and the previous record.
there are 2 stage variable

CumTimeOf
PreviousVehicle


if PreviousVehicle = current record vehicle then
CumTimeOf = CumTimeOf + current_time_on
send the record to target
else
if PreviousVehicle <> current record vehicle and
current record flag = 'P' then
CumTimeOf = cumilative_time_on of cuurent record (max. cumulative from Feed 1)
PreviousVehicle = current record vehicle
else
if PreviousVehicle <> current record vehicle and
current record flag <> 'P' then
CumTimeOf = 0
PreviousVehicle = current record vehicle
send the record to target

If you parse the first record through the if cond. given above then it satifies the last if cond. hence ur o/p would be A|1|0 and the PreviousVehicle =A

If you parse the second record through the if cond. given above then it satifies the first if cond. hence ur o/p would be A|1|1

then
A|1|2
A|1|3

Does it sound ok now?

[scottr]

look's great but i have small doubts

1)retrieves the max. cumulative of existing vehicle from the table. Also adds a flag P -- how to achive this.

2)reg stage variables CumTimeOf and PreviousVehicle how to set them with current feed values ( am i sounds reasonable).

i am very much thankful for your help.. i will try this on monday and i'll get back to if i have any hurdels.

thanks sonia..

[sonia jacob]

look's great but i have small doubts

1)retrieves the max. cumulative of existing vehicle from the table. Also adds a flag P -- how to achive this.

oracle select stmt. would do the work for u.

2)reg stage variables CumTimeOf and PreviousVehicle how to set them with current feed values ( am i sounds reasonable).

there is no need to worry about the records of the current feed and the previous feed, once it comes to the transformer. The comparison happens between two consecutive records.