Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
werhardt
Constraint Violating Yak Guru
270 Posts |
 Posted - 2008-07-29 : 22:42:49
|
| I have this proceedured code that I created which was working great, now it is giving me a message. What does this message mean?Msg 8152, Level 16, State 14, Line 155String or binary data would be truncated.Here is my procedure below. Thanks.USE [Impact_PROD]GO/****** Object: StoredProcedure [dbo].[proc_Build_MonthEndProvidersREVISED] Script Date: 07/29/2008 14:10:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[proc_Build_MonthEndProvidersREVISED]AS/**Modified 04/30/2007 By Mike SalernoAdded Step 0 to determine if there is data available for import BEFORE dropping existing tablesIf there is no data - this procedure will abort and leave existing data in the provider finder**//**************************************************************************JGHawley 2006.10.30Build a process to mimic the NetworX dit_DHS_MonthEndProviders_DGH process---------------------------------------------------------------------------proc_Build_MonthEndProviders DROP TABLE dhs_MonthEndProvidersREVISEDDROP TABLE dhs_propsp_helptext dhs_MonthEndProvidersREVISEDSELECT MAX(clm_NewDt) FROM clmSELECT DISTINCT dgh_prop_disc FROM DHS_MonthEndProvidersSELECT phone, * FROM DHS_MonthEndProvidersWHERE PROVIDERTYPECODE = 'HOSP' --[Last Name] = 'morris' AND [First Name] = 'james' PROVIDERTYPECODE = 'ANC' AND Doctorid IN ('A6238','A6870','A7010050')ORDER BY PROVIDERNAMESELECT pro_id1, pro_spec1, pro_spec2, pro_spec3, * FROM DHS_MonthEndProviders WHERE ProviderTypeCode = 'HOSP' ORDER BY pro_spec1SELECT pro_id1, pro_spec1, pro_spec2, pro_spec3, pro_phone, * FROM pro WHERE pro_id1 = '00232913' ORDER BY pro_spec1**/DECLARE @StartDate datetimeSELECT @StartDate = GETDATE()PRINT '/** StartDate: **/'PRINT 'STEP ZERO - Determine if there is data to be imported! If not, then ABORT!'IF (SELECT COUNT(pro.pro_id1)FROM proJOIN prop dgh ON pro.pro_id1 = dgh.prop_id1 AND dgh.prop_prd = 'DGH'WHERE pro.pro_xtyp NOT IN ('BILL', 'EOTF') AND LEFT(dgh.prop_cont,2) <> 'NP') < 200000BEGINPRINT ''PRINT 'THERE IS NO DATA TO BE IMPORTED - ABORTING!'PRINT ''RETURNENDPRINT '/** Step 01: ' + CAST(@StartDate AS VARCHAR(25)) + ' CREATE TABLE dhs_MonthEndProvidersREVISED **/'if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[dhs_MonthEndProvidersREVISED]') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table [dbo].[dhs_MonthEndProvidersREVISED]CREATE TABLE dhs_MonthEndProvidersREVISED( ProviderID int NULL, [PROVIDERTYPECODE] varchar(40) NULL, [PROVIDERNAME] varchar(60) NULL, --PROVIDERCATEGORYCODE varchar(40) NULL, PROVIDERCATEGORYDESC varchar(40) NULL, [Update Date] varchar(40) NULL, [Effective Date] varchar(50) NULL, [Termination Date] varchar(50) NULL, [AHS] varchar(40) NULL, [DoctorID] varchar(40) NULL, [UPIN] varchar(40) NULL, [Last Name] varchar(40) NULL, [First Name] varchar(40) NULL, [Middle Name] varchar(40) NULL, [Ext] varchar(40) NULL, [Degree] varchar(40) NULL, [Degree2] varchar(40) NULL, [Social Security] varchar(40) NULL, [Mental Health] varchar(40) NULL, [Primary/Specialty] varchar(40) NULL, [MVA] varchar(40) NULL, [Workmen's Comp] varchar(40) NULL, [Spec 1] varchar(40) NULL, [Spec 2] varchar(40) NULL, [Spec 3] varchar(40) NULL, [Tax ID] varchar(40) NULL, [Practice] varchar(60) NULL, [IPA_PHO] varchar(40) NULL, [Address 1] varchar(55) NULL, [Address 2] varchar(55) NULL, [Address 3] varchar(55) NULL, [City] varchar(40) NULL, [Zip] varchar(40) NULL, [St] varchar(40) NULL, [Phone] varchar(40) NULL, [Fax #] varchar(40) NULL, [Billing Name] varchar(60) NULL, [Billing Address] varchar(55) NULL, [Billing City] varchar(40) NULL, [Billing Zip] varchar(40) NULL, [Billing St] varchar(40) NULL, [Billing Phone] varchar(40) NULL, [Hospital 1] varchar(50) NULL, [Hospital 2] varchar(50) NULL, [Hospital 3] varchar(50) NULL, [Hospital 4] varchar(50) NULL, [Hospital 5] varchar(50) NULL, [COUNTY] varchar(40) NULL, [Fee Schedule] varchar(40) NULL, [Workmen's Comp Fee Sch] varchar(40) NULL, [Signed Contract] varchar(40) NULL, [Comment] varchar(185) NULL, [email] varchar(255) NULL, [URL] varchar(255) NULL, [LATITUDE] varchar(40) NULL, [LONGITUDE] varchar(40) NULL, [DOB] varchar(50) NULL, [NPI] varchar(10) NULL, --Impact fields [pro_id1] varchar(8) NULL,-- SELECT pro_bref, pro_btag FROM pro ORDER BY pro_bref DESC [pro_bref] varchar(8) NULL, [pro_btag] varchar(1) NULL, [pro_pcs] varchar(8) NULL, [pro_sex] varchar(1) NULL, [pro_type] varchar(1) NULL, [pro_geo] varchar(6) NULL, [pro_altid] varchar(30) NULL, [pro_eff] varchar(50) NULL, [pro_trm] varchar(50) NULL, [pro_xtyp] varchar(4) NULL, [pro_chgdt] varchar(50) NULL, [pro_spec1] varchar(3) NULL, [pro_spec2] varchar(3) NULL, [pro_spec3] varchar(3) NULL, [pro_phone] varchar(24) NULL, dgh_prop_id1 varchar(8) NULL, dgh_prop_id2 varchar(4) NULL, dgh_prop_net varchar(5) NULL, dgh_prop_prd varchar(5) NULL, dgh_prop_eff varchar(50) NULL, dgh_prop_trm varchar(50) NULL, dgh_prop_pcp varchar(1) NULL, dgh_prop_prc varchar(6) NULL, dgh_prop_uac varchar(5) NULL, dgh_prop_cont varchar(8) NULL, dgh_prop_disc decimal(5,2) NULL, dgh_prop_chgdt varchar(50) NULL, mva_prop_id1 varchar(8) NULL, mva_prop_id2 varchar(4) NULL, mva_prop_net varchar(5) NULL, mva_prop_prd varchar(5) NULL, mva_prop_eff varchar(50) NULL, mva_prop_trm varchar(50) NULL, mva_prop_pcp varchar(1) NULL, mva_prop_prc varchar(6) NULL, mva_prop_uac varchar(5) NULL, mva_prop_disc decimal(5,2) NULL, mva_prop_chgdt varchar(50) NULL, wcp_prop_id1 varchar(8) NULL, wcp_prop_id2 varchar(4) NULL, wcp_prop_net varchar(5) NULL, wcp_prop_prd varchar(5) NULL, wcp_prop_eff varchar(50) NULL, wcp_prop_trm varchar(50) NULL, wcp_prop_pcp varchar(1) NULL, wcp_prop_prc varchar(6) NULL, wcp_prop_uac varchar(5) NULL, wcp_prop_disc decimal(5,2) NULL, wcp_prop_chgdt varchar(50) NULL,)------------------------------------------------------------------------------------------------------PRINT '/** Step 02: ' + CAST(GETDATE() AS VARCHAR(25)) + ' Import Provider Data into dhs_MonthEndProvidersREVISED **/'INSERT INTO dhs_MonthEndProvidersREVISED (pro_id1, [Update Date], [Effective Date], [Termination Date], [AHS], [DoctorID], [UPIN], [Last Name], [First Name], [Middle Name], [Ext], [Degree], [Degree2], [Social Security], [Primary/Specialty], [DOB], [NPI], [Spec 1], [Spec 2], [Spec 3], [Tax ID], [Practice], [Address 1], [Address 2], [Address 3], [City], [St], [Zip], [COUNTY], [Phone], [Fax #], [Signed Contract], [Comment], [email], [URL], [LATITUDE], [LONGITUDE], pro_sex, pro_type, pro_geo, pro_altid, pro_eff, pro_trm, pro_xtyp,pro_chgdt, pro_btag, pro_bref, pro_pcs, pro_spec1, pro_spec2, pro_spec3, pro_phone, dgh_prop_id1, dgh_prop_id2, dgh_prop_net, dgh_prop_prd, dgh_prop_eff, dgh_prop_trm, dgh_prop_pcp, dgh_prop_prc, dgh_prop_uac, dgh_prop_cont, dgh_prop_disc, dgh_prop_chgdt, [PROVIDERTYPECODE], PROVIDERNAME )SELECT pro.pro_id1,[Update Date]=Case CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)End,[Effective Date] =Case CONVERT(VARCHAR(10), (dgh.prop_eff), 101) WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_eff), 101) End,[Termination Date] =Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_trm), 101) End, [AHS] = CASE WHEN dgh.prop_trm is null THEN 'X' WHEN dgh.prop_trm = '03/23/1977' THEN 'X' WHEN dgh.prop_trm = '' THEN 'X' WHEN GetDate() BETWEEN dgh.prop_eff AND dgh.prop_trm THEN 'X' --WHEN GetDate() > dgh.prop_trm THEN 'X3' ELSE '' END, [DoctorID] = CASE dgh.prop_cont WHEN '' THEN RIGHT(dgh.prop_id1,6) + RIGHT(dgh.prop_id2,2) ELSE dgh.prop_cont END, --dgh.prop_cont AS [DoctorID], pro.pro_upin AS [UPIN], [Last Name] = CASE pro.pro_xtyp WHEN 'ANCI' THEN pro.pro_office WHEN 'HOSP' THEN pro.pro_office ELSE (left(pro.pro_lname,case when charindex(' ',replace(pro.pro_lname,',',' '))>0 then charindex(' ',replace(pro.pro_lname,',',' '))-1 else LEN(pro.pro_lname) end))--as LASTNAME,end, case when charindex(' ',replace(pro.pro_lname,',',' '))>0 then substring(pro.pro_lname,charindex(' ',replace(pro.pro_lname,',',' '))+1,LEN(pro.pro_lname)) else null end as Ext, --pro.pro_lname AS [Last Name], pro.pro_fname AS [First Name], pro.pro_minit AS [Middle Name], --'' AS [Ext], -- KZ confirmed this is not valued pro.pro_degree AS [Degree], '' AS [Degree2], -- KZ confirmed this is not valued pro.pro_ssn AS [Social Security], [Primary/Specialty] = CASE pro.pro_xtyp WHEN 'ANCI' THEN 'A' WHEN 'BILL' THEN 'X' WHEN 'EOTF' THEN 'X' WHEN 'HOSP' THEN 'H' WHEN 'PRIM' THEN 'S' --'P' KateZ: The only thing that should have a "P" is Family Practice, General Practice, Internal Medicine, & Pediatrics. WHEN 'PROV' THEN 'S' --'P' WHEN 'SPEC' THEN 'S' ELSE 'X' END, --pro.pro_type AS [Primary/Specialty], DOB =Case CONVERT(VARCHAR(10), (pro.pro_dob), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_dob), 101)End, pro.pro_npi AS NPI, pro.pro_spec1 AS [Spec 1], pro.pro_spec2 AS [Spec 2], pro.pro_spec3 AS [Spec 3], pro.pro_tax1 AS [Tax ID], pro.pro_office AS [Practice], pro.pro_addr1 AS [Address 1], pro.pro_addr2 AS [Address 2], '' AS [Address 3], -- KZ confirmed this is not valued pro.pro_city AS [City], pro.pro_state AS [St], pro.pro_zip AS [Zip], replace(pro_county,' COUNTY','') + ' COUNTY' AS 'County', --pro.pro_county AS [COUNTY], pro.pro_phone AS [Phone], pro.pro_fax AS [Fax #], '' AS [Signed Contract], -- KZ confirmed this is not valued '' AS [Comment], -- KZ confirmed this is not valued pro.pro_email AS [email], pro.pro_www AS [URL], pro.pro_lat AS [LATITUDE], pro.pro_lon AS [LONGITUDE], pro.pro_sex, pro.pro_type, pro.pro_geo, pro.pro_altid, [pro.pro_eff] =Case CONVERT(VARCHAR(10), (pro.pro_eff), 101) WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_eff), 101) End,[pro.pro_trm] =Case CONVERT(VARCHAR(10), (pro.pro_trm), 101) WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_trm), 101)End,pro.pro_xtyp, [pro.pro_chgdt] =Case CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)End,pro.pro_btag, pro.pro_bref, pro.pro_pcs, pro.pro_spec1, pro.pro_spec2, pro.pro_spec3, pro.pro_phone, dgh.prop_id1, dgh.prop_id2, dgh.prop_net, dgh.prop_prd, dgh.prop_pcp, dgh.prop_prc, dgh.prop_uac, dgh.prop_cont, dgh.prop_disc, [dgh.prop_eff] =Case CONVERT(VARCHAR(10), (dgh.prop_eff), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_eff), 101)End,[dgh.prop_trm] =Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_trm), 101)End,[dgh.prop_chgdt] =Case CONVERT(VARCHAR(10), (dgh.prop_chgdt), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_chgdt), 101)End, [PROVIDERTYPECODE] = CASE pro.pro_xtyp WHEN 'ANCI' THEN 'ANC' WHEN 'BILL' THEN 'XX1' WHEN 'EOTF' THEN 'XX2' WHEN 'HOSP' THEN 'HOSP' WHEN 'PRIM' THEN 'PHYS' WHEN 'PROV' THEN 'PHYS' WHEN 'SPEC' THEN 'PHYS' ELSE 'XX3' END, [PROVIDERNAME] = CASE pro.pro_xtyp WHEN 'ANCI' THEN pro.pro_office WHEN 'HOSP' THEN pro.pro_office ELSE pro.pro_lname ENDFROM proJOIN prop dgh ON pro.pro_id1 = dgh.prop_id1 AND dgh.prop_prd = 'DGH'join pcsa ON pro.pro_id1 = pcsa_id1 and pcsa_Stat NOT IN ('NOGOOD')WHERE pro.pro_xtyp NOT IN ('BILL', 'EOTF') AND LEFT(dgh.prop_cont,2) <> 'NP' --AND pro.pro_lname = 'morris' AND pro.pro_fname = 'james'------------------------------------------------------------------------------------------------------PRINT '/** Step 03: ' + CAST(GETDATE() AS VARCHAR(25)) + ' Modify dhs_MonthEndProvidersREVISED DocID **/'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[DoctorID] = 'H' + dhs_MonthEndProvidersREVISED.[DoctorID]WHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('HOSP') AND LEFT([DoctorID],1) <> 'H' AND LEN([DoctorID]) > 0UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[DoctorID] = 'A' + dhs_MonthEndProvidersREVISED.[DoctorID]WHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('ANC') AND LEFT([DoctorID],1) <> 'A' AND LEN([DoctorID]) > 0PRINT '/** Step 03a: Cleanup DocIDs **/'UPDATE dhs_MonthEndProvidersREVISED SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')UPDATE dhs_MonthEndProvidersREVISED SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')UPDATE dhs_MonthEndProvidersREVISED SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')UPDATE dhs_MonthEndProvidersREVISED SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')UPDATE dhs_MonthEndProvidersREVISED SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')/**SELECT DoctorID, RIGHT(DoctorID, 1), LEFT(DoctorID, LEN(DoctorID) - 1)FROM dhs_MonthEndProvidersREVISEDWHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')**/------------------------------------------------------------------------------------------------------PRINT '/** Step 04: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with Billing Info **/'UPDATE dhs_MonthEndProvidersREVISED SET [Billing Name] = bill.pro_office, [Billing Address] = bill.pro_addr1, [Billing City] = bill.pro_city, [Billing St] = bill.pro_state, [Billing Zip] = bill.pro_zip, [Billing Phone] = bill.pro_phone FROM pro AS bill WHERE dhs_MonthEndProvidersREVISED.pro_bref = bill.pro_id1 AND dhs_MonthEndProvidersREVISED.pro_btag = 'N' ------------------------------------------------------------------------------------------------------PRINT '/** Step 05: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with Hospital Affiliations **/'-- select * from pcshUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Hospital 1] = PCSH_1.pcsh_ofnaFROM pcsh AS pcsh_1 WHERE dhs_MonthEndProvidersREVISED.pro_pcs = pcsh_1.pcsh_id1 AND pcsh_1.pcsh_what = 'PRIVIL' AND pcsh_1.pcsh_id2 = '0001'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Hospital 2] = PCSH_2.pcsh_ofnaFROM pcsh AS pcsh_2 WHERE dhs_MonthEndProvidersREVISED.pro_pcs = pcsh_2.pcsh_id1 AND pcsh_2.pcsh_what = 'PRIVIL' AND pcsh_2.pcsh_id2 = '0002'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Hospital 3] = PCSH_3.pcsh_ofnaFROM pcsh AS pcsh_3 WHERE dhs_MonthEndProvidersREVISED.pro_pcs = pcsh_3.pcsh_id1 AND pcsh_3.pcsh_what = 'PRIVIL' AND pcsh_3.pcsh_id2 = '0003'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Hospital 4] = PCSH_4.pcsh_ofnaFROM pcsh AS pcsh_4 WHERE dhs_MonthEndProvidersREVISED.pro_pcs = pcsh_4.pcsh_id1 AND pcsh_4.pcsh_what = 'PRIVIL' AND pcsh_4.pcsh_id2 = '0004'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Hospital 5] = PCSH_5.pcsh_ofnaFROM pcsh AS pcsh_5WHERE dhs_MonthEndProvidersREVISED.pro_pcs = pcsh_5.pcsh_id1 AND pcsh_5.pcsh_what = 'PRIVIL' AND pcsh_5.pcsh_id2 = '0005'------------------------------------------------------------------------------------------------------PRINT '/** Step 06: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with SPEC **/'/**########Select * FROM valid WHERE val_type = '302' AND val_code = 'INT' SELECT pro_spec1, [Spec 1], DoctorID, pro_id1 FROM dhs_MonthEndProvidersREVISED WHERE [Spec 1] = '' ORDER BY pro_spec1########SELECT [Spec 2], val_desc, val_type, val_codeFROM validINNER JOIN dhs_MonthEndProvidersREVISEDON dhs_MonthEndProvidersREVISED.[Spec 2] = valid.val_codeAND valid.val_type = '302'WHERE dhs_MonthEndProvidersREVISED.[Spec 2] <> ''**/UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Spec 1] = valid.val_descFROM valid WHERE dhs_MonthEndProvidersREVISED.[Spec 1] = valid.val_code AND dhs_MonthEndProvidersREVISED.[Spec 1] <> '' AND valid.val_type = '302'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Spec 2] = valid.val_descFROM valid WHERE dhs_MonthEndProvidersREVISED.[Spec 2] = valid.val_code AND dhs_MonthEndProvidersREVISED.[Spec 2] <> '' AND valid.val_type = '302'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Spec 3] = valid.val_descFROM valid WHERE dhs_MonthEndProvidersREVISED.[Spec 3] = valid.val_code AND dhs_MonthEndProvidersREVISED.[Spec 3] <> '' AND valid.val_type = '302'------------------------------------------------------------------------------------------------------PRINT '/** Step 07: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with IPA **/'--SELECT * FROM valid WHERE val_code = 'GRE'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[IPA_PHO] = valid.val_descFROM prop LEFT JOIN valid ON prop.prop_sys = valid.val_code AND val_type = '367'WHERE dhs_MonthEndProvidersREVISED.dgh_prop_id1 = prop.prop_id1 AND valid.val_desc <> 'SYS AFFILIATE CODE' AND dhs_MonthEndProvidersREVISED.dgh_prop_id2 = prop.prop_id2-- UPDATE dhs_MonthEndProvidersREVISED SET [IPA_PHO] = NULL WHERE [IPA_PHO] = 'SYS AFFILIATE CODE'------------------------------------------------------------------------------------------------------PRINT '/** Step 08: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with Mental Health **/'-- ANC/HOSP do not reference this field-- X means no coverage, M = Mental HEalth, D = Dental-- SELECT * FROM valid WHERE valid.val_type = '302' ORDER BY val_code-- SELECT * FROM valid WHERE valid.val_type = '302' AND val_opt = 'M' ORDER BY val_codeUPDATE dhs_MonthEndProvidersREVISED SET [Mental Health] = valid.val_optFROM validWHERE valid.val_type = '302' AND dhs_MonthEndProvidersREVISED.pro_spec1 = valid.val_code AND valid.val_opt <> '3L' AND ([Mental Health] = '' OR [Mental Health] IS NULL)UPDATE dhs_MonthEndProvidersREVISED SET [Mental Health] = valid.val_optFROM validWHERE valid.val_type = '302' AND dhs_MonthEndProvidersREVISED.pro_spec2 = valid.val_code AND valid.val_opt <> '3L' AND ([Mental Health] = '' OR [Mental Health] IS NULL)UPDATE dhs_MonthEndProvidersREVISED SET [Mental Health] = valid.val_optFROM validWHERE valid.val_type = '302' AND dhs_MonthEndProvidersREVISED.pro_spec3 = valid.val_code AND valid.val_opt <> '3L' AND ([Mental Health] = '' OR [Mental Health] IS NULL)UPDATE dhs_MonthEndProvidersREVISED SET [Mental Health] = 'X'WHERE ([Mental Health] = '' OR [Mental Health] IS NULL)------------------------------------------------------------------------------------------------------PRINT '/** Step 08b: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with [Primary/Specialty] **/'--'P' KateZ: The only thing that should have a "P" is Family Practice, General Practice, Internal Medicine, & Pediatrics. UPDATE dhs_MonthEndProvidersREVISED SET [Primary/Specialty] = 'P'WHERE dhs_MonthEndProvidersREVISED.[Primary/Specialty] = 'S' AND dhs_MonthEndProvidersREVISED.[Spec 1] IN ('Family Practice', 'General Practice', 'Internal Medicine', 'Pediatrics')UPDATE dhs_MonthEndProvidersREVISED SET [Primary/Specialty] = 'P'WHERE dhs_MonthEndProvidersREVISED.[Primary/Specialty] = 'S' AND dhs_MonthEndProvidersREVISED.[Spec 2] IN ('Family Practice', 'General Practice', 'Internal Medicine', 'Pediatrics')UPDATE dhs_MonthEndProvidersREVISED SET [Primary/Specialty] = 'P'WHERE dhs_MonthEndProvidersREVISED.[Primary/Specialty] = 'S' AND dhs_MonthEndProvidersREVISED.[Spec 3] IN ('Family Practice', 'General Practice', 'Internal Medicine', 'Pediatrics')------------------------------------------------------------------------------------------------------PRINT '/** Step 09: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with DGH **/'-- SELECT top 50 * FROM prop --prop_pcp = A prop_prc=%CHRGS prop_disc = 80.00-- DocID = 322530 20%-- DocID = 5009790 20%-- DocID = 322530 20%-- DocID = 205150 20%-- SELECT * FROM prop WHERE prop_id1 IN ('00098380', '00098410')-- SELECT DISTINCT prop_uac FROM prop/** changed 20061115 JGHUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CASE dgh_prop_prc WHEN '%CHRGS' THEN CAST(dgh_prop_disc AS VARCHAR(6)) WHEN 'PHYFEE' AND (dgh_prop_uac <> '' OR dgh_prop_uac is null) THEN dgh_prop_uac ELSE 'X' ENDWHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('PHYS')**/-- %CHRGS always uses dgh_prop_disc/** removed 2006.11.20 JGHUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CAST(dgh_prop_disc AS VARCHAR(6))WHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('PHYS') AND dgh_prop_prc = '%CHRGS'-- PHYFEE is a medtalk that will use Fee Schedule if populated otherwise DIscountUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CASE dgh_prop_uac WHEN '' THEN dgh_prop_uac WHEN NULL THEN dgh_prop_uac ELSE CAST(dgh_prop_disc AS VARCHAR(6)) ENDWHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('PHYS') AND dgh_prop_prc = 'PHYFEE'-- Any other MedTalk gets an 'X'UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = 'X'WHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('PHYS') AND dgh_prop_prc NOT IN ('PHYFEE', '%CHRGS')---- SELECT distinct dgh_prop_uac, dgh_prop_prc, dgh_prop_disc FROM dhs_MonthEndProvidersREVISED WHERE PROVIDERTYPECODE IN ('PHYS')UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CASE dgh_prop_prc WHEN '%CHRGS' THEN CAST(dgh_prop_disc AS VARCHAR(6)) WHEN 'PHYFEE' THEN dgh_prop_uac ELSE 'X' ENDWHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('ANC')UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CASE dgh_prop_prc WHEN '%CHRGS' THEN CAST(dgh_prop_disc AS VARCHAR(6)) WHEN 'PHYFEE' THEN dgh_prop_uac ELSE 'X' ENDWHERE dhs_MonthEndProvidersREVISED.PROVIDERTYPECODE IN ('HOSP')**/-- new 2006.11.20UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CASE LEFT(dgh_prop_prc, 6) WHEN '%CHRGS' THEN CAST(dgh_prop_disc AS VARCHAR(6)) WHEN 'PHYFEE' THEN dgh_prop_uac ELSE 'X' END-- handle PHYFEE but dgh_prop_discUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CAST(dgh_prop_disc AS VARCHAR(6))-- WHERE dgh_prop_prc = 'PHYFEE' AND ([Fee Schedule] <> '' OR [Fee Schedule] is null)WHERE dgh_prop_prc = 'PHYFEE' AND [FEE SCHEDULE] = ''-- SELECT DISTINCT [Fee Schedule] FROM dhs_MonthEndProvidersREVISEDUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Fee Schedule] = CASE [Fee Schedule] WHEN 'A' THEN 'A' WHEN 'BCD' THEN 'B' WHEN 'E' THEN 'E' WHEN 'F' THEN 'F' WHEN 'G' THEN 'G' WHEN 'H' THEN 'H' WHEN 'I' THEN 'I' WHEN 'J' THEN 'J' WHEN 'K' THEN 'K' WHEN 'L' THEN 'L' WHEN 'M' THEN 'M' WHEN 'N' THEN 'N' WHEN 'X' THEN 'X' --WHEN '0.00' THEN '100' removed 2006.11.21 per KateZ WHEN '10.00' THEN '90' WHEN '15.00' THEN '85' WHEN '25.00' THEN '75' WHEN '30.00' THEN '70' WHEN '35.00' THEN '65' WHEN '40.00' THEN '60' WHEN '50.00' THEN '50' WHEN '55.00' THEN '45' WHEN '58.00' THEN '42' WHEN '60.00' THEN '40' WHEN '62.00' THEN '38' WHEN '64.00' THEN '36' WHEN '65.00' THEN '35' WHEN '67.00' THEN '33' WHEN '70.00' THEN '30' WHEN '75.00' THEN '25' WHEN '77.00' THEN '23' WHEN '78.00' THEN '22' WHEN '80.00' THEN '20' WHEN '83.00' THEN '17' WHEN '85.00' THEN '15' WHEN '87.00' THEN '13' WHEN '88.00' THEN '12' WHEN '90.00' THEN '10' WHEN '92.00' THEN '08' WHEN '93.00' THEN '07' WHEN '95.00' THEN '05' WHEN '96.00' THEN '04' WHEN '97.00' THEN '03' WHEN '98.00' THEN '02' WHEN '99.00' THEN '01' WHEN '100.00' THEN '00' ELSE 'ZZ' END--WHERE dgh_prop_prc = '%CHRGS'------------------------------------------------------------------------------------------------------PRINT '/** Step 10: ' + CAST(GETDATE() AS VARCHAR(25)) + ' CREATE dhs_prop for processing with MVA/WCP **/'if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[dhs_Prop]') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table [dbo].[dhs_Prop]--CREATE TABLE dhs_Prop( prop_id1 varchar(8) NULL, prop_id2 varchar(4) NULL, prop_net varchar(5) NULL, prop_prd varchar(5) NULL, prop_eff datetime NULL, prop_trm datetime NULL, prop_pcp varchar(1) NULL, prop_prc varchar(6) NULL, prop_uac varchar(5) NULL, prop_disc decimal(5,2) NULL, prop_chgdt datetime NULL)--INSERT INTO dhs_Prop ( prop_id1, prop_id2, prop_net, prop_prd, prop_eff, prop_trm, prop_pcp, prop_prc, prop_uac, prop_disc, prop_chgdt )SELECT prop_id1, prop_id2, prop_net, prop_prd, prop_eff, CASE prop_trm WHEN '' THEN '12/31/9999' WHEN '3/23/1977' THEN '12/31/9999' ELSE prop_trm END, prop_pcp, prop_prc, prop_uac, prop_disc, prop_chgdtFROM prop-- 2006.11.20 SETS UP mva AND wc PROCESSUPDATE dhs_MonthEndProvidersREVISED SET dgh_prop_trm = '12/31/9999' WHERE dgh_prop_trm = '' OR dgh_prop_trm IS NULLOR dgh_prop_trm = '3/23/1977' ------------------------------------------------------------------------------------------------------PRINT '/** Step 11: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with MVA **/'UPDATE dhs_MonthEndProvidersREVISED SET [mva_prop_id2] = ( SELECT TOP 1 prop_id2 FROM dhs_prop WHERE dhs_MonthEndProvidersREVISED.pro_id1 = dhs_prop.prop_id1 -- AND dhs_MonthEndProvidersREVISED.dgh_prop_net = dhs_prop.prop_net -- same network product AND dhs_prop.prop_prd = 'MVA' -- limit to MVA products AND dhs_prop.prop_eff BETWEEN dhs_MonthEndProvidersREVISED.dgh_prop_eff AND dhs_MonthEndProvidersREVISED.dgh_prop_trm ORDER BY prop_trm DESC )--UPDATE dhs_MonthEndProvidersREVISED SET mva_prop_id1 = dhs_MonthEndProvidersREVISED.pro_id1, mva_prop_net = prop_net, mva_prop_prd = prop_prd, mva_prop_eff = prop_eff, mva_prop_trm = prop_trm, mva_prop_pcp = prop_pcp, mva_prop_prc = prop_prc, mva_prop_uac = prop_uac, mva_prop_disc = prop_disc, mva_prop_chgdt = prop_chgdtFROM dhs_propWHERE dhs_MonthEndProvidersREVISED.pro_id1 = dhs_prop.prop_id1 AND dhs_MonthEndProvidersREVISED.mva_prop_id2 = dhs_prop.prop_id2--UPDATE dhs_MonthEndProvidersREVISED SET [MVA] = ( CASE WHEN mva_prop_id2 = '' THEN 'N' WHEN mva_prop_id2 IS NULL THEN 'N' WHEN mva_prop_id2 <> '' AND mva_prop_trm = '' THEN 'Y' WHEN mva_prop_id2 <> '' AND mva_prop_trm IS NULL THEN 'Y' WHEN mva_prop_id2 <> '' AND mva_prop_trm = '12/31/9999' THEN 'Y' WHEN mva_prop_id2 IS NOT NULL AND mva_prop_trm = '' THEN 'Y' WHEN mva_prop_id2 IS NOT NULL AND mva_prop_trm IS NULL THEN 'Y' WHEN mva_prop_id2 IS NOT NULL AND mva_prop_trm = '12/31/9999' THEN 'Y' ELSE CAST(Month([mva_prop_trm]) AS VARCHAR(2)) + '/' + CAST(Day([mva_prop_trm]) AS VARCHAR(2)) + '/' + RIGHT(CAST(Year([mva_prop_trm]) AS VARCHAR(4)),2)--CAST(mva_prop_trm AS VARCHAR(12)) END )-- SELECT MVA, mva_prop_id2, mva_prop_trm FROM dhs_MonthEndProvidersREVISED/** removed 2006.11.07 JGHUPDATE dhs_MonthEndProvidersREVISED SET [MVA] = ( CASE WHEN CAST(mva_prop_trm AS DateTime) <> CAST('12/31/9999' AS DateTime) THEN CAST(Month([MVA]) AS VARCHAR(2)) + '/' + CAST(Day([MVA]) AS VARCHAR(2)) + '/' + RIGHT(CAST(Year([MVA]) AS VARCHAR(4)),2) ELSE 'Y' END )WHERE IsDate([MVA]) = 1**/------------------------------------------------------------------------------------------------------PRINT '/** Step 12: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with [Workmen s Comp] **/'UPDATE dhs_MonthEndProvidersREVISED SET [wcp_prop_id2] = ( SELECT TOP 1 prop_id2 FROM dhs_prop WHERE dhs_MonthEndProvidersREVISED.pro_id1 = dhs_prop.prop_id1 -- AND dhs_MonthEndProvidersREVISED.dgh_prop_net = dhs_prop.prop_net -- same network product AND dhs_prop.prop_prd = 'WCP' -- limit to WCP products AND dhs_prop.prop_eff BETWEEN dhs_MonthEndProvidersREVISED.dgh_prop_eff AND dhs_MonthEndProvidersREVISED.dgh_prop_trm ORDER BY prop_trm DESC )--UPDATE dhs_MonthEndProvidersREVISED SET wcp_prop_id1 = dhs_MonthEndProvidersREVISED.pro_id1, wcp_prop_net = prop_net, wcp_prop_prd = prop_prd, wcp_prop_eff = prop_eff, wcp_prop_trm = prop_trm, wcp_prop_pcp = prop_pcp, wcp_prop_prc = prop_prc, wcp_prop_uac = prop_uac, wcp_prop_disc = prop_disc, wcp_prop_chgdt = prop_chgdtFROM dhs_propWHERE dhs_MonthEndProvidersREVISED.pro_id1 = dhs_prop.prop_id1 AND dhs_MonthEndProvidersREVISED.wcp_prop_id2 = dhs_prop.prop_id2--UPDATE dhs_MonthEndProvidersREVISED SET [Workmen's Comp] = ( CASE WHEN wcp_prop_id2 = '' THEN 'N' WHEN wcp_prop_id2 IS NULL THEN 'N' WHEN wcp_prop_id2 <> '' AND wcp_prop_trm = '' THEN 'Y' WHEN wcp_prop_id2 <> '' AND wcp_prop_trm IS NULL THEN 'Y' WHEN wcp_prop_id2 <> '' AND wcp_prop_trm = '12/31/9999' THEN 'Y' WHEN wcp_prop_id2 IS NOT NULL AND wcp_prop_trm = '' THEN 'Y' WHEN wcp_prop_id2 IS NOT NULL AND wcp_prop_trm IS NULL THEN 'Y' WHEN wcp_prop_id2 IS NOT NULL AND wcp_prop_trm = '12/31/9999' THEN 'Y' ELSE CAST(Month([wcp_prop_trm]) AS VARCHAR(2)) + '/' + CAST(Day([wcp_prop_trm]) AS VARCHAR(2)) + '/' + RIGHT(CAST(Year([wcp_prop_trm]) AS VARCHAR(4)),2) END )-- SELECT MVA, COUNt(MVA) FROM dhs_MonthEndProvidersREVISED GROUp BY MVA ORDER BY MVA------------------------------------------------------------------------------------------------------PRINT '/** Step 13: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with [Workmens Comp Fee Sch] **/'-- [Workmen's Comp Fee Sch]-- [Workmen's Comp]UPDATE dhs_MonthEndProvidersREVISED SET [Workmen's Comp Fee Sch] = '' WHERE [Workmen's Comp] = 'N' -- New 2006.11.20UPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Workmen's Comp Fee Sch] = CASE LEFT(wcp_prop_prc, 6) WHEN '%CHRGS' THEN CAST(wcp_prop_disc AS VARCHAR(6)) WHEN 'PHYFEE' THEN wcp_prop_uac ELSE 'X' ENDWHERE [Workmen's Comp] <> 'N'-- handle PHYFEE but dgh_prop_discUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Workmen's Comp Fee Sch] = CAST(wcp_prop_disc AS VARCHAR(6))WHERE wcp_prop_prc = 'PHYFEE' AND [Workmen's Comp] <> 'N' AND [Workmen's Comp Fee Sch] = ''-- SELECT DISTINCT [Fee Schedule] FROM dhs_MonthEndProvidersREVISEDUPDATE dhs_MonthEndProvidersREVISED SET dhs_MonthEndProvidersREVISED.[Workmen's Comp Fee Sch] = CASE [Workmen's Comp Fee Sch] WHEN 'A' THEN 'A' WHEN 'BCD' THEN 'B' WHEN 'E' THEN 'E' WHEN 'F' THEN 'F' WHEN 'G' THEN 'G' WHEN 'H' THEN 'H' WHEN 'I' THEN 'I' WHEN 'J' THEN 'J' WHEN 'K' THEN 'K' WHEN 'L' THEN 'L' WHEN 'M' THEN 'M' WHEN 'N' THEN 'N' WHEN 'X' THEN 'X' --WHEN '0.00' THEN '100' removed 2006.11.21 per KateZ WHEN '10.00' THEN '90' WHEN '15.00' THEN '85' WHEN '25.00' THEN '75' WHEN '30.00' THEN '70' WHEN '35.00' THEN '65' WHEN '40.00' THEN '60' WHEN '50.00' THEN '50' WHEN '55.00' THEN '45' WHEN '58.00' THEN '42' WHEN '60.00' THEN '40' WHEN '62.00' THEN '38' WHEN '64.00' THEN '36' WHEN '65.00' THEN '35' WHEN '67.00' THEN '33' WHEN '70.00' THEN '30' WHEN '75.00' THEN '25' WHEN '77.00' THEN '23' WHEN '78.00' THEN '22' WHEN '80.00' THEN '20' WHEN '83.00' THEN '17' WHEN '85.00' THEN '15' WHEN '87.00' THEN '13' WHEN '88.00' THEN '12' WHEN '90.00' THEN '10' WHEN '92.00' THEN '08' WHEN '93.00' THEN '07' WHEN '95.00' THEN '05' WHEN '96.00' THEN '04' WHEN '97.00' THEN '03' WHEN '98.00' THEN '02' WHEN '99.00' THEN '01' WHEN '100.00' THEN '00' ELSE 'ZZ' ENDWHERE [Workmen's Comp] <> 'N'------------------------------------------------------------------------------------------------------PRINT '/** Step 15: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with Modalities **/'/**SELECT prou_type, prou_descFROM prouGROUP BY prou_type, prou_descHAVING LEFT(prou_type, 1) = '*'prou_type prou_desc --------- -------------------- *XR X-RAY*ECHO ECHOCARDIOGRAM*NM NUCLEAR MEDICINE*STRES STRESS TEST*CT COMPUTERIZED TOMOGRA*FLU FLUOROSCOPY*DEXA DUAL ENERGY X-RAY AB*PET POSITRON EMISSION TO*MRA MAGNETIC RESONANCE A*BD BONE DENSITY TEST*MRI MAGNETIC RESONANCE I*SPECT SPECTROSCOPY*OMRI OPEN MAGNETIC RESONA*MAM MAMMOGRAPHY*US ULTRASOUND(15 row(s) affected)select top 20 *FROM prou--WHERE prou_type = '*xr'WHERE prou_id = '00009885'SELECT max(prou_line) from prou - 11**/UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 1UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 2UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 3UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 4UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 5UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 6UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 7UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 8UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 9UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 10UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 11UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = [COMMENT] + [prou_type]FROM prouWHERE dhs_MonthEndProvidersREVISED.pro_id1 = prou.prou_idAND LEFT(prou_type, 1) = '*'AND prou_line = 12UPDATE dhs_MonthEndProvidersREVISEDSET COMMENT = REPLACE([COMMENT], '*', ' ') ------------------------------------------------------------------------------------------------------PRINT '/** Step 16: ' + CAST(GETDATE() AS VARCHAR(25)) + ' UPDATE dhs_MonthEndProvidersREVISED with clean NPI **/'/**SELECT pro_id1, NPI, LEN(NPI)FROM DHS_MonthEndProviders WHERE IsNumeric(NPI)=1ORDER BY LEN(NPI) DESC13,037 w/ 10 len**/UPDATE DHS_MonthEndProvidersSET NPI = ''WHERE IsNumeric(NPI)<>1 OR LEN(NPI) < 10 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-07-29 : 23:35:15
|
| It usually means you are copying one column/field to another column/field which has a different definition which is smallereg declare @char_13 nvarchar(13)create table test(char_13 nvarchar(13), char_5 nvarchar(5))select @char_13 = '13 Characters'insert into test (char_13, char_5) values (@char_13,@char_13)select * from test |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-30 : 00:01:04
|
| Make sure your table fields have enough length to accomodate the data you're trying to insert into them. The best method would be to declare fields as having the same length as that of source table from which you're trying to insert. |
|
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2008-07-30 : 10:36:41
|
Is there an easier way of figuring this out? I have been working on this all morning. I am not sure what I did. The only thing that I did different this time is the Last name formula and the date to case statements, in the source field this was a datetime, we changed it to a varchar which did work. The last name was 40 but up upped that to 60SELECT pro.pro_id1,[Update Date]=Case CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)End,[Effective Date] =Case CONVERT(VARCHAR(10), (dgh.prop_eff), 101) WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_eff), 101) End,[Termination Date] =Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_trm), 101) End, [AHS] = CASE WHEN dgh.prop_trm is null THEN 'X' WHEN dgh.prop_trm = '03/23/1977' THEN 'X' WHEN dgh.prop_trm = '' THEN 'X' WHEN GetDate() BETWEEN dgh.prop_eff AND dgh.prop_trm THEN 'X' --WHEN GetDate() > dgh.prop_trm THEN 'X3' ELSE '' END, [DoctorID] = CASE dgh.prop_cont WHEN '' THEN RIGHT(dgh.prop_id1,6) + RIGHT(dgh.prop_id2,2) ELSE dgh.prop_cont END, --dgh.prop_cont AS [DoctorID], pro.pro_upin AS [UPIN], [Last Name] = CASE pro.pro_xtyp WHEN 'ANCI' THEN pro.pro_office WHEN 'HOSP' THEN pro.pro_office ELSE (left(pro.pro_lname,case when charindex(' ',replace(pro.pro_lname,',',' '))>0 then charindex(' ',replace(pro.pro_lname,',',' '))-1 else LEN(pro.pro_lname) end))--as LASTNAME,end, case when charindex(' ',replace(pro.pro_lname,',',' '))>0 then substring(pro.pro_lname,charindex(' ',replace(pro.pro_lname,',',' '))+1,LEN(pro.pro_lname)) else null end as Ext, --pro.pro_lname AS [Last Name], pro.pro_fname AS [First Name], pro.pro_minit AS [Middle Name], --'' AS [Ext], -- KZ confirmed this is not valued pro.pro_degree AS [Degree], '' AS [Degree2], -- KZ confirmed this is not valued pro.pro_ssn AS [Social Security], [Primary/Specialty] = CASE pro.pro_xtyp WHEN 'ANCI' THEN 'A' WHEN 'BILL' THEN 'X' WHEN 'EOTF' THEN 'X' WHEN 'HOSP' THEN 'H' WHEN 'PRIM' THEN 'S' --'P' KateZ: The only thing that should have a "P" is Family Practice, General Practice, Internal Medicine, & Pediatrics. WHEN 'PROV' THEN 'S' --'P' WHEN 'SPEC' THEN 'S' ELSE 'X' END, --pro.pro_type AS [Primary/Specialty], DOB =Case CONVERT(VARCHAR(10), (pro.pro_dob), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_dob), 101)End, pro.pro_npi AS NPI, pro.pro_spec1 AS [Spec 1], pro.pro_spec2 AS [Spec 2], pro.pro_spec3 AS [Spec 3], pro.pro_tax1 AS [Tax ID], pro.pro_office AS [Practice], pro.pro_addr1 AS [Address 1], pro.pro_addr2 AS [Address 2], '' AS [Address 3], -- KZ confirmed this is not valued pro.pro_city AS [City], pro.pro_state AS [St], pro.pro_zip AS [Zip], replace(pro_county,' COUNTY','') + ' COUNTY' AS 'County', --pro.pro_county AS [COUNTY], pro.pro_phone AS [Phone], pro.pro_fax AS [Fax #], '' AS [Signed Contract], -- KZ confirmed this is not valued '' AS [Comment], -- KZ confirmed this is not valued pro.pro_email AS [email], pro.pro_www AS [URL], pro.pro_lat AS [LATITUDE], pro.pro_lon AS [LONGITUDE], pro.pro_sex, pro.pro_type, pro.pro_geo, pro.pro_altid, [pro.pro_eff] =Case CONVERT(VARCHAR(10), (pro.pro_eff), 101) WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_eff), 101) End,[pro.pro_trm] =Case CONVERT(VARCHAR(10), (pro.pro_trm), 101) WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_trm), 101)End,pro.pro_xtyp, [pro.pro_chgdt] =Case CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (pro.pro_chgdt), 101)End,pro.pro_btag, pro.pro_bref, pro.pro_pcs, pro.pro_spec1, pro.pro_spec2, pro.pro_spec3, pro.pro_phone, dgh.prop_id1, dgh.prop_id2, dgh.prop_net, dgh.prop_prd, dgh.prop_pcp, dgh.prop_prc, dgh.prop_uac, dgh.prop_cont, dgh.prop_disc, [dgh.prop_eff] =Case CONVERT(VARCHAR(10), (dgh.prop_eff), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_eff), 101)End,[dgh.prop_trm] =Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_trm), 101)End,[dgh.prop_chgdt] =Case CONVERT(VARCHAR(10), (dgh.prop_chgdt), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_chgdt), 101)End, [PROVIDERTYPECODE] = CASE pro.pro_xtyp WHEN 'ANCI' THEN 'ANC' WHEN 'BILL' THEN 'XX1' WHEN 'EOTF' THEN 'XX2' WHEN 'HOSP' THEN 'HOSP' WHEN 'PRIM' THEN 'PHYS' WHEN 'PROV' THEN 'PHYS' WHEN 'SPEC' THEN 'PHYS' ELSE 'XX3' END, [PROVIDERNAME] = CASE pro.pro_xtyp WHEN 'ANCI' THEN pro.pro_office WHEN 'HOSP' THEN pro.pro_office ELSE pro.pro_lname ENDFROM proJOIN prop dgh ON pro.pro_id1 = dgh.prop_id1 AND dgh.prop_prd = 'DGH'join pcsa ON pro.pro_id1 = pcsa_id1 and pcsa_Stat NOT IN ('NOGOOD')WHERE pro.pro_xtyp NOT IN ('BILL', 'EOTF') AND LEFT(dgh.prop_cont,2) <> 'NP' quote:
Originally posted by visakh16
Make sure your table fields have enough length to accomodate the data you're trying to insert into them. The best method would be to declare fields as having the same length as that of source table from which you're trying to insert.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-30 : 10:54:25
|
| Easier way is to concentrate on insert statements where you're trying to populate values to varchar fields. You need to make sure the values that you specify has lengths equal to or less than length of target field. Its very difficult for a third person to look into your code and spot the place especially without knowing your table structure details. |
|
|
|
|
|
|
|
|
Truncated Message