Update a table with SQL GROUP BY CASE statement

Question

DimAward
--------  
AwardKey (PK)  
TypeCode  
SourceCode  
SourceKey  

CREATE TABLE [Final].[DimAward](
    [AwardKey] [int] IDENTITY(1,1) NOT NULL,
    [SourceKey] [varchar](10) NOT NULL,
    [SourceCode] [varchar](10) NOT NULL,
    [TypeCode] [varchar](10) NOT NULL,
 CONSTRAINT [pk_DimAward] PRIMARY KEY NONCLUSTERED 
(
    [AwardKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [uq_DimAward_SourceKey] UNIQUE CLUSTERED 
(
    [SourceKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]
) ON [PRIMARY]

GO

FactAwardDetail
----------------
AwardKey (FK to DimAward.AwardKey)  
AidYearkey  
StudentKey  
TermKey  
AwardLevelKey  

CREATE TABLE [Final].[FactAwardDetail](
    [AwardDetailKey] [int] IDENTITY(1,1) NOT NULL,
    [AwardKey] [int] NOT NULL,
    [AidYearKey] [int] NOT NULL,
    [StudentKey] [int] NOT NULL,
    [TermKey] [int] NOT NULL,
    [AwardLevelKey] [int] NOT NULL,
 CONSTRAINT [pk_FactAwardDetail] PRIMARY KEY NONCLUSTERED 
(
    [AwardDetailKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [uq_FactAwardDetail_CompoundID] UNIQUE NONCLUSTERED 
(
    [StudentKey] ASC,
    [AidYearKey] ASC,
    [TermKey] ASC,
    [AwardKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

FactAwardDetail has multiple records for a given StudentKey/AidYearkey/Termkey (a student can have more than one award in a given aidyear/term) - probably need to group by StudentKey, AidYearkey, TermKey.

I need to update FactAwardDetail.AwardLevelKey with FactAwardDetail.AwardKey based on the following logic.

if any record for a given StudentKey/AidYearKey/TermKey combination satisfies the following:

SELECT 1  
From DimAward a INNER JOIN  
FactAwardDetail ad on a.AwardKey = ad.AwardKey  
Where a.SourceKey = 'powell'  
group by ad.StudentKey, ad.AidYearKey, ad.TermKey  

then

set ad.AwardLevelkey = ad.awardkey  
set ad.AwardLevelKey for remaining records for the same StudentKey/AidYearkey/Termkey combination to -1

if none of the records satisfied the above condition, then look for a new condition as follows: If any record for a given StudentKey/AidYearkey/Termkey combination satisfies the following:

SELECT 1
From DimAward a INNER JOIN
FactAwardDetail ad on a.AwardKey = ad.AwardKey
Where a.SourceKey = 'regt'
group by ad.StudentKey, ad.AidYearKey, ad.TermKey

then

set ad.AwardLevelkey = ad.awardkey
set rest of ad.AwardLevelKey for remaining records for the same StudentKey/AidYearkey/Termkey combination to -1

if none of the records satisfied the above two conditions, then look for a new condition as follows:

If any record for a given StudentKey/AidYearkey/Termkey combination satisfies the following:

SELECT 1
From DimAward a INNER JOIN
FactAwardDetail ad on a.AwardKey = ad.AwardKey
Where a.SourceKey = 'presm'
group by ad.StudentKey, ad.AidYearKey, ad.TermKey

then set ad.AwardLevelkey = ad.awardkey set rest of ad.AwardLevelKey for remaining records for the same StudentKey/AidYearkey/Termkey combination to -1

if none of the records satisfied the above 3 conditions, I will need to repeat this pattern a few more times.

I would appreciate any help with writing a t-sql statement.

Answer 1

You must not thing in constructs like if-then-else. SQL was designed for set operations so use it that way. First you need to have a ordered list of the SourceKeys:

declare @SourceKeyPriority table
(
  Priority int, SourceKey varchar(10)
)
insert into @SourceKeyPriority values (1,'powell')
insert into @SourceKeyPriority values (2,'regt')
insert into @SourceKeyPriority values (3,'presm')

It doesn't need to be table variable, just whatever works for you. Then update all records to -1 We will subsequently update those rows that have matching award. Then you need to find the best (highest priority) fit for a given ad.StudentKey, ad.AidYearKey, ad.TermKey combination. Wrap that in a CTE and use it to update your table:

with cte as
(
  select 
    ad.StudentKey, 
    ad.AidYearKey, 
    ad.TermKey, 
    min(x.Priority) as Priority
  from 
    @SourceKeyPriority x
    inner join DimAward a
      on a.SourceKey = x.SourceKey
    inner join FactAwardDetail ad 
      on a.AwardKey = ad.AwardKey
  group by 
    ad.StudentKey, ad.AidYearKey, ad.TermKey
)
update ad
set AwardLevelKey = ad.AwardKey
from FactAwardDetail ad
inner join cte 
  on cte.StudentKey = ad.StudentKey
  and cte.AidYearKey = ad.AidYearKey
  and cte.TermKey = ad.TermKey
inner join @SourceKeyPriority x
  on x.Priority = cte.priority
inner join DimAward a
  on a.SourceKey = x.SourceKey
  and a.AwardKey = ad.AwardKey