--=========== TEST DATA ===========================

-- Reset all NOT NULL to NULL...

--Source Table:
if object_id('[dbo].[CMC_BLCO_COMM_ITEM]') is not null drop table [dbo].[CMC_BLCO_COMM_ITEM]

CREATE TABLE [dbo].[CMC_BLCO_COMM_ITEM](
 [BLEI_CK] [int] NULL,
 [CSPI_ID] [char](8) NULL,
 [PMFA_ID] [char](2) NULL,
 [BLCO_SEQ_NO] [smallint] NULL,
 [COAR_ID] [char](12) NULL,
 [BLCO_EFF_DT] [datetime] NULL,
 [BLCO_TERM_DT] [datetime] NULL,
 [BLCO_PCT] [int] NULL,
 [ROW_MODIFICATION_TYPE] [char](1) NULL,
 [CR_RUN_CYC_EXCTN_SK] [int] NULL,
 [LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL
) ON [PRIMARY];

insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( null, 1 );
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( 96, 2 );
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( 97, 3 );

--Target Table:
if object_id('[dbo].[BILL_CMSN]') is not null drop table [dbo].[BILL_CMSN]

CREATE TABLE [dbo].[BILL_CMSN](
 [BILL_CMSN_SK] [int] NULL,
 [SRC_UNQ_BILL_ENTY_ID] [int] NULL,
 [PDT_PLAN_ID] [varchar](50) NULL,
 [FEE_DISC_ID] [varchar](50) NULL,
 [BILL_CMSN_SEQ_NUM] [smallint] NULL,
 [SRC_SYS_CD] [varchar](30) NULL,
 [CR_RUN_CYC_EXCTN_SK] [int] NULL,
 [LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL,
 [CRD_DTTM] [datetime] NULL,
 [UPDT_DTTM] [datetime] NULL,
 [SRC_SYS_CD_SK] [int] NULL,
 [BILL_ENTY_SK] [int] NULL,
 [PDT_PLAN_SK] [int] NULL,
 [CMSN_ARGT_SK] [int] NULL,
 [BILL_CMSN_EFF_DT_SK] [int] NULL,
 [BILL_CMSN_TRMN_DT_SK] [int] NULL,
 [BILL_CMSN_PREM_PCT] [int] NULL,
) ON [PRIMARY]

insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 1 );
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 2 );
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 3 );

--Look Up Table:
if object_id('[dbo].[BILL_ENTY]') is not null drop table [dbo].[BILL_ENTY]

CREATE TABLE [dbo].[BILL_ENTY](
 [BILL_ENTY_SK] [int] NULL,
 [SRC_UNQ_BILL_ENTY_ID] [int] NULL,
 [SRC_SYS_CD] [varchar](30) NULL,
 [CR_RUN_CYC_EXCTN_SK] [int] NULL,
 [LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL,
 [CRD_DTTM] [datetime] NULL,
 [UPDT_DTTM] [datetime] NULL,
 [SRC_SYS_CD_SK] [int] NULL,
 [BILL_GRP_SK] [int] NULL,
 [GRP_SK] [int] NULL,
 [SUB_GRP_SK] [int] NULL,
 [SUBS_SK] [int] NULL,
 [ALT_FUNDG_AGRE_SK] [int] NULL,
 [BILL_LVL_CD_SK] [int] NULL,
 [BILL_ENTY_LST_BILL_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_LST_BILL_END_DT_SK] [int] NULL,
 [BILL_ENTY_LST_APV_BILL_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_LST_BILL_PMT_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_LST_TOLRNC_PAID_BILL_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_LST_RCONCL_BILL_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_LST_BILL_GNT_DT_SK] [int] NULL,
 [BILL_ENTY_LST_MLTP_MODE_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_LST_MLTP_MODE_END_DT_SK] [int] NULL,
 [BILL_ENTY_BILL_BAT_STU_CD_SK] [int] NULL,
 [BILL_ENTY_LST_BILL_MNL_ALLOCS_DUE_DT_SK] [int] NULL,
 [BILL_ENTY_PRCS_STU_CD_SK] [int] NULL,
 [BILL_ENTY_SSPND_BILL_CD_SK] [int] NULL,
 [BILL_ENTY_LST_ARCV_DT_SK] [int] NULL,
 [BILL_ENTY_ELIG_THRU_DT_UPDT_CD_SK] [int] NULL,
 [BILL_ENTY_TOLRNC_METH_CD_SK] [int] NULL,
 [BILL_ENTY_NET_DUE_AMT] [money] NULL,
 [BILL_ENTY_NET_CURR_OUTSTND_SUSP_AMT] [money] NULL,
 [BILL_ENTY_RCONCL_IND] [char](1) NULL,
 [BILL_ENTY_SUBS_RATNG_IND] [char](1) NULL,
 [BILL_ENTY_RMTC_TOLRNC_AMT] [money] NULL,
 [BILL_ENTY_RMTC_TOLRNC_PCT] [int] NULL,
 [SRC_UNQ_BILL_LVL_ID] [int] NULL 
) ON [PRIMARY]

insert [dbo].[BILL_ENTY] ([BILL_ENTY_SK], [SRC_UNQ_BILL_ENTY_ID]) values ( 33, 96 );

--Condition:

--I am working on DW project where I need to validate if the data from the Source Column 
--   has populated correctly on the Target Column based on a lookup condition. I have given the 
--  create table statements for the Source table,Target table and the Lookup table above. 
--  THe actual columns used in this condition are highlighted. The condition is given below,

--If CMC_BLCO_COMM_ITEM.BLEI_CK is NULL then set BILL_ENTY_SK = -1 

--ELSE IF CMC_BLCO_COMM_ITEM.BLEI_CK is NULL THEN Perform a LOOKUP on 
--BILL_ENTY WHERE BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = CMC_BLCO_COMM_ITEM.BLEI_CK and 
--RETURN BILL_ENTY.BILL_ENTY_SK 

--ELSE IF Lookup fails then set BILL_ENTY_SK = -1

--I need some help to create a SQL statement which will validate if the data from the source column has
-- populated correctly on the table column based on the above condition.

--========= CHECK BEFORE UPDATES =============
select [CR_RUN_CYC_EXCTN_SK], [BILL_ENTY_SK] from [dbo].[BILL_CMSN];

--========= SUGGESTED SOLUTION ================
UPDATE [dbo].[BILL_CMSN]
	SET BILL_ENTY_SK = CASE WHEN CI.BLEI_CK IS NULL THEN -1 
							WHEN BE.BILL_ENTY_SK IS NULL THEN -1
							ELSE BE.BILL_ENTY_SK END
FROM [dbo].[BILL_CMSN] BC
	INNER JOIN [dbo].[CMC_BLCO_COMM_ITEM] CI
		ON BC.[CR_RUN_CYC_EXCTN_SK] = CI.[CR_RUN_CYC_EXCTN_SK] -- ????? WHAT'S THE JOIN ???????
	LEFT OUTER JOIN dbo.BILL_ENTY BE
		ON BE.SRC_UNQ_BILL_ENTY_ID = CI.BLEI_CK;

--========= CHECK AFTER UPDATES =============
select [CR_RUN_CYC_EXCTN_SK], [BILL_ENTY_SK] from [dbo].[BILL_CMSN];