Geek asks and answers

Query extensibility with WHERE EXISTS with a large table

The following query is designed to find the number of people who went to the hospital, the total number of people who went to the hospital, and divide them into two to find the percentage. The table Claimsis two million plus rows and has the correct non-clustered index patientid, admissiondate, and dischargdate. The request is fast enough, but I'm interested in how I can make it more convenient. I would like to be able to add another code to the row, where (hcpcs.hcpcs ='97001')and change the change to percentRehabNotHomeHealthin another column. Is this possible if you do not write a large, bold expression about the join into which I will attach the results of the two queries together? I know that adding an extra column, the math will not look right, but at the moment it does not bother me. desired sample result:http://imgur.com/BCLrd
database schema

enter image description here

select  h.hospitalname
    ,count(*) as visitCounts
    ,hospitalcounts
    ,round(count(*)/cast(hospitalcounts as float) *100,2) as percentRehabNotHomeHealth
    from Patient p
    inner join statecounties as sc on sc.countycode = p.countycode
    and sc.statecode = p.statecode
    inner join hospitals as h on h.npi=p.hospitalnpi
    inner join
    --this join adds the hospitalCounts column
    (
        select h.hospitalname, count(*) as hospitalCounts
            from hospitals as h
            inner join patient as p on p.hospitalnpi=h.npi
            where p.statecode='21' and h.statecode='21'
            group by h.hospitalname
    ) as t on t.hospitalname=h.hospitalname
    --this where exists clause gives the visitCounts column
    where h.stateCode='21' and p.statecode='21'
    and exists
    (
        select distinct p2.patientid
            from Patient as p2
            inner join Claims as c on c.patientid = p2.patientid
            and c.admissiondate = p2.admissiondate
            and c.dischargedate = p2.dischargedate
            inner join hcpcs on hcpcs.hcpcs=c.hcpcs
            inner join hospitals as h on h.npi=p2.hospitalnpi
            where (hcpcs.hcpcs ='97001' or hcpcs.hcpcs='9339' or hcpcs.hcpcs='97002')
            and p2.patientid=p.patientid 
    ) 
    and hospitalcounts > 10
    group by h.hospitalname, t.hospitalcounts
    having count(*)>10
+5
source share
1 answer

You can look in CTE (Common Table Expressions) to get what you need. This will allow you to get generalized data and join the details in a common way. As an example, I changed your connection in the subquery as CTE.

;with hospitalCounts as (
    select h.hospitalname, count(*) as hospitalCounts
    from hospitals as h
    inner join patient as p on p.hospitalnpi=h.npi
    where p.statecode='21' and h.statecode='21'
    group by h.hospitalname
)
select  h.hospitalname
    ,count(*) as visitCounts
    ,hospitalcounts
    ,round(count(*)/cast(hospitalcounts as float) *100,2) as percentRehabNotHomeHealth
from Patient p
inner join statecounties as sc on sc.countycode = p.countycode
and sc.statecode = p.statecode
inner join hospitals as h on h.npi=p.hospitalnpi
inner join hospitalCounts on t.hospitalname=h.hospitalname
--this where exists clause gives the visitCounts column
where h.stateCode='21' and p.statecode='21'
and exists
(
    select p2.patientid
        from Patient as p2
        inner join Claims as c on c.patientid = p2.patientid
        and c.admissiondate = p2.admissiondate
        and c.dischargedate = p2.dischargedate
        inner join hcpcs on hcpcs.hcpcs=c.hcpcs
        inner join hospitals as h on h.npi=p2.hospitalnpi
        where (hcpcs.hcpcs ='97001' or hcpcs.hcpcs='9339' or hcpcs.hcpcs='97002')
        and p2.patientid=p.patientid 
) 
and hospitalcounts > 10
group by h.hospitalname, t.hospitalcounts
having count(*)>10
+4
source

More articles:


All Articles