Top 100 medication prescribed in past 6 month

Transact-SQL
1

Hi All..

I need to get a top 100 meds prescribe by a doctor for past 6 month for practice id/location ='0022' : here is my query: I am not sure if this is correct? for practice 0022 there are 367 providers.
Thanks!

select count(medication_name), pm.last_name,pm.first_name,c.meds_id,practice_id,quanity,c.create_timestamp,medication_name,
START_DATE,DATE_STOPPED,DATEPART(MONTH, C.create_timestamp) AS MONTH
from provider pm
join medications C on C.provider_id=pm.provider_id
where C.create_timestamp >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6,current_timestamp)), 0) --- from the first day
and practice_id='0022'
group by medication_name,pm.last_name,pm.first_name,c.meds_id,quanity,c.create_timestamp,medication_name,practice_id, > START_DATE,DATE_STOPPED
order by count(C.Meds_id) desc

2

That's not going to work. If you provide DDL, sample data and expected outcome, we can help

1 Like
3

sorry don't have any sample data and its all patients information . I'll figure out something/Thanks!
Pasi.

4

Could you at a minimum provide the schema of the tables

declare @provider table(provider_id int, practice_id int, 
last_name varchar(50), first_name varchar(50))
declare @medications table(provider_id int, meds_id int, 
medication_name varchar(50), 
create_timestamp timestamp, 
quanity int,START_DATE date, DATE_STOPPED date)

insert into @provider
select distinct top 10000 column_id,  user_type_id, name, 
reverse(name) 
 from sys.all_columns

insert into @medications(provider_id,meds_id,medication_name, 
quanity, START_DATE, DATE_STOPPED)
select top 10000 p.provider_id, column_id * 3, ac.name, column_id, 
getdate(), DATEADD(dd,column_id, getdate()) 
  from sys.all_columns ac 
  join @provider p on ac.column_id = p.provider_id

select * from @provider

select * from @medications
1 Like
5

Thanks yosiasz.. I had to do a group by Medication name and I think I got what I want, but thanks again for looking into this!
Pasi