I see similar questions but not one that answers exactly this case, so I will go ahead and ask.
I need to select a group of facilities, but once they've matched a certain set of criteria, they may not be included. So if the submission month matches a particular month and either FacWideAU, FacWideAR, or AU = 1 (not 0 or null), then that facility cannot be included. The facilities may have multiple results.
Sample DB Example (only Hosp1 should qualify):
HospID | HospName | FacWideAU | FacWideAR | AU
---------------------------------------------------------------
1 Hosp1 0
2 Hosp2 1 0 0
2 Hosp2 0 0
3 Hosp3 1 1 0
3 Hosp3
3 Hosp3 0
etc.
Is there an easier way to write the query than the following (repeating all the selection logic, which has joins and is more convoluted in reality)?
Many thanks!
Select distinct HospID, HospName
from <Hospital Table>
where Description='...whatever...'
etc.
and ID NOT in
(
Select distinct HospID
from <Hospital Table>
where Description='...whatever...'
etc.
and (case when (TO_CHAR(SubmissionMonth, 'MON-YYYY') = 'Mar-2017' AND
(FacWideAU = 1 or FacWideAR = 1 or AU = 1)) then 1 else 0 end) = 1
)
I found another solution: You could use a left join on your table.
Select distinct h1.HospID, h1.HospName
from <Hospital Table> h1
left join <Hospital Table> h2 on h1.HospId = h2.HospId
and (((TO_CHAR(h2.SubmissionMonth, 'MON-YYYY') = 'Mar-2017')
and (isnull(h2.FacWideAU, 0) = 1 or isnull(h2.FacWideAR, 0) = 1 or isnull(h2.AU, 0) = 1))
where Description='...whatever...'
and h2.id is null
etc.