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_nu mber_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

as always, any help or guidance is appreciated.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

> 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_nu mber_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...4/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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq