> Second Iteration continued...
>
> prior text deleted due to length...
>
> i decided to simplify the problem and only look at
> the
> select that is supposed count the first pass pass.
>
> the data is as follows...
>
> t_inspect
> inspect_id, sn_id
> 178, 200
> 179, 200
>
> t_inspect_result
> id, inspect_id, inspect_result_pass
> 27, 178, *false*
> 28, 179, *false*
> 31, 179, true
>
> ** designates first pass value, ie, not 2nd, 3rd,
> 4th,
> 5th, etc... passes.
>
> so, the following query should yield 0, since zero
> first pass passes are logged.
>
> it returns one row with a value of 1 (as in number
> one). if t_inspect_result_id 27 is changed to
> "true",
> it returns two rows, both with a value of 1.
>
> (SELECT DISTINCT ON (t_inspect_result.inspect_id)
> count(inspect_result_pass) as passed
> FROM t_inspect_result, t_inspect,
> t_inspect_area,
> t_serial_number,
> t_link_contract_number_job_number, t_job_number,
> t_product
> WHERE t_inspect.inspect_id =
> t_inspect_result.inspect_id
> AND inspect_result_pass = 't'
> AND t_inspect.serial_number_id =
> t_serial_number.serial_number_id
> AND
> t_serial_number.link_contract_number_job_number_id =
>
>
t_link_contract_number_job_number.link_contract_number_job_number_id
> AND
> t_link_contract_number_job_number.job_number_id =
> t_job_number.job_number_id
> AND t_product.product_id =
> t_job_number.product_id
> AND t_inspect.inspect_area_id =
> t_inspect_area.inspect_area_id
> AND t_inspect.serial_number_id = '200'
> GROUP BY t_inspect_result.inspect_id,
> t_inspect_result.inspect_result_timestamp
okay, this is working, albeit, it is a simplification
of the issue.
to get # initial passes:
SELECT COUNT (pass)
FROM (SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass
FROM t_inspect_result, t_inspect,
t_serial_number
WHERE t_inspect_result.inspect_id =
t_inspect.inspect_id
AND t_inspect.serial_number_id =
t_serial_number.serial_number_id
AND t_inspect.serial_number_id = 200
AND t_inspect_result.inspect_result_pass = 't'
ORDER BY t_inspect.inspect_id,
inspect_result_timestamp ASC)
AS pass
to get # total:
SELECT COUNT (total)
FROM (SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass
FROM t_inspect_result, t_inspect,
t_serial_number
WHERE t_inspect_result.inspect_id =
t_inspect.inspect_id
AND t_inspect.serial_number_id =
t_serial_number.serial_number_id
AND t_inspect.serial_number_id = 200
ORDER BY t_inspect.inspect_id,
inspect_result_timestamp ASC)
AS total
both seem to be working as expected. i found an old
thread that discussed this issue...
http://archives.postgresql.org/pgsql-sql/2004-04/msg00219.php
and i simplified the problem (complexity to be added
after structure is determined) - which i should have
done to begin with.
i think i can get the rest of the way tomorrow. i'll
post the working query when i get it.
thanks for the help.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com