From: SAL on
Thank you for your reply Plamen. I wound up with the following solution, not
sure if it's the most efficient but.....

SELECT AnnId FROM AffectedEasementsGASB
WHERE TransfersAtAnn = 1 AND AnnId NOT IN
(SELECT AnnId FROM AffectedEasementsGASB
WHERE TransfersAtAnn IS NULL
GROUP BY AnnId
HAVING Count(AnnId) > 0)
GROUP BY AnnId
HAVING Count(AnnId) > 0))

S

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:yL-dnXCwheojgVPWnZ2dnUVZ_qI7AAAA(a)speakeasy.net...
> Here is one solution:
>
> SELECT AnnId
> FROM AffectedEasementsGASB
> GROUP BY AnnId
> HAVING MAX(CAST(TransfersAtAnn AS INT)) = 1
> AND COUNT(TransfersAtAnn) = COUNT(*);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on
The solution that I posted has a better execution plan that the one you posted. But always best to test with your real data.

--
Plamen Ratchev
http://www.SQLStudio.com
From: SAL on
Hmmm, it does work. I just tested it. :)

S
P.S. still trying to understand it.


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:yL-dnXKwhequvVPWnZ2dnUVZ_qI7AAAA(a)speakeasy.net...
> The solution that I posted has a better execution plan that the one you
> posted. But always best to test with your real data.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on
The HAVING clause predicates implement the logic. The MAX predicate guarantees there is at least one with value 1.
COUNT(TransfersAtAnn) is count excluding NULLs and COUNT(*) is count including NULLs, if they are equal then there are
no NULLs.

--
Plamen Ratchev
http://www.SQLStudio.com
From: SAL on
That's very close to what I thought. Thank you for your explanation
Plamen...

S

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:JqOdnb_ks8FzyVPWnZ2dnUVZ_s6dnZ2d(a)speakeasy.net...
> The HAVING clause predicates implement the logic. The MAX predicate
> guarantees there is at least one with value 1. COUNT(TransfersAtAnn) is
> count excluding NULLs and COUNT(*) is count including NULLs, if they are
> equal then there are no NULLs.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


First  |  Prev  | 
Pages: 1 2 3
Prev: Qs re CTE
Next: Interested in SQL Server Audit Tool