November 26, 2008 at 9:14 am
Here is my proc code...
INSERT INTO dbo.CLM_HDR
(
[EDI_CLM_ID]
,[VEND_ID]
,[VEND_CLM_NBR]
,[REC_TYP_CD]
,[BILG_NPI_ID]
,[BILG_TAX_ID]
,[BILG_TAXNMY_CD]
,[BILG_PROV_ZIP_CD]
,[PAT_FST_NM]
,[PAT_MID_NM]
,[PAT_LST_NM]
,[PAT_HCSC_ID]
,[PAT_MCAID_ID]
,[PAT_GRP_ID]
,[PAT_DOB]
,[BILL_AMT]
,[PD_AMT]
,[ADJ_IND]
,[RNDRG_NPI_ID]
,[RNDRG_TAXNMY_CD]
,[REFRG_NPI_NBR]
,[CLM_TYP_CD]
,[CLM_HDR_STA_CD]
,[CLM_HDR_VRSN_NBR]
,[RCVD_FR_EDI_TS]
,[LST_CHG_USR_ID]
,[LST_CHG_TS]
,[BILG_PROV_FST_NM]
,[BILG_PROV_MID_NM]
,[BILG_PROV_LST_NM]
,[BILG_PROV_NM_PRX]
,[BILG_PROV_NM_SUX]
,[RNDRG_PFIN]
,[LST_SVC_DT]
,[RNDRG_PROV_FST_NM]
,[RNDRG_PROV_MID_NM]
,[RNDRG_PROV_LST_NM]
,[RNDRG_PROV_NM_PRX]
,[RNDRG_PROV_NM_SUX]
,[BILG_PROV_NM_QLFR]
,[RNDRG_PROV_NM_QLFR]
)
SELECT
[EDI_CLM_ID]
,[VEND_ID]
,[VEND_CLM_NBR]
,[REC_TYP_CD]
,[BILG_NPI_ID]
,[BILG_TAX_ID]
,[BILG_TAXNMY_CD]
,[BILG_PROV_ZIP_CD]
,[PAT_FST_NM]
,[PAT_MID_NM]
,[PAT_LST_NM]
,[PAT_HCSC_ID]
,[PAT_MCAID_ID]
,[PAT_GRP_ID]
,[PAT_DOB]
,CAST(CAST([BILL_AMT] as decimal)/100 as DECIMAL(12, 2)) as [BILL_AMT]
,CAST(CAST([PD_AMT] as decimal)/100 as DECIMAL(12, 2)) as [PD_AMT]
,[ADJ_IND]
,[RNDRG_NPI_ID]
,[RNDRG_TAXNMY_CD]
,[REFRG_NPI_NBR]
,ISNULL(c.Description, 'SALUD') as [CLM_TYP_CD] -- it is better to have user update this field
,CASE [ADJ_IND]-- rather having the default SALUD ( per Russ)
WHEN '7' THEN 'A'
WHEN '8' THEN 'A'
ELSE 'N'END as [CLM_HDR_STA_CD]
,1 as [CLM_HDR_VRSN_NBR]
,GETDATE() as [RCVD_FR_EDI_TS]
,'BatchInsert' as [LST_CHG_USR_ID]
,GETDATE() as [LST_CHG_TS]
,[BILG_PROV_FST_NM]
,[BILG_PROV_MID_NM]
,[BILG_PROV_LST_NM]
,[BILG_PROV_NM_PRX]
,[BILG_PROV_NM_SUX]
,[RNDRG_PFIN]
,[LAST_DT_SVC]
,[RNDRG_PROV_FST_NM]
,[RNDRG_PROV_MID_NM]
,[RNDRG_PROV_LST_NM]
,[RNDRG_PROV_NM_PRX]
,[RNDRG_PROV_NM_SUX]
,[BILG_PROV_NM_QLFR]
,[RNDRG_PROV_NM_QLFR]
FROM dbo.tblMedClmLoadingAll l
LEFT OUTER JOIN dbo.tblCodeList c
ON (c.CodeName = 'EncounterType'
AND c.CodeValue = RIGHT(LTRIM(RTRIM(l.PAT_GRP_ID)), 6))
WHERE l.FLAG_COL IS NULL
GROUP BY
l.[EDI_CLM_ID]
,l.[VEND_ID]
,l.[VEND_CLM_NBR]
,l.[REC_TYP_CD]
,l.[BILG_NPI_ID]
,l.[BILG_TAX_ID]
,l.[BILG_TAXNMY_CD]
,l.[BILG_PROV_ZIP_CD]
,l.[PAT_FST_NM]
,l.[PAT_MID_NM]
,l.[PAT_LST_NM]
,l.[PAT_HCSC_ID]
,l.[PAT_MCAID_ID]
,l.[PAT_GRP_ID]
,l.[PAT_DOB]
,l.[BILL_AMT ]
,l.[PD_AMT]
,l.[ADJ_IND]
,l.[RNDRG_NPI_ID]
,l.[RNDRG_TAXNMY_CD]
,l.[REFRG_NPI_NBR]
,c.[Description]
,l.[BILG_PROV_FST_NM]
,l.[BILG_PROV_MID_NM]
,l.[BILG_PROV_LST_NM]
,l.[BILG_PROV_NM_PRX]
,l.[BILG_PROV_NM_SUX]
,l.[RNDRG_PFIN]
,l.[LAST_DT_SVC]
,l.[RNDRG_PROV_FST_NM]
,l.[RNDRG_PROV_MID_NM]
,l.[RNDRG_PROV_LST_NM]
,l.[RNDRG_PROV_NM_PRX]
,l.[RNDRG_PROV_NM_SUX]
,l.[BILG_PROV_NM_QLFR]
,l.[RNDRG_PROV_NM_QLFR]
All I am doing here is inserting a row into my bast table from loading table and i am joining the anothet codelist table.
here is the structure for codelist tbl............
CREATE TABLE [dbo].[tblCodeList](
[CodeID] [int] IDENTITY(1,1) NOT NULL,
[CodeName] [varchar](50) NOT NULL,
[CodeValue] [varchar](15) NULL,
[Status] [char](1) NOT NULL,
[Description] [varchar](100) NULL,
[LastUpdatedBy] [varchar](25) NULL,
[LastUpdatedDate] [datetime] NULL,
CONSTRAINT [PK_tblCodeList] PRIMARY KEY CLUSTERED
This are the values in the codelist tbl.........
CodeName CodeValue Status Description
-------------------------------------------------- --------------- ------ ----------------------------------------------------------------------------------------------------
EncounterType N72302 A SCI
EncounterType N72400 A SCI
EncounterType N72401 A SCI
EncounterType N72583 A SALUD
Vendor ST4909 A Doral
Vendor S04909 I Doral
Vendor ST4908 A Davis
Vendor S04908 I Davis
Vendor ST0222 A Logisticare
Vendor S00222 I Logisticare
Vendor ST4910 A BlueChip
Vendor S04910 I BlueChip
Vendor ST4911 A BlueChip
Vendor S04911 I BlueChip
Vendor EMP030 A Prime Therapeutics PNF
Vendor EMP031 A Logisticare PNF
Vendor EMP032 A Davis PNF
Vendor EMP033 A Doral PNF
Vendor EMP034 A Primier PNF
Vendor EMP035 I Primier PNF
Vendor EMP037 A BC Provider Adds
This is the part ",CASE [ADJ_IND]-- rather having the default SALUD ( per Russ)
WHEN '7' THEN 'A'
WHEN '8' THEN 'A'
ELSE 'N'END as [CLM_HDR_STA_CD]" I want to modify to add new value in the STA_CD field.
NOW if the VEND_ID in the loading tbl matches the codevalue in the codelist table where Description is 'Doral or davis' and status is active and codename is vendor I want to add 'B' as CLM_HDR_STA_CD in base and rest of the logic will be the same like....CASE [ADJ_IND]-- rather having the default SALUD ( per Russ)
WHEN '7' THEN 'A'
WHEN '8' THEN 'A'
ELSE 'N'END as [CLM_HDR_STA_CD]"
November 26, 2008 at 9:28 am
The short answer is "Yes", you can nest CASE expressions.
-------------------------------------------------------------------
select case when 1 = case when col2 = 'B' then 1
else 0
end then 'Equals'
when 1 > case when col2 = 'B' then 1
else 0
end then 'Greater Than'
else null
end
-------------------------------------------------------------------
If nesting seems confusing, you might try using CTE's to gradually build up the value you want
;with cte1 as
(select case when 1 = case when col2 = 'B' then 1 else 0 end as caseValue)
select case when caseValue = 1 then 'Equals'
when caseValue = 0 then 'Greater Than'
else null
end
from cte1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 26, 2008 at 10:24 am
Thanks,
But how would I Do the join this time? I know I have to have another join this time because the search creteria is different...........
December 1, 2008 at 9:58 am
Different search criteria don't always imply a different join. Oftentimes, tables are joined by a foreign key, but other columns are tested in the WHERE clause to determine which rows should be updated. If you join using a case expression (or most any other function), you will wind up doing a full table scan and performance will be very poor.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 1, 2008 at 10:07 am
Thanks........
This is what I did.......
,.........................
..................................
.................................,
CASE
WHEN cd.Description = 'Doral'THEN 'B'
WHEN cd.Description = 'Davis'THEN 'B'
WHEN [ADJ_IND]= '7'THEN 'A'
WHEN [ADJ_IND]= '8'THEN 'A'
ELSE 'N'END as [CLM_HDR_STA_CD]
,1 as [CLM_HDR_VRSN_NBR]
,GETDATE() as [RCVD_FR_EDI_TS]
,'BatchInsert' as [LST_CHG_USR_ID]
,GETDATE() as [LST_CHG_TS]
,[BILG_PROV_FST_NM]
,[BILG_PROV_MID_NM]
,[BILG_PROV_LST_NM]
,[BILG_PROV_NM_PRX]
,[BILG_PROV_NM_SUX]
,[RNDRG_PFIN]
,[LAST_DT_SVC]
,[RNDRG_PROV_FST_NM]
,[RNDRG_PROV_MID_NM]
,[RNDRG_PROV_LST_NM]
,[RNDRG_PROV_NM_PRX]
,[RNDRG_PROV_NM_SUX]
,[BILG_PROV_NM_QLFR]
,[RNDRG_PROV_NM_QLFR]
FROM dbo.tblMedClmLoadingAll l
LEFT OUTER JOIN dbo.tblCodeList c
ON (c.CodeName = 'EncounterType'
AND c.CodeValue = RIGHT(LTRIM(RTRIM(l.PAT_GRP_ID)), 6)
AND l.FLAG_COL IS NULL )
INNER JOIN dbo.tblCodeList cd
ON l.VEND_ID = cd.CodeValue
WHERE cd.CodeName = 'Vendor'
AND cd.Status = 'A'
GROUP BY
l.[EDI_CLM_ID]
,l.[VEND_ID]
,l.[VEND_CLM_NBR]
,l.[REC_TYP_CD]
,l.[BILG_NPI_ID]
,l.[BILG_TAX_ID]
,l.[BILG_TAXNMY_CD]
,l.[BILG_PROV_ZIP_CD]
,l.[PAT_FST_NM]
,l.[PAT_MID_NM]
,l.[PAT_LST_NM]
,l.[PAT_HCSC_ID]
,l.[PAT_MCAID_ID]
,l.[PAT_GRP_ID]
,l.[PAT_DOB]
,l.[BILL_AMT ]
,l.[PD_AMT]
,l.[ADJ_IND]
,l.[RNDRG_NPI_ID]
,l.[RNDRG_TAXNMY_CD]
,l.[REFRG_NPI_NBR]
,c.[Description]
,l.[BILG_PROV_FST_NM]
,l.[BILG_PROV_MID_NM]
,l.[BILG_PROV_LST_NM]
,l.[BILG_PROV_NM_PRX]
,l.[BILG_PROV_NM_SUX]
,l.[RNDRG_PFIN]
,l.[LAST_DT_SVC]
,l.[RNDRG_PROV_FST_NM]
,l.[RNDRG_PROV_MID_NM]
,l.[RNDRG_PROV_LST_NM]
,l.[RNDRG_PROV_NM_PRX]
,l.[RNDRG_PROV_NM_SUX]
,l.[BILG_PROV_NM_QLFR]
,l.[RNDRG_PROV_NM_QLFR]
,cd.Description
December 1, 2008 at 10:20 am
Thanks. Sorry for letting so many days pass. They let us off early for Thanksgiving holidays.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply