| SQL HELP [message #511290] |
Fri, 10 June 2011 14:25  |
 |
russromei
Messages: 6
Registered: June 2011
Location: Charlotte
|
Junior Member |
|
|
Seems simple enough, yet I am unable to achieve it
Below is current working scripts with hard coded values
Anywhere you see "offender_book_id = '0000450364'"
Needs to be replaced with a list of offender_book_id's
from anywhere except ost1 and ost2
The script currently selects a min and a max record to compare date fields but I can only get it to work with the hard codes
THANKS FOR ANY HELP IN ADVANCE
Select lu.description
,lu.agy_loc_id
,bk.booking_no
,o.last_name || ', ' || o.first_name name
,o.offender_id_display
,ost1.offender_book_id ofbid_MIN
,ost1.living_unit_id MIN
,ost1.assignment_time AT_MIN
,ost2.offender_book_id ofbid_MAX
,substr(ost2.living_unit_id,1,2) LUMAX
,ost2.assignment_time AT_MAX
FROM
-- bed_assignment_histories bh,
living_units lu,
offender_bookings bk,
offenders o
,(select * from bed_assignment_histories c where assignment_time = (select min(assignment_time) from bed_assignment_histories d where offender_book_id = '0000450364' and substr(living_unit_id,1,2) = '85')) ost1
,(select * from bed_assignment_histories e where assignment_time = (select max(assignment_time) from bed_assignment_histories f where offender_book_id = '0000450364' )) ost2
WHERE bk.offender_id = o.offender_id
AND bk.active_flag = 'Y'
AND bk.offender_book_id = ost1.offender_book_id
----
AND ost1.offender_book_id = '0000450364'
AND substr(ost1.living_unit_id,1,2) = substr(ost2.living_unit_id,1,2)
AND ost1.living_unit_id = lu.living_unit_id
AND ost1.assignment_date < (sysdate - 34)
AND SUBSTR (LU.description, 6, 2) || SUBSTR (LU.description, 9, 2) = :ppod
AND LU.agy_loc_id = :pfac
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
