On 17 May 2012 23:19, CRPence wrote:
On 17 May 2012 22:30, Pete Helgren wrote:
Right...but if the select was:

select id where emp = '1111'

And emp '1111' had 100 records in it, then the record set would
have 100 records, so the question (rephrased) is:

Given the above record set (100 records of emp '1111') how do I:

1) Locate the ONE record where the date is '5-17-2012'?
2) Move the cursor from that position to the record just prior?


Is a valid assumption that there will be only one row [or zero rows]
matching the specified date; i.e. there can not be multiple rows for
the same date for any one value of EMP? That would make selection
easier; i.e. no requirement to combine the Date and Time columns into
a TimeStamp column.

Is the goal perhaps to find the one "record just prior"; i.e.
perhaps there really is no need to get a set with both rows, thus no
reason to scroll.


My examples below used all numeric types for my convenience, thus the predicates and expressions would have to be adapted to the actual data types, as presented in the OP.


Depending on possible assumptions that can be made, slight variations of the following join query can get the two rows [probably fairly quickly; esp. with safe assumptions], possibly in one row:


select /* a.* , */ b.*
from et a inner join et b
on a.emp=b.emp
and a.date>=b.date and a.time>=b.time
where a.emp=1111
and a.date=20120517
order by b.date desc,b.time desc
fetch first 2 rows only /* first 1 row if a.date>b.date */


With an appropriate ORDER BY, the following [assuming one row per date] can get the "1)" row, and then a FETCH next 1 row gets the "2)" "prior" row:


select * from et
where et.emp=1111 and et.date<=20120517
and et.date
>= ( select max(s.date) from et s
where s.emp=et.emp
and s.date
< (select max(x.date) from et x
where x.emp=s.emp )
)


Similar to the above [but without the assumption that there is only one row per date]:


select * from et
where et.emp=1111 and et.date<=20120517
and timestamp(et.date||digits(et.time)||'00')
>= ( select max(timestamp(s.date||digits(s.time)||'00'))
from et s
where s.emp=et.emp
and timestamp(s.date||digits(s.time)||'00')
< (select max(timestamp(x.date||digits(x.time)||'00'))
from et x
where x.emp=s.emp )
)


The prior queries can be easily adjusted to select just the one "prior" row, by changing the ">=" under the "and" to just "=". What the query effects might be easiest to understand having been broken into individual CTEs; e.g.:


with
/* select all rows for emp=1111 and before\at limit */
etlmt as
(select et.*
, timestamp(et.date||digits(et.time)||'00') as datetime
from et where emp=1111 and date<=20120517)
/* select the max timestamp for the emp=1111 rows */
,etmax as
(select max(s.datetime) as datetime
from etlmt s )
/* select the prior maximum timestamp to the max */
,etprv as
(select max(s.datetime) as datetime
from etlmt s
where s.datetime<(select x.datetime from etmax x) )
select * from etlmt
where datetime=(select datetime from etprv)
/* and etlmt.date<=20120517 -- if using datetime>=() */


Setup for testing the above was:

create table et
( id numeric(4), emp numeric(4)
, time numeric(4), date numeric(8)
)
;
insert into et values
(1000, 1111, 0935, 20120515)
, (1001, 1111, 0905, 20120516)
, (1002, 2222, 0910, 20120517)
, (1003, 3333, 0911, 20120517)
, (1004, 3333, 1015, 20120517)
, (1005, 1111, 1020, 20120517)
;

Regards, Chuck

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].