August 13, 2008 at 11:36 am
Below is the code. I need to make sure that this column [EDI_CLM_SEQ_NBR] matches the total count for dtl table for each CLM_KEY. i.e. If [EDI_CLM_SEQ_NBR] has value 3 then there must be 3 details for that HDR. SO when i SELECT
SO one HDR with Clm_key 1111 and EDI_CLM_SEQ_NBR = 3 has three deatils, and I have to make sure that this 3 matches the count of CLM_KEY in the DTL tables. ( I Know u are thinking..., But in my case it is possible that user might go and alter the CLM_KEY in the detail
tbl)
SELECT
h.[EDI_CLM_ID]
,h.[EDI_CTL_TS]
,h.[EDI_CLM_SEQ_NBR] ,h.[VEND_NM]
,h.[REC_TYP_CD]
,h.[BILG_NPI_ID] ,h.[BILG_TAX_ID]
,h.[BILG_PROV_ZIP_CD]
,h.[VEND_CLM_ID]
,h.[EDI_CLM_ID] as [REF_CLM_NO]
,h.[PAT_FST_NM]
,h.[PAT_MID_NM]
,h.[PAT_LST_NM]
,h.[PAT_HCSC_ID]
,h.[PAT_MCAID_ID]
,h.[PAT_GRP_ID]
,h.[PAT_DOB]
,h.[BILL_AMT]
,h.[ADJ_IND]
,h.[RNDRG_NPI_ID]
,h.[RNDRG_TAXNMY_CD]
,h.[ENCTR_TYP_CD]
,h.[PD_AMT]
,d.[CLM_LN_NBR]
,d.[PROC_CD]
,d.[PROC_MDFYR_1_CD]
,d.[PROC_MDFYR_2_CD]
,d.[PROC_MDFYR_3_CD]
,d.[PROC_MDFYR_4_CD]
,d.[SVC_FR_DT]
,d.[SVC_TO_DT]
,h.[CLM_KEY]
FROM dbo.CLM_HDR AS h
INNER JOIN
CLM_LN AS d
ON (h.CLM_KEY = d.CLM_KEY)
WHERE NOT EXISTS (SELECT 1 AS Expr1 FROM CLM_LN d1
WHERE d1.CLM_KEY = d.CLM_KEY AND
d1.[CLM_LN_STA_CD] <> 'S')
HERE IS TABLE STRUCTURE for both
CREATE TABLE [dbo].[CLM_HDR](
[CLM_KEY] [int] IDENTITY(1,1) NOT NULL,
[EDI_CLM_ID] [varchar](20) NOT NULL,
[EDI_CTL_TS] [varchar](25) NOT NULL,
[EDI_CLM_SEQ_NBR] [char](3) NOT NULL,
[VEND_NM] [varchar](35) NULL,
[VEND_CLM_ID] [varchar](20) NULL,
[REC_TYP_CD] [char](1) NULL,
[BILG_NPI_ID] [varchar](20) NULL,
[BILG_TAX_ID] [varchar](20) NULL,
[BILG_PROV_ZIP_CD] [varchar](15) NULL,
[PAT_FST_NM] [varchar](25) NULL,
[PAT_MID_NM] [varchar](25) NULL,
[PAT_LST_NM] [varchar](35) NULL,
[PAT_HCSC_ID] [varchar](20) NULL,
[PAT_MCAID_ID] [varchar](20) NULL,
[PAT_GRP_ID] [varchar](30) NULL,
[PAT_DOB] [varchar](8) NULL,
[BILL_AMT] [decimal](12, 2) NULL,
[PD_AMT] [decimal](10, 2) NULL,
[ADJ_IND] [char](1) NULL,
[RNDRG_NPI_ID] [varchar](20) NULL,
[RNDRG_TAXNMY_CD] [varchar](30) NULL,
[ENCTR_TYP_CD] [varchar](5) NULL,
[CLM_HDR_STA_CD] [char](1) NULL,
[CLM_HDR_VRSN_NBR] [smallint] NOT NULL,
[CLM_WARNING_FLG] [char](1) NULL,
[RCVD_FR_EDI_TS] [datetime] NULL,
[SNT_TO_EDI_TS] [datetime] NULL,
[OMNICAID_CLM_ID] [decimal](17, 0) NULL,
[RCVD_FR_OMNICAID_TS] [datetime] NULL,
[LST_CHG_USR_ID] [varchar](100) NULL,
[LST_CHG_TS] [datetime] NULL,
CONSTRAINT [PK_CLM_HDR] PRIMARY KEY CLUSTERED
(
[CLM_KEY] ASC
** DTL Table
CREATE TABLE [dbo].[CLM_LN](
[CLM_LN_KEY] [int] IDENTITY(1,1) NOT NULL,
[CLM_KEY] [int] NOT NULL,
[CLM_LN_NBR] [smallint] NOT NULL,
[PROC_CD] [varchar](10) NULL,
[PROC_MDFYR_1_CD] [char](2) NULL,
[PROC_MDFYR_2_CD] [char](2) NULL,
[PROC_MDFYR_3_CD] [char](2) NULL,
[PROC_MDFYR_4_CD] [char](2) NULL,
[SVC_FR_DT] [char](8) NULL,
[SVC_TO_DT] [char](8) NULL,
[CLM_LN_STA_CD] [char](1) NULL,
[CLM_LN_VRSN_NBR] [smallint] NOT NULL,
[OMNICAID_CLM_ID] [decimal](17, 0) NULL,
[RCVD_FR_OMNICAID_TS] [datetime] NULL,
[LST_CHG_USR_ID] [varchar](100) NULL,
[LST_CHG_TS] [datetime] NULL,
CONSTRAINT [PK_CLM_LN] PRIMARY KEY CLUSTERED
August 13, 2008 at 11:54 am
I think you can do this with a simple UPDATE ...FROM:
find the records that might be affected:
SELECT * FROM CLM_HDR
INNER JOIN (SELECT EDI_CLM_ID,COUNT(EDI_CLM_SEQ_NBR) AS EDI_CLM_SEQ_NBR
FROM CLM_DTL
GROUP BY EDI_CLM_ID ) X ON CLM_HDR .EDI_CLM_ID = X.EDI_CLM_ID
WHERE CLM_HDR .EDI_CLM_SEQ_NBR <> X.EDI_CLM_SEQ_NBR
UPDATE CLM_HDR
SET CLM_HDR .EDI_CLM_SEQ_NBR = X.EDI_CLM_SEQ_NBR
FROM (SELECT EDI_CLM_ID,COUNT(EDI_CLM_SEQ_NBR) AS EDI_CLM_SEQ_NBR
FROM CLM_DTL
GROUP BY EDI_CLM_ID ) X
WHERE CLM_HDR .EDI_CLM_ID = X.EDI_CLM_ID
AND CLM_HDR .EDI_CLM_SEQ_NBR <> X.EDI_CLM_SEQ_NBR
Lowell
August 13, 2008 at 12:08 pm
Loweel I belive U missunderstood me or my explanation was not well enough to give you the hint.....................
the column in HDr tbl [EDI_CLM_SEQ_NBR] will have int value like 1,2, 3, 4, and so on........
If 4 means that i have 4 detail line for that claim HDR. HDR tbl and DTL tbl are tied through CLM_KEY . But now one of the user went and change the CLM key in the Dtl table to something else, so when i do my query( which i have posted earlier) , I will only have 3 detail for that HDR ( provieded that all has status S)
But since i have EDI_CLM_SEQ_NBR = 4 and then only 3 detail ,which is not a match, i DOn't want to select this HDR and its belonging Details( which is rt now only 3, but originally 4)
Thanks
August 13, 2008 at 12:27 pm
ok, so it's not the count, but the MAX(EDI_CLM_SEQ_NBR) in the DTL that is no longer matching the header, right?
same query and update, but with slight changes?
--in theory, this will compar the CLM_HDR to the highest seqence number in the details, and find what doesn't match?
SELECT * FROM CLM_HDR
INNER JOIN (SELECT EDI_CLM_ID,MAX(EDI_CLM_SEQ_NBR) AS EDI_CLM_SEQ_NBR
FROM CLM_DTL
GROUP BY EDI_CLM_ID ) X ON CLM_HDR .EDI_CLM_ID = X.EDI_CLM_ID
WHERE CLM_HDR .EDI_CLM_SEQ_NBR <> X.EDI_CLM_SEQ_NBR
UPDATE CLM_HDR
SET CLM_HDR .EDI_CLM_SEQ_NBR = X.EDI_CLM_SEQ_NBR
FROM (SELECT EDI_CLM_ID,,MAX(EDI_CLM_SEQ_NBR) AS EDI_CLM_SEQ_NBR
FROM CLM_DTL
GROUP BY EDI_CLM_ID ) X
WHERE CLM_HDR .EDI_CLM_ID = X.EDI_CLM_ID
AND CLM_HDR .EDI_CLM_SEQ_NBR <> X.EDI_CLM_SEQ_NBR
Lowell
August 13, 2008 at 12:45 pm
I belive we are still not on the same page here..........
There is no such column name in detail table called [EDI_CLM_SEQ_NBR].
Here If u see in the HDr, For CLM_KEY 3 and EDI_CLM_ID 33333, the [EDI_CLM_SEQ_NBR] is 03, meaning that i have 3 detail records in the Detail table with CLM_LN_NBR as 1 , 2 and 3.
So now if USER went to detail table and alter one CLM_KEY from 3 to 4 then I only have 2 records with CLM_KEY 3, but in my header tbl i have
EDI_CLM_SEQ_NBR] is 03. meaning that there were 3 detail records for this claim, but SOmeone alter it at the detail tbl, so now i don't have match, so i don't want to select it ...
ALSO, i have to make sure that every detail claim belonging to one HDR have status S( thsi is done by my query that i have provided earlier)
Hope this will clarify the situation
Thanks
let me give you my Example here ---------
HEADER table ( there are more Columns, which i Don't think we nee)
CLM_KEY EDI_CLM_ID EDI_CLM_SEQ_NBR CLM_HDR_STA_CD
----------- -------------------- --------------- --------------
1 11111 02 S
2 22222 01 S
3 33333 03 S
4 55555 01 S
ALso Detail table
CLM_KEY CLM_LN_NBR CLM_LN_STA_CD
----------- ---------- -------------
1 1 S
1 2 H
2 1 S
4 1 S
3 1 S
3 2 S
3 3 S
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply