help with getting most current record sql-92

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with getting most current record sql-92

Author

Topic

tnichols333
Starting Member

14 Posts

Posted - 2012-10-17 : 12:30:21

I have an issue when querying against a progress DB using sql-92. I have gotten it to work but it is extremely slow.

2 tables:
locn
co-no
loc-id
type-cd

rpf
co-no
loc-id
pm-id
effect-time - this is in seconds in a day (max of 84600)
effect-date
ss-bprice

I need a list of all the locations and the most current rpf per pm-id where the locn.type-cd = "con".

so the final list will look like this:

co-no | loc-id | pm-id | effect-date | effect-time | ss-bprice

1 | locA | 3511 | 09/12/12 | 02:59 | 2.59
1 | locA | 1243 | 09/10/12 | 02:59 | 2.47
1 | locB | 3511 | 09/20/12 | 04:23 | 2.45
1 | locC | 1243 | 08/21/12 | 05:15 | 2.59
1 | locC | 3511 | 09/20/12 | 05:15 | 2:45

I would be super appreciative for any help!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-17 : 13:11:11

Canyou explain your results, why do you have two rows for LocA? Is there an additional requirement to get rows by Date, but the greatest Time for that date?

tnichols333
Starting Member

14 Posts

Posted - 2012-10-17 : 13:31:47

quote:
Originally posted by Lamprey

Canyou explain your results, why do you have two rows for LocA? Is there an additional requirement to get rows by Date, but the greatest Time for that date?

Thanks Lamprey.
There can be multiple rpf per locn because of the pm-id. I need the latest rpf for each pm-id per locn. So if there are 30 rpf with 10 different pm-id for one locn.loc-id - I need 10 records returned. I need the record with the effect-time and effect-date less than or equal to the time and date the query is run.

tnichols333
Starting Member

14 Posts

Posted - 2012-10-18 : 08:43:17

Any help would be much appreciated!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-18 : 12:05:25

I didn't notice you are not using SQL Server. This is a SQL Server forum, so you might get more traction on a forum that is specific to the platform you are using.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources