SQL Server : if one value in a column occurs at least once or another value more than once

I'm glad I have a question with at least a working example this time. This used to be an efficient query when I was just had criteria where an result was returned if it had a count >= 1. Then I had to an additional count for different code values and if they occur 2 or more times. The query went from running in a few seconds to about 43 seconds.

I think I have the logic right but I was wondering if someone had a more efficient way to do this.

select person.person_id
from person
where 
person.person_id in (
        select procedure.person_id
        from procedure
        where
        procedure.performed_by = '555555'
        and procedure.code in (
                '99201', '99202'
                )
        and year(procedure.service_date) = year(getdate())
        group by procedure.person_id
        having count(1) >= '1'
        ) -- having count >= 1 occurrences
or person.person_id in (
        select person_id
        from procedure
        where
        procedure.performed_by = '55555'
        and code in (
                '99304','99305'
                )
        and year(procedure.service_date) = year(getdate())
        group by procedure.person_id
        having count(1) >= '2'
        ) -- having count >= 2 occurrences

Answers


Does this speed it up?

WITH CTE AS
(
    select procedure.person_id
    from procedure
    where
    procedure.performed_by = '555555'
    and procedure.code in ( '99201', '99202' )
    and year(procedure.service_date) = year(getdate())
    group by procedure.person_id
    having count(1) >= '1'

    UNION

    select person_id
    from procedure
    where
    procedure.performed_by = '55555'
    and code in ('99304','99305')
    and year(procedure.service_date) = year(getdate())
    group by procedure.person_id
    having count(1) >= '2'
)

select person.person_id
from person 
JOIN CTE ON CTE.Person_id = Person.Person_Id

Your first IN is just checking for existance, so there is no real need of using HAVING (on another note, why are you comparing COUNT(1) with a string?, the result is an INT, so you should use >=1 or >=2 instead). You are also using a function on service_date before comparing it, you shouldn't do that since unables the use of a possible index on that column. I would write your query this way:

select p.person_id
from person p
where exists (  select 1
                from procedure
                where
                procedure.performed_by = '555555'
                and procedure.code in ('99201', '99202')
                and procedure.service_date >= dateadd(year,datediff(year,0,getdate()),0)
                and procedure.service_date < dateadd(year,datediff(year,0,getdate())+1,0)
                and procedure.person_id = p.person_id)
or person.person_id in (
        select person_id
        from procedure
        where
        procedure.performed_by = '55555'
        and code in ('99304','99305')
        and procedure.service_date >= dateadd(year,datediff(year,0,getdate()),0)
        and procedure.service_date < dateadd(year,datediff(year,0,getdate())+1,0)
        group by procedure.person_id
        having count(1) >= 2
        ) -- having count >= 2 occurrences

Need Your Help

Dynamic data in postgresql

python postgresql performance dynamic-data vacuum

I intend to have a python script do many UPDATEs per second on 2,433,000 rows. I am currently trying to keep the dynamic column in python as a value in a python dict. Yet to keep my python dict

Confirmation Dialog in Android (andengine)

java android multithreading andengine

I am working on an android app and am trying to figure out how to get a popup confirmation window to display with confirm and cancel buttons when a button is pressed.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.