Using the IN function and need a count

  • 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

  • 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/

  • 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