window functions and grouping – how to propagate data from row 1 through row 3 in a partition?

I have this query below:

select
    Contact.IndividualID,
    Contact.IndividualID as ContactId,
    Contact.CaseNumber as CaseID,
    [Case].ProgramCode as Benefit,
    Contact.Email as EmailAddress,
    '' as EmailTo,
    Contact.FirstName,
    [Case].CaseProgramIndividualStatusCode,
    [Case].ReviewDueDate as RenewDueDate,
    [Case].ReviewDueDate as BenefitExpirationDate,
    [Case].ProgramCode as ProgramCode,
    pref.Phone as MobileNumber,
    Contact.IsHeadOfHousehold,
    row_number() over (partition by Contact.CaseNumber order by Contact.IsHeadOfHousehold desc) as row
from
    SOMETABLE_Contact_Dev Contact    
inner join
    SOMETABLE_Case_Dev [Case] on Contact.IndividualID = [Case].IndividualID and Contact.CaseNumber = [Case].CaseNumber
left join
    [SSP RE Preferences] pref on Contact.IndividualID = pref.ContactId
where 
    (([Case].RenewalTypeCode = 'AC' and [Case].ReviewStatusCode in ('RI','RR')) or 
    ([Case].RenewalTypeCode = 'PS' and [Case].ReviewStatusCode = 'RI')) and 
        DateDiff(day, getdate(), [Case].ReviewDueDate) = 40 and 
        Contact.Email is not null and
        [Case].ProgramCode in ('KC','KT','CC','MA')

And here’s the result set from running this query:

Here’s what I’m having trouble with. What I want to do is for when there’s a grouping as defined by the partition, I want to put as the EmailTo field the Email Address for the top record of the group (row 1):

When there’s no grouping, just use the Email Address for the EmailTo field. What’s the best way to go about this?

Answer

You could use first_value() over the relevant partition.

e.g., Assuming you mean the same partition as used for row_number, add the following line after your row_number line

FIRST_VALUE(Contact.Email) OVER (partition by Contact.CaseNumber order by Contact.IsHeadOfHousehold desc) AS first_email

Advertisement

Answer