Aggregation of prices from Record list

, ,
1

Hi,

Anybody please help me to how to solve my problem.
I am querrying my database using webmethod flow
service.I am getting recordlist(results in pub.db.execSql).
That recordlist contains records one date,24 hourly prices(24 records).
Suppose date is 12/10/2002.Price values starts
at 01:00:00 to 24:00:00.I mean each hour i am getting
one price value.Here i am getting price values 01:00:00
to 07:00:00Am are same values.From 08:00:00 to
23:00:00hrs i am getting same price values.At 24:00:00
hr i am getting one price.So i am getting 3 price values for
24 records(THR1,THR2…THR24).
Time ranges(StartTime 01:00:00AmEndTime07:59AM ,(StartTime 08:00:00Amto EndTime22:59Pm) , StartTime
23:00PM to End Time 23:59PM).

                  Now i have to extract Those 3 price values,

Time Ranges(01:00:00AM to 07:00:59),(08:00:00AMto
22:59PM),23:00:00PM to 23:59:00PM).

for example

Format i have to extract is :
StartTime EndTime Price
01:00:00AM 07:59:00 20
08:00:00AM 22:59:00 PM 30
23:00:00PM 23:59:00 20

Pl any body help me how to do it.I think we have write
javaService.Is any procedure is there in webMethods
to extract Time ranges,Prices after comparing.

Thanks,
Srini

2

Hi Srini,

You have to use
1)LOOP of recordlist
2)Branch on specfic Condn
3)Built in service addFloats for aggregation of price.

Hope this helps you to start…
if you are not able to …send me the code so that i can fix your bug.

Regards,
Prabhu

3

Srini:
If you were to think outside of webMethods, you can modify the SQLQuery itself to return the data accordingly.

Say the table Name is prices and its structure is as follows
pdate datetime (stores only the date)
ptime datetime (stores only the time)
pprice decimal (price field)

The following query will help get you the right data you need.

SELECT pdate, MIN(ptime) AS startTime, MAX(ptime) AS endTime, pprice
FROM prices
GROUP BY pdate, pprice
ORDER BY 1,2,3

thanks
KrishnaN