Use conditional criteria to change Row_Number like operation

Question

Using SQL server 2012. I am using variables to identify the number of times "various criteria" are met within my overall dataset. And I want to take half of these instances and do one thing "first_half_thing" and with the other half do a "second_half_thing". It works okay when I only include those instances where criteria are met. But I want to perform this operation on my whole data set with 3 or 4 different versions of the "various criteria".

My first try was to try and change the value of @CriteriaMetCount when the "various criteria" are met and use that count to do either the "first_half_thing" or the "second_half_thing" but that didn't work. Is there a way to Partition by and then use different sets of "various criteria"

declare @TargetNumber BIGINT
declare @TargetHalf BIGINT
declare @CriteriaMetCount BIGINT

set @CriteriaMetCount=1
Set @TargetNumber=(

select count(distinct(AI2.AccountID)) from Table_AccountInfo AI2
where
AI2.OrgID in (
    select distinct(Org2.OrgID) from Table_Organization Org2
        where Org2.OrganizationName like ('%Name%'))
and (various criteria here)
)

Set @TargetHalf= (select iif(
(@TargetNumber%2)>0,((@TargetNumber+1)/2),(@TargetNumber/2)))

select  AI.AccountID, @targethalf, @targetnumber,ROW_Number() over(order by AI.AccountID) AS ROW_COUNT,
case when ((ROW_Number() over(order by AI.AccountID))<=@targethalf) then  'first_half_thing' else 'second_half_thing' end, 
iif(((ROW_Number() over(order by AI.AccountID))%2=0),(@CriteriaMetCount+1),@CriteriaMetCount)


from Table_AccountInfo LI
where AI.AccountID >= @TargetNumber
and AI.OrgID in (
        select distinct(Org.OrgID) from Table_Organization Org
        where Org.OrganizationName like ('%Name%')) 


and (various criteria here)

score 0 · Answer 1 · answered May 24 '13 at 18:13

Okay I did this: added a temp table which did the first operation (evaluated entire data set for "various criteria") and then did an easy case when to check the current AccountID vs the ones stored in the temp table.

My first experience using a temp table in such fashion and it worked, so there you go.

Use conditional criteria to change Row_Number like operation

1 Answers1