Gregory Brauninger
Reputation: 75
Assistance with error report needed
First off, I apologize for the huge block of code. This is basically a table that contains 4 select statements (each with a different [Lag_Error]) then unions them all together to create an error report.
My boss is on my about making a change to this (her) code. I need this error report to capture if (a.region = 'CR' and a.eff_date = '4/1/2016') the [Lag Error] should display 'Lag should be 1' (like from the first select statement). I was able to accomplish this by using an update statement at the end but she said that I need to go back through the code and make changes to each select statement to do this.
So for any record that has (region CR and eff_date >= 4/1/2016) the [Lag_Error] should read 'Lag Should be 1'
Can any help me with this? I tried to removed all the comments so I can fit everything and avoid cluster in the code as the comments weren't helpful in following the code. This may have caused syntax errors. For that, I apologize.
CREATE TABLE [usr].[#AFF_LAG]
(
[OP_NO] [varchar] NULL,
[PROV_NO] [varchar] (12) NOT NULL,
[AFF_NO] [varchar] (16) NOT NULL,
[EFF_DATE] [datetime] NULL,
[END_DATE] [datetime] NULL,
[LAG] [int] NULL,
[Lag_Error] [varchar] (50) NULL,
[STATUS] [varchar] (2) NULL,
[Prov_Name] [varchar] (60) NULL,
[REGION] [varchar] (2) NULL,
[SPEC_1] [varchar] (2) NULL,
[DESCRIPTION] [char] (72) NULL,
[IRS_NO] [varchar] (10) NULL)
ON [PRIMARY]
SELECT DISTINCT a.OP_NO,
a.PROV_NO,
a.AFF_NO,
a.eff_date,
a.end_Date,
a.lag,
'Lag should be 1' AS Lag_Error,
a.status,
b.First_Name + ' ' + b.Last_Name AS [Prov_Name],
Region,
a.Spec_1,
[description],
a.IRS_NO
INTO #templag2
FROM amisys.dbo.affiliation a
INNER JOIN amisys.dbo.provider b ON a.prov_no = b.prov_no
LEFT OUTER JOIN amisys.dbo.code_detail cd
ON a.spec_1 = cd.code_no AND prefix = 'sp'
WHERE a.end_date = '12/31/9999'
AND a.void = ' '
AND a.lag <> 1
AND b.prov_no <> '1000001395'
AND ( (a.spec_1 = 'ca' AND region = 'ni')
OR (a.spec_1 = 'FN' AND region = 'S1')
OR (a.spec_1 = 'FN' AND region = 'SD')
OR (a.spec_1 = 'FN' AND region = 'WC')
OR (a.spec_1 = 'FN' AND region = 'WJ')
OR a.prov_no = '100064886'
OR ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region = 'SI')
OR ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region = 'wj')
OR ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region IN ('GC','WC'))
OR a.irs_no = '274630894'
OR a.irs_no = '010940346'
OR ( a.prov_no IN ('1000009816',
'1000011878',
'436211363SC1',
'720204265TE1',
'720914714PE1',
'720975722CE1')
AND a.region = 'pn')
OR a.irs_no = '452829073'
OR a.prov_no IN ('0001',
'0002',
'0003',
'0004',
'0005',
'0006',
'0007',
'0008',
'0009',
'0010',
'0011')
AND NOT a.prov_no = '100067743')
SELECT DISTINCT a.OP_NO,
a.PROV_NO,
a.AFF_NO,
a.eff_date,
a.end_Date,
a.lag,
'Lag should be 30' AS Lag_Error,
a.status,
b.First_Name + ' ' + b.Last_Name AS [Prov_Name],
Region,
a.Spec_1,
[description],
a.IRS_NO
INTO #templag3
FROM amisys.dbo.affiliation a
INNER JOIN amisys.dbo.provider b ON a.prov_no = b.prov_no
LEFT OUTER JOIN amisys.dbo.code_detail cd
ON a.spec_1 = cd.code_no AND prefix = 'sp'
WHERE a.end_date = '12/31/9999'
AND a.void = ' '
AND b.prov_no <> '1000001395'
AND a.lag <> 30
AND NOT ( (a.spec_1 = 'ca' AND region = 'ni')
OR (a.spec_1 = 'ca' AND region = 'ui')
OR (a.spec_1 = 'ca' AND region = 'CI')
OR (a.spec_1 = 'ca' AND region = 'SI')
OR (a.spec_1 = 'ca' AND region = 'wj')
OR (a.spec_1 = 'CA' AND region = 'GC')
OR (a.spec_1 = 'CA' AND region = 'WC')
OR (a.spec_1 = 'OB' AND region = 'WJ'))
and not a.prov_no = '100064886'
AND NOT ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region = 'SI')
AND NOT ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region = 'wj')
AND NOT ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region IN ('GC','WC'))
AND NOT a.prov_no = '720478620ST1'
AND NOT a.irs_no IN ('201729674',
'205432631',
'205432782',
'271770321',
'720276883',
'720502505',
'721473968',
'204670876')
AND NOT ( a.prov_no IN ('1000009816',
'1000011878',
'436211363SC1',
'720204265TE1',
'720914714PE1',
'720975722CE1')
AND a.region = 'pn')
AND NOT a.irs_no = '452829073'
AND NOT a.prov_no IN ('0001',
'0002',
'0003',
'0004',
'0005',
'0006',
'0007',
'0008',
'0009',
'0010',
'0011')
AND NOT a.prov_no = '100067743'
SELECT DISTINCT a.OP_NO,
a.PROV_NO,
a.AFF_NO,
a.eff_date,
a.end_Date,
a.lag,
'Lag should be 25' AS Lag_Error,
a.status,
b.First_Name + ' ' + b.Last_Name AS [Prov_Name],
Region,
a.Spec_1,
[description],
a.IRS_NO
INTO #templag4
FROM amisys.dbo.affiliation a
INNER JOIN amisys.dbo.provider b ON a.prov_no = b.prov_no
LEFT OUTER JOIN amisys.dbo.code_detail cd
ON a.spec_1 = cd.code_no AND prefix = 'sp'
WHERE a.end_date = '12/31/9999'
AND a.void = ' '
AND a.lag <> 25
AND a.prov_no = '720478620ST1'
AND NOT a.prov_no = '100067743'
SELECT DISTINCT a.OP_NO,
a.PROV_NO,
a.AFF_NO,
a.eff_date,
a.end_Date,
a.lag,
'Lag should be 21' AS Lag_Error,
a.status,
b.First_Name + ' ' + b.Last_Name AS [Prov_Name],
Region,
a.Spec_1,
[description],
a.IRS_NO
INTO #templag5
FROM amisys.dbo.affiliation a
INNER JOIN amisys.dbo.provider b ON a.prov_no = b.prov_no
LEFT OUTER JOIN amisys.dbo.code_detail cd
ON a.spec_1 = cd.code_no AND prefix = 'sp'
WHERE a.end_date = '12/31/9999'
AND a.void = ' '
AND b.prov_no <> '1000001395'
AND a.lag <> '21'
AND ( a.irs_no IN ('201729674',
'205432631',
'205432782',
'271770321',
'720276883',
'720502505',
'721473968',
'204670876')
OR b.prov_no = '0012')
AND NOT ( (a.spec_1 = 'ca' AND region = 'ni')
OR (a.spec_1 = 'ca' AND region = 'ui')
OR (a.spec_1 = 'ca' AND region = 'CI')
OR (a.spec_1 = 'FN' AND region = 'WJ'))
and not a.prov_no = '100064886'
AND NOT ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region = 'SI')
AND NOT ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region = 'WJ')
AND NOT ( a.prov_no IN ('100070539', '721313430ST1')
AND a.region IN ('GC','WC'))
AND NOT a.prov_no = '720478620ST1'
AND NOT a.irs_no = '274630894'
AND NOT a.irs_no = '010940346'
AND NOT ( a.prov_no IN ('1000009816',
'1000011878',
'436211363SC1',
'720204265TE1',
'720914714PE1',
'720975722CE1')
AND a.region = 'pn')
AND NOT a.irs_no = '452829073'
AND NOT a.prov_no IN ('0001',
'0002',
'0003',
'0004',
'0005',
'0006',
'0007',
'0008',
'0009',
'0010',
'0011')
AND NOT a.prov_no = '100067743'
INSERT INTO #AFF_LAG
SELECT * FROM #templag2
UNION
SELECT * FROM #templag3
UNION
SELECT * FROM #templag4
UNION
SELECT * FROM #templag5
SELECT service_No,
CLAIM_NO,
Member_No,
P.Aff_No AS SVC_AFF_NO,
p.paid,
Amt_Charge,
Amt_Allow_P,
Amt_Copay,
Amt_Coin,
Amt_Risk,
Amt_pay,
Count_1,
Ex_1,
Location,
Procedure_1,
Modifier_1,
P.Eff_Date AS CLM_EFF_DATE,
Paid_Date,
A.[OP_NO],
A.[PROV_NO],
A.[AFF_NO],
A.[eff_DATE],
A.[END_DATE],
A.[LAG],
A.[Lag_Error],
A.[STATUS],
A.[Prov_Name],
A.[REGION],
A.[SPEC_1],
A.[DESCRIPTION],
A.[IRS_NO]
FROM #aff_lag AS A
LEFT OUTER JOIN AMISYS.dbo.service AS P
ON A.AFF_NO = P.AFF_NO AND p.paid IN ('NN', '*N')
GROUP BY service_No,
CLAIM_NO,
Member_No,
P.Aff_No,
P.Paid,
Amt_Charge,
Amt_Allow_P,
Amt_Copay,
Amt_Coin,
Amt_Risk,
Amt_pay,
Count_1,
Ex_1,
Location,
Procedure_1,
Modifier_1,
P.Eff_Date,
Paid_Date,
A.[OP_NO],
A.[PROV_NO],
A.[AFF_NO],
A.[eff_DATE],
A.[END_DATE],
A.[LAG],
A.[Lag_Error],
A.[STATUS],
A.[Prov_Name],
A.[REGION],
A.[SPEC_1],
A.[DESCRIPTION],
A.[IRS_NO]
DROP TABLE #AFF_LAG
DROP TABLE #templag2
DROP TABLE #templag3
DROP TABLE #templag4
DROP TABLE #templag5
Upvotes: 0
Views: 77
Answers (1)
Stainy
Reputation: 450
I'd recommend you talk to your boss and advocate strongly to refactor this, however, it sounds like she may not be receptive to the idea.
If you must change all the selects, then change the WHERE in the first statement (Lag should be 1) to check for the new requirement or all the other requirements. This will take care of any row where region = 'CR' and eff_date >= 4/1/2016:
WHERE (region = 'CR' and eff_date >= '4/1/2016')
OR
(
... --Rest of where clause goes here
)
Change the other selects to ignore rows that meet the new requirement, since the first statement will have returned all of them:
WHERE NOT(region = 'CR' and eff_date >= '4/1/2016')
AND ... --Rest of where clause goes here
Upvotes: 1
Related Questions
- How to add a column with a default value to an existing table in SQL Server?
- Find all tables containing column with specified name
- How to query MongoDB with "like"
- How can I do an UPDATE statement with JOIN in SQL Server?
- How can I delete using INNER JOIN with SQL Server?
- oracle sql count value for weeks until a specific column changes
- firebird 2.5 selecting records with date closest to today
- How to get mutiple results from group by in postgres