Crystal Formula Question using sql

[jrtrice]

Hello All,

I am using a report to show all meds for a patient for each paticular lab date. So say they come in for lab work on a particular date. Each lab date they have open meds that I need to list for each lab date. I can get it to list each med and the lab dates which display they meds units.

For example

12/04/11 amikacin 4 mg
12/05/11 amikacin 4 mg
12/06/11 amikacin 8 mg
12/07/11 amikacin 8 mg
12/08/11 amikacin 4 mg
12/09/11 amikacin 4 mg
12/10/11 amikacin 12mg

12/04/11 captropril 4 mg
12/05/11 captropril 4 mg
12/06/11 captropril 8 mg
12/07/11 captropril 4 mg
12/08/11 captropril 4 mg
12/09/11 captropril 12 mg
12/10/11 captropril 12 mg



They only want to pull in the last six dates and the associated information. I am trying to set up a formula in crystal to supress all dates after six. The problem here is that it only displays the six amikacin. I want it to display the first six of each med and not just the first six. Any idea of how I can do this?

I need to distinct count the medicine_name and lab date but not sure how to combine distinct counts on two columns so that I can pull in the last six lab dates for each medicine?

I have a parameter set up to allow them to select how many records they want to pull. I just need it to pull that number of records for each med and not just that many records total for all meds.

I hope that makes sense.

Thanks



Any ideas on how to do this?

 

[Dagon]

What ranking queries were invented for:

drop table med;

create table med (issdate date, descr varchar2(50), amt varchar2(50));

insert into med values(to_date('12/04/11', 'DD/MM/YY'),'amikacin',  '4 mg');
insert into med values(to_date('12/05/11', 'DD/MM/YY'),'amikacin',  '4 mg');
insert into med values(to_date('12/06/11', 'DD/MM/YY'),'amikacin',  '8 mg');
insert into med values(to_date('12/07/11', 'DD/MM/YY'),'amikacin',  '8 mg');
insert into med values(to_date('12/08/11', 'DD/MM/YY'),'amikacin',  '4 mg');
insert into med values(to_date('12/09/11', 'DD/MM/YY'),'amikacin',  '4 mg');
insert into med values(to_date('12/10/11', 'DD/MM/YY'),'amikacin',  '12mg');

insert into med values(to_date('12/04/11', 'DD/MM/YY'),'captropril',  '4 mg');
insert into med values(to_date('12/05/11', 'DD/MM/YY'),'captropril',  '4 mg');
insert into med values(to_date('12/06/11', 'DD/MM/YY'),'captropril',  '8 mg');
insert into med values(to_date('12/07/11', 'DD/MM/YY'),'captropril',  '4 mg');
insert into med values(to_date('12/08/11', 'DD/MM/YY'),'captropril',  '4 mg');
insert into med values(to_date('12/09/11', 'DD/MM/YY'),'captropril',  '12 mg');
insert into med values(to_date('12/10/11', 'DD/MM/YY'),'captropril',  '12 mg');

select descr, issdate, amt
from
(select descr, amt, issdate, rank() over (partition by descr order by issdate desc) rnk
from med)
where rnk <= 6
order by descr, issdate