-- Declare variables for poverty level values

DECLARE @contiguousUSOnePerson float
DECLARE @contiguousUSIncrement float

DECLARE @alaskaOnePerson float
DECLARE @alaskaIncrement float

DECLARE @hawaiiOnePerson float
DECLARE @hawaiiIncrement float

 

-- Set the values

SET @contiguousUSOnePerson = 15650
SET @contiguousUSIncrement = 5500

SET @alaskaOnePerson = 19550
SET @alaskaIncrement = 6880

SET @hawaiiOnePerson = 17990
SET @hawaiiIncrement = 6330

 

-- Check to see if the year is in the cw_year_rft table

DECLARE @yrPK nvarchar(4)

set @yrPK = (SELECT yer_rpk FROM cw_year_rft WHERE yer_rpk = 2025)

 

-- Add the year to the table if missing

IF @yrPK IS NULL
BEGIN
INSERT INTO cw_year_rft(yer_rpk, yer_active, yer_last_updated) VALUES(2025,'1',getdate())
END

 

-- Create the records for the base values (one person household)

INSERT INTO [dbo].[cw_poverty_level_rft] ([pvr_lv_rpk],[pvr_lv_yer_rfk],[pvr_lv_one_person],[pvr_lv_stt_rfk])
VALUES (73,2025,@contiguousUSOnePerson,NULL)

INSERT INTO [dbo].[cw_poverty_level_rft] ([pvr_lv_rpk],[pvr_lv_yer_rfk],[pvr_lv_one_person],[pvr_lv_stt_rfk])
VALUES (74,2025,@alaskaOnePerson,'02')

INSERT INTO [dbo].[cw_poverty_level_rft] ([pvr_lv_rpk],[pvr_lv_yer_rfk],[pvr_lv_one_person],[pvr_lv_stt_rfk])
VALUES (76,2025,@hawaiiOnePerson,'12')

 

-- Create the records for additional person increments

INSERT INTO [dbo].[cw_poverty_level_increment] ([pvr_lv_inc_yer_rfk],[pvr_lv_inc_stt_rfk],[pvr_lv_inc_amount],[pvr_lv_inc_min_hh_size],[pvr_lv_inc_max_hh_size])
VALUES (2025,NULL,@contiguousUSIncrement, NULL, NULL)

INSERT INTO [dbo].[cw_poverty_level_increment] ([pvr_lv_inc_yer_rfk],[pvr_lv_inc_stt_rfk],[pvr_lv_inc_amount],[pvr_lv_inc_min_hh_size],[pvr_lv_inc_max_hh_size])
VALUES (2025,'02',@alaskaIncrement, NULL, NULL)

INSERT INTO [dbo].[cw_poverty_level_increment] ([pvr_lv_inc_yer_rfk],[pvr_lv_inc_stt_rfk],[pvr_lv_inc_amount],[pvr_lv_inc_min_hh_size],[pvr_lv_inc_max_hh_size])
VALUES (2025,'12',@hawaiiIncrement, NULL, NULL)

 

-- The rest is for updating poverty level records for the year

DECLARE @SttRFK nvarchar(2)
DECLARE @OnePsn real
DECLARE @addlPsn real

 

-- Default values to lower 48

SET @OnePsn = @contiguousUSOnePerson
SET @addlPsn = @contiguousUSIncrement

 

-- Get the grantee state

SET @SttRFK = (SELECT dmn_stt_rfk FROM cw_domain WHERE dmn_dmn_tp_rfk = '1')

 

-- Alaska

IF @SttRFK = '02'
BEGIN
SET @OnePsn = @alaskaOnePerson
SET @addlPsn = @alaskaIncrement
END

 

-- Hawaii

IF @SttRFK = '12'
BEGIN
SET @OnePsn = @hawaiiOnePerson
SET @addlPsn = @hawaiiIncrement
END

 

-- Update existing values

UPDATE cw_poverty_level_assessment
SET pvr_lv_poverty_level = pvr_lv_household_income / (@OnePsn + (pvr_lv_household_size - 1) * @addlPsn)
WHERE YEAR(pvr_lv_date) = 2025