September 24, 2012 at 9:54 am
I have a query where I am selecting numerous fields. The problem is I only need the data where
(charges.service_item_id IN ('G8907', 'G8908', 'G8909', 'G8910', 'G8911', 'G8912', 'G8913', 'G8914', 'G8915', 'G8916', 'G8917', 'G8918')) having a count >=2
In other words I only want patients that have 2 or more of these gcodes. I can't seem to get this to work.
See example query below
select......
WHERE (charges.source_type = 'v') AND (patient_encounter.practice_id NOT IN ('0069', '0034', '0026', '0076', '0049', '0061', '0055', '0038', '0060', '0063', '0039', '0041',
'0053', '0079', '0052', '0056', '0068', '0029', '0050', '0051', '0062', '0073', '0088', '0089', '0087', '0078', '0082', '0085', '0086', '0012', '0042','0040'))
AND (charges.service_item_id IN ('G8907', 'G8908', 'G8909', 'G8910', 'G8911', 'G8912', 'G8913', 'G8914', 'G8915', 'G8916', 'G8917', 'G8918'))
and (charges.create_timestamp >= '09/10/2012') AND (charges.create_timestamp <= '09/16/2012')
and (substring (payer_mstr.payer_name,12,2) ='--'
and left (payer_mstr.payer_name,9)= 'medicare '
or(substring (payer_mstr.payer_name,12,3) =' --'
and left (payer_mstr.payer_name,9)= 'medicare'
or(substring (payer_mstr.payer_name,18,2) ='--'
and left (payer_mstr.payer_name,9)= 'medicare '
or (substring(payer_mstr.payer_name,12,2)=' '
and left (payer_mstr.payer_name,9)='medicare '))))
and payer_mstr.delete_ind ='N'
and practice.delete_ind ='N'
GROUP BY practice_misc.custom_statusbar_text, patient_encounter.person_id, patient_encounter.enc_id, patient_encounter.enc_timestamp, person.last_name, person.first_name, patient.med_rec_nbr,
practice.practice_name, patient_encounter.enc_nbr, charges.service_item_id, payer_mstr.payer_name, practice.practice_id, charges.create_timestamp
Thanks for your help
LDB
September 24, 2012 at 10:15 am
I see you are pretty new around here. For this type of thing it really requires that you post ddl (create table statements) and sample data (insert statements) along with desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.
From what you posted it looks like you could do a lot to optimize this query too. The ddl and the entire query you are trying will go a long way in helping that too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 10:35 am
If you can do a select in the from statement for the patients that have 2 chgs/proc and use it as a driver to select the rest of the info.
Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply