whttc
asked on
how to pull the earlest date of procedures
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
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
BuggyCoder
please share the data and kindly elaborate the problem.
ASKER
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_stu
sponsorcost, PIcost, modify_date
FROM Abbott
WHERE (il_prcode = '36416') AND investigator_id in ('15886','32329')
ORDER BY investigator_id, modify_date
example.xls
SELECT investigator_id, type, il_prcode, qty, total_patients_per_sub_stu
sponsorcost, PIcost, modify_date
FROM Abbott
WHERE (il_prcode = '36416') AND investigator_id in ('15886','32329')
ORDER BY investigator_id, modify_date
example.xls
Select tmp.* from
(
SELECT top 2 investigator_id, type, il_prcode, qty, total_patients_per_sub_stu
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
(
SELECT top 2 investigator_id, type, il_prcode, qty, total_patients_per_sub_stu
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
ASKER
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
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
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,o
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,o
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The request is cancelled
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.