OraFAQ Forum: Reports & Discoverer » Dynamically displaying current value in following rows

Home » Developer & Programmer » Reports & Discoverer » Dynamically displaying current value in following rows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamically displaying current value in following rows [message #500877]

Thu, 24 March 2011 04:35

ankurjain
Messages: 11
Registered: July 2007
Location: Vadodara, Gujarat, India

Junior Member

Hello Guys,

I have a problem where I have to display the value of current cell in next following cells.

The table structure is as follows :
ttdate - date
individualplanid - varchar(10); - train number
sch_deptime - number(8); - scheduled departure time in milli seconds
sch_arrtime - number(8); - scheduled arrival time in milli seconds
stn - varchar(10); - station short name
dep_delay - number(8); - dep delay in milli seconds
arr_delay - number(8); - arr delay in milli seconds

The delay is filled continuously by application software continuously.

I want to make a query where I want a calculated field which does the prediction of train arrival on coming stations. This shall be done as the delay + sch_deptime for all the next stations.

Following is a dataset :

individualplanid  sch_deptime  sch_arrtime  stn  arr_delay dep_delay

BO646	NULL	57900000	BVI	NULL	80000
BO646	NULL	58140000	KILE	NULL	40000
BO646	NULL	58320000	MDD	NULL	20000
BO646	NULL	58530000	GMN	NULL	NULL
BO646	NULL	59160000	ADH	NULL	NULL
BO646	NULL	59550000	STC	NULL	NULL
BO646	NULL	59940000	BA	NULL	NULL
BO646	NULL	60540000	DDR	NULL	NULL
BO646	NULL	61200000	BCL	NULL	NULL
BO646	61800000	NULL	CCG	12000	NULL

What I want to do is

individualplanid sch_deptime  sch_arrtime  stn  arr_delay dep_delay EAT
BO646	NULL	57900000	BVI	NULL	80000	57980000
BO646	NULL	58140000	KILE	NULL	40000	58180000
BO646	NULL	58320000	MDD	NULL	20000	58340000
BO646	NULL	58530000	GMN	NULL	NULL	58550000
BO646	NULL	59160000	ADH	NULL	NULL	59180000
BO646	NULL	59550000	STC	NULL	NULL	59570000
BO646	NULL	59940000	BA	NULL	NULL	59960000
BO646	NULL	60540000	DDR	NULL	NULL	60560000
BO646	NULL	61200000	BCL	NULL	NULL	61220000
BO646	61800000	NULL	CCG	12000	NULL	61812000

The last column (EAT) is calculated one. It shall be like as the dep_delay is updated the EAT for the following records shall be sch_deptime + dep_delay. I tried my hand at windows and partitioning but not of much help.

I did was something like this. The calculated field is cum_depdelay which is only the delay ( not sch_deptime + dep_delay ).

select ttdate,individualplanid td,station,sch_deptime,sch_arrtime,act_depdelay,
sum(act_depdelay) over ( partition by individualplanid order by sch_deptime rows between current row and unbounded following ) c_depdelay
from logtime where ttdate='14-Mar-2011' and individualplanid='BO646'
order by sch_deptime,sch_arrtime;


TD	STATION	SCH_DEPTIME	SCH_ARRTIME	ACT_DEPDELAY	C_DEPDELAY
BO646	BVI	57900000	NULL	80000	140000
BO646	KILE	58140000	NULL	40000	60000
BO646	MDD	58320000	NULL	20000	20000
BO646	GMN	58530000	NULL	NULL	NULL
BO646	ADH	59160000	NULL	NULL	NULL
BO646	STC	59550000	NULL	NULL	NULL
BO646	BA	59940000	NULL	NULL	NULL
BO646	DDR	60540000	NULL	NULL	NULL
BO646	BCL	61200000	NULL	NULL	NULL
BO646	CCG	NULL	61800000	NULL	NULL

Please guide me on how shall i proceed ?

-Ankur Jain

Report message to a moderator

Re: Dynamically displaying current value in following rows [message #500884 is a reply to message #500877]

Thu, 24 March 2011 06:20

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Indentation is spoiled so I didn't quite get what value belongs to what column header, so I created my own test case:

SQL> select * from test;

PLANID  SCH_DEPTIME STN    ARR_DELAY        EAT
------- ----------- ----- ---------- ----------
BO646      57900000 BVI        80000
BO646      58140000 KILE       40000
BO646      58320000 MDD        20000
BO646      58530000 GMN
BO646      59160000 ADH
BO646      59550000 STC
BO646      59940000 BA
BO646      60540000 DDR
BO646      61200000 BCL

EAT is calculated upon existing SCH_DEPTIME value and its ARR_DELAY value (if it exists. If not, then take the last known one):

SQL> update test t set
  2    t.eat = (select t1.sch_deptime +
  3               case when t1.arr_delay is not null then t1.arr_delay
  4                    else (select t2.arr_delay
  5                          from test t2
  6                          where t2.planid = t.planid
  7                            and t2.sch_deptime = (select max(t3.sch_deptime)
  8                                                  from test t3
  9                                                  where t3.planid = t.planid
 10                                                    and t3.arr_delay is not null
 11                                                 )
 12                          )
 13               end
 14             from test t1
 15             where t1.planid = t.planid
 16               and t1.sch_deptime = t.sch_deptime
 17            );

9 rows updated.

The result:

SQL> select * from test;

PLANID  SCH_DEPTIME STN    ARR_DELAY        EAT
------- ----------- ----- ---------- ----------
BO646      57900000 BVI        80000   57980000
BO646      58140000 KILE       40000   58180000
BO646      58320000 MDD        20000   58340000
BO646      58530000 GMN                58550000
BO646      59160000 ADH                59180000
BO646      59550000 STC                59570000
BO646      59940000 BA                 59960000
BO646      60540000 DDR                60560000
BO646      61200000 BCL                61220000

Is this what you are looking for?

Report message to a moderator

Re: Dynamically displaying current value in following rows [message #501015 is a reply to message #500884]

Fri, 25 March 2011 02:05

ankurjain
Messages: 11
Registered: July 2007
Location: Vadodara, Gujarat, India

Junior Member

Hello littlefoot,
Sorry my first post was a little messy. I will explain again in a little detail. This database keeps the information of a train's id,scheduled departure time ( for intermediate stations ) ,scheduled arrival time ( for last station ), stopping stations and delay. The delay field is updated as the train makes its journey by application software. Now I want to make a report where I will use this info and display a new value named EAT where I want to display the sch_deptime + arr_delay for next all stations. Its like estimated time of arrival depending on the delay for next all stations. This field is not in database it needs to be calculated. The query you gave is helpful. I will try to do some changes and try displaying it in report.

Thanks for prompt reply Smile

-Ankur

[Updated on: Fri, 25 March 2011 05:24]

Report message to a moderator

Previous Topic:	Character Report printing problem
Next Topic:	Report Format 'Delimited (Tab)' showing duplicates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 02:30:16 CST 2025