Re: Query result as variable compared to another

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mischa Sandberg (mischa_sandberg_at_telus.net)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 02:45:16 GMT

You may care to express your problem with a few more specifics...
like what the primary keys of network_charts and claims are.

Care to explain in non-SQL terms what you're trying to achieve?

"previous Vist_date's record in the first query till I obtain a distinct
value"?
Do you mean, the greatest claims.visit_date (for the same
hin/refer_phys_id?)
that is less than (c1.visit_date) in the first query?

"zeyneddine" <zeyneddine@discussions.microsoft.com> wrote in message
news:735163EC-DAC8-4F29-9DCB-C06903602E81@microsoft.com...
> I have two queries:
>
> SELECT DISTINCT c1.hin, c1.refer_phys_id, c1.refer_phys_last,
c1.refer_phys_first
> FROM network_charts n1 INNER JOIN
> claims c1 ON c1.hin = n1.hin
> WHERE (c1.visit_date =
> (SELECT MAX(c2.visit_date)
> FROM claims c2
> WHERE c2.hin = c1.hin))
> ORDER BY c1.hin, c1.patient_first_name, c1.patient_last_name
>
> and
>
> SELECT DISTINCT c1.hin, c1.refer_phys_id, c1.render_phys_last,
c1.render_phys_first
> FROM network_charts n1 INNER JOIN
> claims c1 ON c1.hin = n1.hin
> WHERE (c1.visit_date =
> (SELECT MAX(c2.visit_date)
> FROM claims c2
> WHERE c2.hin = c1.hin))
> ORDER BY c1.hin, c1.patient_first_name, c1.patient_last_name
>
> I want to compare the values obtained by "refer_phys_first" against
"render_phys_first" and "refer_phys_last" against "render_phys_last", and if
they are the same, I want to obtain the previous Vist_date's record in the
first query till I obtain a distinct value.
>
> How do I go about doing that???