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
midrange.com
