Flagging an Address based on a conditional occurrences

I’d like to create a new 'Hit?' column that returns Hit if a SiteLocation has a 1 in column 'Required?' and does not have Pie-Active in column 'Product-Status'.

Here's the starting table:

SiteAddress	Required?	Product-Status
1234 something street	1	Cake-active
1234 something street	0	Pie-active
1234 something street	0	Cake-inactive
1234 something street	0	KeyLime-active
1234 something street	0	Vanilla Sundae-active
1234 something street	0	Pie-inactive
567 other street	0	Cake-active
567 other street	1	Cake-inactive
567 other street	0	KeyLime-inactive

In this example, SiteLocation 567 other street would get flagged as hit since it contains at least one 1 and does not contain any Pie-active. See below for what I'm hoping to create:

SiteAddress	Required?	Product-status	Hit?
1234 something street	1	Cake-active	No Hit
1234 something street	0	Pie-active	No Hit
1234 something street	0	Cake-inactive	No Hit
1234 something street	0	KeyLime-active	No Hit
1234 something street	0	Vanilla Sundae-active	No Hit
1234 something street	0	Pie-inactive	No Hit
567 other street	0	Cake-active	Hit
567 other street	1	Cake-inactive	Hit
567 other street	0	KeyLime-inactive	Hit

Although 1234 something street has 1 in column 'Required?', it also has Pie-active so this is an example where the condition is not satisfied.

I understand that I need to use logic similar to this:

condition1 = Required? == 1
condition2 = Product-Status != 'Pie-Active'
RelevantAddresses  = distinct SiteAddresses satisfying condition 1 and condition 2
New Column: Hit? = if SiteAddress is in RelevantAddresses, Hit, otherwise No Hit

Note that SiteAddress, Required? and Product-Status are all alias names (I had to manually create them) so I believe I need to create sub queries to access these fields.

I attempted to create this query but I'm running into a bit of a wall. I have this so far:

DECLARE @Required? as bit
DECLARE @COND1 as bit

SET @COND1 = CASE WHEN @Required? = 1 THEN 1 ELSE 0 END

SELECT @Required? = (SELECT 
CASE
WHEN
.... subquery ... as Required?

    
SET @COND2 = CASE WHEN @Product-Status != 1 THEN 1 ELSE 0 END

SELECT @Product-Status = (SELECT
CASE 
WHEN 
.... subquery ... as Product-Status

Thanks

Solution

You can use a subquery:

select t1.*, 
       case when tmp.SiteAddress is not null 
            then 'Hit'
            else 'No Hit'
       end as [Hit?]
from your_table t1
left join 
(
  select SiteAddress
  from your_table
  group by SiteAddress
  having max([Required?]) = 1  
     and sum(case when [Product-status] = 'Pie-Active' then 1 else 0 end) = 0
) tmp on t1.SiteAddress = tmp.SiteAddress

or even shorter do it with window functions:

select *, 
       case when sum(case when [Product-status] = 'Pie-Active' then 1 else 0 end) over (partition by SiteAddress) = 0
                 and max([Required?]) over (partition by SiteAddress) = 1  
            then 'Hit'
            else 'No Hit'
       end as [Hit?]
from your_table

Flagging an Address based on a conditional occurrences

SQLFiddle demo