We help IT Professionals succeed at work.

how to pull the earlest date of procedures

whttc
whttc asked
on
I would like to pull procedures which are negotiated earlest date.  If I wrote this aggregate query below, I will pull more than one earlest procedures because the variable qty has different numbers under these variables a.sponsor, a.protocol, a.investigator_id, a.il_prcode.

I guess i have to write a subquery to solve this problem. please suggest.
Thanks



select a.sponsor, a.protocol, a.investigator_id, a.il_prcode, qty, min(a.modify_date)as modify_date

from Abbott_Neg a
group by a.sponsor, a.protocol, a.investigator_id, a.il_prcode, a.qty
Comment
Watch Question

please share the data and kindly elaborate the problem.

Author

Commented:
Please see the attached excell as an example, I would like to pull the two lines pulled in yellow, which are negotiated in earlest date under this procedure code and two investigators.


SELECT     investigator_id, type, il_prcode, qty, total_patients_per_sub_study, overhead_percentage, negotiation_to_local_rate,
                      sponsorcost, PIcost, modify_date
FROM         Abbott
WHERE     (il_prcode = '36416') AND investigator_id in ('15886','32329')
ORDER BY investigator_id, modify_date
example.xls

Commented:
Select tmp.* from
(
SELECT top 2  investigator_id, type, il_prcode, qty, total_patients_per_sub_study, overhead_percentage, negotiation_to_local_rate,
                      sponsorcost, PIcost, modify_date
FROM         Abbott
WHERE     (il_prcode = '36416') AND investigator_id in ('15886','32329')
Order by modify_Date
) tmp
ORDER BY tmp.investigator_id, tmp.modify_date

Author

Commented:
Thank you,

 However, I add in "where statement" becaseu I give you a small data as an example.

in reality, there are more than 50 codes and many investigators. so I have to delete the where statement to get the result. how am i fix this query?

Thanks
use this.....

select pi_id,code,type,patients, oh, cost, min(qty) as 'Qty',min(date) as 'MinDate' from yourtable
group by pi_id,code
sorry i missed some column names in group by clause

use this.....

select pi_id,code,type,patients, oh, cost, min(qty) as 'Qty',min(date) as 'MinDate' from yourtable
group by pi_id,code,type,patients,oh,cost
Commented:
Select tmp.* from
(
SELECT investigator_id, type, il_prcode, qty, total_patients_per_sub_study, overhead_percentage, negotiation_to_local_rate,
                      sponsorcost, PIcost, modify_date,
row_number() over(partition by investigator_id order by modify_date) as seq
FROM         Abbott
) tmp
where seq <= 2
ORDER BY tmp.investigator_id, tmp.modify_date

Author

Commented:
The request is cancelled
Steve WalesSenior Database Administrator

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.