This doesn't make sense to me - making me crazy - er

• krypto69

  SSChampion

  Points: 13613

  More actions

  December 9, 2025 at 12:36 pm

  #4697711

  Hi

  So I have the below select query that won't return results if I have it this way:

   

  --DOES NOT RETURN--
  AND EXISTS (SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx 
  WHERE (xx.SubscriberNumber = x.subscribernumber OR x.MBI = xx.mbi) 
  AND xx.LastDateProcessed>'05/31/2022')

  But will return results with this very small change:

  --DOES RETURN-- REPLACE ABOVE WITH BELOW
  AND EXISTS( SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx
  WHERE (x.SubscriberNumber = xx.subscribernumber OR MBI = xx.mbi)
  AND xx.LastDateProcessed>'05/31/2022')

  Here is the statement:

  DECLARE @FUTURE DATE
  SET @FUTURE = '12/01/2025'
  SELECT DISTINCT
     x.LastDateProcessed,
     FileType = 
  CONVERT(VARCHAR(20),CASE WHEN x.TradingPartnerID = 'HUMANA_MCR' AND s.EDIFileName LIKE '%REMBX%' THEN 'REMBX'
   WHEN x.TradingPartnerID = 'HUMANA_MCR' AND s.[Action] = 4 THEN 'Daily - Audit'
   WHEN x.TradingPartnerID = 'HUMANA_MCR' AND s.[Action] = 2 THEN 'Daily'
   WHEN x.ExceptionType = 'Audit Findings' THEN 'Audit Findings'
   ELSE IIF(s.[Action] = 2, 'Daily','Audit')
  END),
     Rejection = 
  IIF( x.Rejection LIKE '%non par pcp%' , 'Non Par PCP; has been on file since ' + CONVERT(VARCHAR,x.FirstDateProcessed,101),x.Rejection),
  
         x.BenefitContractId,
         x.BenefitBegin,
         x.BenefitEnd,
         x.MBI,
         x.SubscriberNumber,
         x.MemberLastName,
         x.MemberFirstName,
         x.MemberBirthday,
         x.MemberAddress1,
         x.MemberAddress2,
         x.MemberCity,
         x.MemberState,
         x.MemberZip,
         x.MemberPhone,
         Payor = COALESCE(x.employergroup_ud,'Benefit Contract needs to be added'),
         s.ProviderID,
         s.ProviderFirstName,
         s.ProviderLastName
  -- ###################################################################
  --                  REQUIRED FIELDS FOR TRACKER SYSTEM
  -- ###################################################################
  
  ,exceptionName = 
  CASE WHEN x.TradingPartnerId LIKE 'Humana%' AND x.Rejection NOT LIKE '%non par pcp%' THEN 'Humana Eligibility Exceptions'
  WHEN x.TradingPartnerId LIKE 'Humana%' AND x.Rejection LIKE '%non par pcp%' THEN 'Humana Non Par PCP Exceptions'
   WHEN x.TradingPartnerId = 'CP' THEN 'CarePlus Eligibility Exceptions'
   WHEN x.TradingPartnerId LIKE 'Wellcare%' THEN 'WellCare Eligibility Exceptions'
   WHEN x.TradingPartnerId = 'CENTENE' THEN 'Centene Eligibility Exceptions'
   WHEN x.TradingPartnerId = '821023772' THEN 'Devoted Eligibility Exceptions'
   WHEN x.TradingPartnerId = '810726576' THEN 'Solis Eligibility Exceptions'
   WHEN x.TradingPartnerId = 'ULTIMATE_H2962' THEN 'Ultimate Eligibility Exceptions'
   WHEN x.TradingPartnerId = 'ALHEALTH' THEN 'Alignment Eligibility Exceptions'
   WHEN x.TradingPartnerId = '824632870' THEN 'FloridaCare Eligibliity Exceptions'
   WHEN x.TradingPartnerId = '592015694' THEN 'Florida Blue Eligibliity Exceptions'
   WHEN x.TradingPartnerId = 'AMERIHEALTH' THEN 'Amerihealth Eligibliity Exceptions'
   WHEN x.TradingPartnerId = 'DRHCP' THEN 'DHCP Eligibliity Exceptions'
   ELSE 'Unknown Eligibility Exceptions'
  END 
  ,'EDI' exceptionTeam -- exceptionTeam indicates which team is primarily responsible for dealing with the exception. 
  
  --,RTRIM(x.TradingPartnerId) + '/' + x.SubscriberNumber + '/' + convert(VARCHAR(8),x.BenefitBegin,112) exceptionKey -- exceptionKey is the "primary key" of this type of exception. IE: Whatever value uniquely distinguishes this exception "row" from the others.
  ,CONVERT(VARCHAR(100), x.ID) exceptionKey
  
  
  -- temp table ##exceptionResults4ABC0D39002845698A493D9FB834C964 is the required name of the final temp table where the stored procedure will output all exception detail data.
  -- The tracker refresh process will look for this table when running the procedure in order to load the data into the system.
  --INTO ##exceptionResults4ABC0D39002845698A493D9FB834C964
  
  FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception x
  INNER JOIN Staging_Archive.dbo.staging_import s ON s.EnrollmentId = x.EnrollmentId
  WHERE  x.BenefitBegin < @Future
  AND x.Rejection NOT LIKE '%Future%'
  
  --DOES NOT RETURN--
  AND EXISTS (SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx 
  WHERE (xx.SubscriberNumber = x.subscribernumber OR x.MBI = xx.mbi) 
  AND xx.LastDateProcessed>'05/31/2022')
  
  --DOES RETURN-- REPLACE ABOVE WITH BELOW
  AND EXISTS( SELECT 1 FROM edi.dbo.PEC_RGT_EDI_834_Inbound_Exception xx
  WHERE (x.SubscriberNumber = xx.subscribernumber OR MBI = xx.mbi)
  AND xx.LastDateProcessed>'05/31/2022')
  
  
  AND x.LOB = 'Medicare'
  AND x.Rejection NOT LIKE '%non par pcp%'
  

  I've had a few other devs look at it and no one can figure it out. To make it even odder this statement was working fine for many years..

   

  • This topic was modified 1 hours, 48 minutes ago by krypto69.