SQL stored procedure to load the error and correct record based on some business rules

Transact-SQL
1

I will explain the requirement in simple terms and minimal data is as below:

SSN is primary key and all other columns are NOT NULL constraints

Source Data:

SSN Address County State DOB
1234 123 st dfg collin WI 04/19/2001
5234 923 at hgf collin WI 04/24/2005
9234 823 at adf las col WI 07/27/2009
3234 Null las col WI 09/27/2009
9238 523 at adf Null WI 02/27/2009

Now i need to have sql stored procedure which create two tables master_sds and error_sds with output shown below when we load first time

Output:

master_sds:
SSN Address County State DOB
1234 123 st dfg collin WI 04/19/2001
5234 923 at hgf collin WI 04/24/2005
9234 823 at adf las col WI 07/27/2009

Error_sds:
SSN Address County State DOB Error_desc start_date end_date current_flag cre_dt
3234 Null las col WI 09/27/2009 address blank 05/01/2014 null Y 12/03/2024
9238 523 at adf Null WI 02/27/2009 county blank 05/01/2014 null Y 12/03/2024

Now when i load next time my source data will be like below:

Source Data:

SSN Address County State DOB
1234 123 st dfg collin WI 04/19/2001
5234 923 at hgf collin WI 04/24/2005
9234 823 at adf las col WI 07/27/2009
3234 564 lalfa las col WI 09/27/2008
9238 523 at adf Null WI 02/27/2009
7238 500 at adf irvi Null 09/27/2015

now the sql stored procedure should generate output as below:

master_sds:
SSN Address County State DOB
1234 123 st dfg collin WI 04/19/2001
5234 923 at hgf collin WI 04/24/2005
9234 823 at adf las col WI 07/27/2009
3234 564 lalfa las col WI 09/27/2008

Error_sds:
SSN Address County State DOB Error_desc start_date end_date current_flag cre_dt
3234 lalfa las col WI 09/27/2009 05/01/2014 12/03/2024 N 12/03/2024
9238 523 at adf Null WI 02/27/2009 county blank 05/01/2014 null Y 12/03/2024
7238 500 at adf irv Null 09/27/2015 state is balnk 05/01/2014 null Y 12/03/2024

Please provide complete stored procedures based on the samples provided

2

We are not here to do your work for you. Have you try anything at all ?

1 Like
3

Yes, i have tried with below query but unable to meet the exact requirements..

As per requirement we need to update the error target table when source is updated with some values.. But bcoz where condition in sql the ssn nuumber is not showing so unable to load the correct value and update the timestamp and current indicator in error table

CREATE PROCEDURE LoadErrorSDSData
AS
BEGIN
DECLARE @current_date DATE = GETDATE();

-- Update existing error records to end the current ones if they have been corrected
UPDATE error_sds
SET 
    end_date = @current_date,
    current_flag = 'N'
WHERE SSN IN (
    SELECT SSN
    FROM source_data
    WHERE Address IS NOT NULL AND County IS NOT NULL AND State IS NOT NULL AND DOB IS NOT NULL
) AND current_flag = 'Y';

-- Insert new error records into error_sds table
INSERT INTO error_sds (SSN, Address, County, State, DOB, Error_desc, start_date, end_date, current_flag, cre_dt)
SELECT
    SSN,
    Address,
    County,
    State,
    DOB,
    CASE
        WHEN Address IS NULL THEN 'Address blank'
        WHEN County IS NULL THEN 'County blank'
        WHEN State IS NULL THEN 'State blank'
        WHEN DOB IS NULL THEN 'DOB blank'
    END AS Error_desc,
    @current_date AS start_date,
    NULL AS end_date,
    'Y' AS current_flag,
    @current_date AS cre_dt
FROM source_data
WHERE Address IS NULL OR County IS NULL OR State IS NULL OR DOB IS NULL;

-- Update error records that have been corrected with new values
UPDATE error_sds
SET 
    Address = COALESCE(source_data.Address, error_sds.Address),
    County = COALESCE(source_data.County, error_sds.County),
    State = COALESCE(source_data.State, error_sds.State),
    DOB = COALESCE(source_data.DOB, error_sds.DOB),
    start_date = @current_date,
    end_date = NULL,
    current_flag = 'Y',
    cre_dt = @current_date
FROM error_sds
INNER JOIN source_data
ON error_sds.SSN = source_data.SSN
WHERE (source_data.Address IS NOT NULL OR source_data.County IS NOT NULL OR source_data.State IS NOT NULL OR source_data.DOB IS NOT NULL)
AND error_sds.current_flag = 'N';

END;
GO

4

Can you provide the table DDL and sample data in DML consumable format ?

also you mention SSN is primary key and all other columns are NOT NULL constraints, but I see the Null in your sample data ?