[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Help with a SQL Query

I need to update the 'Group' field name in my report if another value equals a specific value. If Status = E, I need Group to equal 'OCMC ' and if it does not, I need it to give me 5 blanks. Any assistance is appreciated.
SET NOCOUNT ON 

SELECT
	SPACE(3) AS [Client],
	REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [Run Date], 
	SPACE(1) AS [Activity Flag], 
	SPACE(2) AS [Filler],
	LEFT(ISNULL(pp.PatientId,'') + SPACE(9),9) AS [Employee Number],
	'01' AS [Member Sequence],
	SPACE(2) AS [Filler2],
	CASE 
		WHEN pp.SSN IS NULL THEN RIGHT(SPACE(9) + ISNULL(pp.SSN, '999999999'),9)
    	    	ELSE LEFT(pp.SSN , 9)
	END AS [Member Soc_Sec_No],
	LEFT(ISNULL(pp.Last,'') + SPACE(25),25) AS [Last Name], 
	LEFT(ISNULL(pp.First,'') + SPACE(20),20) AS [First Name],
	LEFT(ISNULL(pp.Middle,'') + SPACE(1),1) AS [Middle Initial],  
	LEFT(ISNULL(pp.Address1,'') + SPACE(25),25) AS [Address 1],
	LEFT(ISNULL(pp.Address2,'') + SPACE(25),25) AS [Address 2], 
	LEFT(ISNULL(pp.City,'') + SPACE(25),25) AS [City],
	LEFT(ISNULL(pp.State,'') + SPACE(2),2) AS [State],
	LEFT(ISNULL(pp.Zip,'') + SPACE(5),5) AS [Zip],
	CASE WHEN pp.Phone1 IS NULL Then SPACE(12) ELSE SUBSTRING(LTRIM(pp.Phone1),1,3)+'/' + SUBSTRING(LTRIM(pp.Phone1),4,3) + '-' + SUBSTRING(LTRIM(pp.Phone1),7,4) END AS [Phone],   
	pp.Sex AS [Sex],  
	'P' AS [Relation],
	'' AS [Status], 
	REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS [DOB], 
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodStartDate , 101) , '/' , '') AS [Effective Date],
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodEndDate , 101) , '/' , '') AS [Term Date],
	SPACE(4) AS [Filler3],
	SPACE(1) AS [COBRA],
	LEFT(ISNULL(pcp.DotID,'') + SPACE(9),9) AS [PCP],
	SPACE(4) AS [PCP Suffix],
	LEFT(ISNULL(pcp.Last,'') + SPACE(25),25) AS [PCP Last Name],
	LEFT(ISNULL(pcp.First,'') + SPACE(20),20) AS [PCP First Name], 
	SPACE(8) AS [PCP Effective Date], 
	SPACE(19) AS [Filler4],
	LEFT(ISNULL(pcp.FederalTaxID,'') + SPACE(12),12) AS [PCP Federal ID],
	'' AS [Group],
	SPACE(1) AS [Member wo Sub],
	SPACE(1) AS [PreEx flag],
	SPACE(8) AS [PreEx date] ,
	REPLACE(CONVERT(VARCHAR(10) , pp.created , 101) , '/' , '') AS [Date of Hire] 
INTO #Details	

FROM    
	cusMMEligibilityProfile p
	JOIN cusMMEligibilityApplicationInstance ai ON p.pkid = ai.fkMMEligibilityProfile
	JOIN cusMMEligibilityApplication app ON app.fkMMEligibilityApplicationInstance = ai.pkid
	JOIN cusMMEligibilityDecision d ON app.fkMMEligibilityDecision = d.pkid
	JOIN PatientProfile pp ON p.fkPatientProfileID = pp.PatientProfileId
	LEFT OUTER JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId

WHERE   
	(d.eligibilityPeriodStartDate IS NOT NULL AND d.eligibilityPeriodEndDate IS NOT NULL) 
	AND d.eligibilityPeriodStartDate >= ISNULL(NULL, '1/1/1900') AND d.eligibilityPeriodEndDate < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))
	AND p.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND ai.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND app.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkEligibilityDecisionID = ( SELECT TOP 1 MedListsid  FROM cusCRIMedLists WHERE TableName = 'EligDecision' AND Description = 'Granted' )	

SELECT 
	#Details.[Client] 
	, #Details.[Run Date] 
	, #Details.[Activity Flag]  
	, #Details.[Filler] 
	, #Details.[Employee Number]
	, #Details.[Member Sequence] 
	, #Details.[Filler2] 
	, #Details.[Member Soc_Sec_No]
	, #Details.[Last Name]  
	, #Details.[First Name] 
	, #Details.[Middle Initial]   
	, #Details.[Address 1] 
	, #Details.[Address 2]  
	, #Details.[City] 
	, #Details.[State] 
	, #Details.[Zip] 
	, #Details.[Phone]   
	, #Details.[Sex]   
	, #Details.[Relation] 
	--, #Details.[Status]   
	, CASE WHEN #Details.[Term Date]  <= '12/31/2010' THEN 'D' ELSE 'E' END AS [Status]
	, #Details.[DOB]  
	, #Details.[Effective Date] 
	, #Details.[Term Date] 
	, #Details.[Filler3] 
	, #Details.[COBRA]
	, #Details.[PCP] 
	, #Details.[PCP Suffix] 
	, #Details.[PCP Last Name] 
	, #Details.[PCP First Name]  
	, #Details.[PCP Effective Date] 
	, #Details.[Filler4] 
	, #Details.[PCP Federal ID] 
	, #Details.[Group]
	--, CASE WHEN #Details.[Status] = 'E' THEN 'OCMC ' ELSE '     ' END AS [Group]
	, #Details.[Member wo Sub]
	, #Details.[PreEx flag]
	, #Details.[PreEx date] 
	, #Details.[Date of Hire] 
	
FROM #Details  

DROP TABLE #Details

Open in new window

0
Jeff S
Asked:
Jeff S
  • 5
  • 2
1 Solution
 
Ephraim WangoyaCommented:
here
SET NOCOUNT ON 

SELECT
	SPACE(3) AS [Client],
	REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [Run Date], 
	SPACE(1) AS [Activity Flag], 
	SPACE(2) AS [Filler],
	LEFT(ISNULL(pp.PatientId,'') + SPACE(9),9) AS [Employee Number],
	'01' AS [Member Sequence],
	SPACE(2) AS [Filler2],
	CASE 
		WHEN pp.SSN IS NULL THEN RIGHT(SPACE(9) + ISNULL(pp.SSN, '999999999'),9)
    	    	ELSE LEFT(pp.SSN , 9)
	END AS [Member Soc_Sec_No],
	LEFT(ISNULL(pp.Last,'') + SPACE(25),25) AS [Last Name], 
	LEFT(ISNULL(pp.First,'') + SPACE(20),20) AS [First Name],
	LEFT(ISNULL(pp.Middle,'') + SPACE(1),1) AS [Middle Initial],  
	LEFT(ISNULL(pp.Address1,'') + SPACE(25),25) AS [Address 1],
	LEFT(ISNULL(pp.Address2,'') + SPACE(25),25) AS [Address 2], 
	LEFT(ISNULL(pp.City,'') + SPACE(25),25) AS [City],
	LEFT(ISNULL(pp.State,'') + SPACE(2),2) AS [State],
	LEFT(ISNULL(pp.Zip,'') + SPACE(5),5) AS [Zip],
	CASE WHEN pp.Phone1 IS NULL Then SPACE(12) ELSE SUBSTRING(LTRIM(pp.Phone1),1,3)+'/' + SUBSTRING(LTRIM(pp.Phone1),4,3) + '-' + SUBSTRING(LTRIM(pp.Phone1),7,4) END AS [Phone],   
	pp.Sex AS [Sex],  
	'P' AS [Relation],
	'' AS [Status], 
	REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS [DOB], 
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodStartDate , 101) , '/' , '') AS [Effective Date],
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodEndDate , 101) , '/' , '') AS [Term Date],
	SPACE(4) AS [Filler3],
	SPACE(1) AS [COBRA],
	LEFT(ISNULL(pcp.DotID,'') + SPACE(9),9) AS [PCP],
	SPACE(4) AS [PCP Suffix],
	LEFT(ISNULL(pcp.Last,'') + SPACE(25),25) AS [PCP Last Name],
	LEFT(ISNULL(pcp.First,'') + SPACE(20),20) AS [PCP First Name], 
	SPACE(8) AS [PCP Effective Date], 
	SPACE(19) AS [Filler4],
	LEFT(ISNULL(pcp.FederalTaxID,'') + SPACE(12),12) AS [PCP Federal ID],
	'' AS [Group],
	SPACE(1) AS [Member wo Sub],
	SPACE(1) AS [PreEx flag],
	SPACE(8) AS [PreEx date] ,
	REPLACE(CONVERT(VARCHAR(10) , pp.created , 101) , '/' , '') AS [Date of Hire] 
INTO #Details	

FROM    
	cusMMEligibilityProfile p
	JOIN cusMMEligibilityApplicationInstance ai ON p.pkid = ai.fkMMEligibilityProfile
	JOIN cusMMEligibilityApplication app ON app.fkMMEligibilityApplicationInstance = ai.pkid
	JOIN cusMMEligibilityDecision d ON app.fkMMEligibilityDecision = d.pkid
	JOIN PatientProfile pp ON p.fkPatientProfileID = pp.PatientProfileId
	LEFT OUTER JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId

WHERE   
	(d.eligibilityPeriodStartDate IS NOT NULL AND d.eligibilityPeriodEndDate IS NOT NULL) 
	AND d.eligibilityPeriodStartDate >= ISNULL(NULL, '1/1/1900') AND d.eligibilityPeriodEndDate < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))
	AND p.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND ai.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND app.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkEligibilityDecisionID = ( SELECT TOP 1 MedListsid  FROM cusCRIMedLists WHERE TableName = 'EligDecision' AND Description = 'Granted' )	

SELECT 
	#Details.[Client] 
	, #Details.[Run Date] 
	, #Details.[Activity Flag]  
	, #Details.[Filler] 
	, #Details.[Employee Number]
	, #Details.[Member Sequence] 
	, #Details.[Filler2] 
	, #Details.[Member Soc_Sec_No]
	, #Details.[Last Name]  
	, #Details.[First Name] 
	, #Details.[Middle Initial]   
	, #Details.[Address 1] 
	, #Details.[Address 2]  
	, #Details.[City] 
	, #Details.[State] 
	, #Details.[Zip] 
	, #Details.[Phone]   
	, #Details.[Sex]   
	, #Details.[Relation] 	
	, CASE WHEN #Details.[Status] = 'E' THEN 'OCMC ' ELSE SPACE(5) END [Status]
	, CASE WHEN #Details.[Term Date]  <= '12/31/2010' THEN 'D' ELSE 'E' END AS [Status]
	, #Details.[DOB]  
	, #Details.[Effective Date] 
	, #Details.[Term Date] 
	, #Details.[Filler3] 
	, #Details.[COBRA]
	, #Details.[PCP] 
	, #Details.[PCP Suffix] 
	, #Details.[PCP Last Name] 
	, #Details.[PCP First Name]  
	, #Details.[PCP Effective Date] 
	, #Details.[Filler4] 
	, #Details.[PCP Federal ID] 
	, #Details.[Group]
	--, CASE WHEN #Details.[Status] = 'E' THEN 'OCMC ' ELSE '     ' END AS [Group]
	, #Details.[Member wo Sub]
	, #Details.[PreEx flag]
	, #Details.[PreEx date] 
	, #Details.[Date of Hire] 
	
FROM #Details  

DROP TABLE #Details

Open in new window

0
 
Ephraim WangoyaCommented:
Sorry about that, a minute
0
 
Ephraim WangoyaCommented:
try
SET NOCOUNT ON 

SELECT
	SPACE(3) AS [Client],
	REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [Run Date], 
	SPACE(1) AS [Activity Flag], 
	SPACE(2) AS [Filler],
	LEFT(ISNULL(pp.PatientId,'') + SPACE(9),9) AS [Employee Number],
	'01' AS [Member Sequence],
	SPACE(2) AS [Filler2],
	CASE 
		WHEN pp.SSN IS NULL THEN RIGHT(SPACE(9) + ISNULL(pp.SSN, '999999999'),9)
    	    	ELSE LEFT(pp.SSN , 9)
	END AS [Member Soc_Sec_No],
	LEFT(ISNULL(pp.Last,'') + SPACE(25),25) AS [Last Name], 
	LEFT(ISNULL(pp.First,'') + SPACE(20),20) AS [First Name],
	LEFT(ISNULL(pp.Middle,'') + SPACE(1),1) AS [Middle Initial],  
	LEFT(ISNULL(pp.Address1,'') + SPACE(25),25) AS [Address 1],
	LEFT(ISNULL(pp.Address2,'') + SPACE(25),25) AS [Address 2], 
	LEFT(ISNULL(pp.City,'') + SPACE(25),25) AS [City],
	LEFT(ISNULL(pp.State,'') + SPACE(2),2) AS [State],
	LEFT(ISNULL(pp.Zip,'') + SPACE(5),5) AS [Zip],
	CASE WHEN pp.Phone1 IS NULL Then SPACE(12) ELSE SUBSTRING(LTRIM(pp.Phone1),1,3)+'/' + SUBSTRING(LTRIM(pp.Phone1),4,3) + '-' + SUBSTRING(LTRIM(pp.Phone1),7,4) END AS [Phone],   
	pp.Sex AS [Sex],  
	'P' AS [Relation],
	'' AS [Status], 
	REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS [DOB], 
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodStartDate , 101) , '/' , '') AS [Effective Date],
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodEndDate , 101) , '/' , '') AS [Term Date],
	SPACE(4) AS [Filler3],
	SPACE(1) AS [COBRA],
	LEFT(ISNULL(pcp.DotID,'') + SPACE(9),9) AS [PCP],
	SPACE(4) AS [PCP Suffix],
	LEFT(ISNULL(pcp.Last,'') + SPACE(25),25) AS [PCP Last Name],
	LEFT(ISNULL(pcp.First,'') + SPACE(20),20) AS [PCP First Name], 
	SPACE(8) AS [PCP Effective Date], 
	SPACE(19) AS [Filler4],
	LEFT(ISNULL(pcp.FederalTaxID,'') + SPACE(12),12) AS [PCP Federal ID],
	'' AS [Group],
	SPACE(1) AS [Member wo Sub],
	SPACE(1) AS [PreEx flag],
	SPACE(8) AS [PreEx date] ,
	REPLACE(CONVERT(VARCHAR(10) , pp.created , 101) , '/' , '') AS [Date of Hire] 
INTO #Details	

FROM    
	cusMMEligibilityProfile p
	JOIN cusMMEligibilityApplicationInstance ai ON p.pkid = ai.fkMMEligibilityProfile
	JOIN cusMMEligibilityApplication app ON app.fkMMEligibilityApplicationInstance = ai.pkid
	JOIN cusMMEligibilityDecision d ON app.fkMMEligibilityDecision = d.pkid
	JOIN PatientProfile pp ON p.fkPatientProfileID = pp.PatientProfileId
	LEFT OUTER JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId

WHERE   
	(d.eligibilityPeriodStartDate IS NOT NULL AND d.eligibilityPeriodEndDate IS NOT NULL) 
	AND d.eligibilityPeriodStartDate >= ISNULL(NULL, '1/1/1900') AND d.eligibilityPeriodEndDate < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))
	AND p.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND ai.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND app.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkEligibilityDecisionID = ( SELECT TOP 1 MedListsid  FROM cusCRIMedLists WHERE TableName = 'EligDecision' AND Description = 'Granted' )	

SELECT 
	#Details.[Client] 
	, #Details.[Run Date] 
	, #Details.[Activity Flag]  
	, #Details.[Filler] 
	, #Details.[Employee Number]
	, #Details.[Member Sequence] 
	, #Details.[Filler2] 
	, #Details.[Member Soc_Sec_No]
	, #Details.[Last Name]  
	, #Details.[First Name] 
	, #Details.[Middle Initial]   
	, #Details.[Address 1] 
	, #Details.[Address 2]  
	, #Details.[City] 
	, #Details.[State] 
	, #Details.[Zip] 
	, #Details.[Phone]   
	, #Details.[Sex]   
	, #Details.[Relation] 
	--, #Details.[Status]   
	, CASE WHEN #Details.[Term Date]  <= '12/31/2010' THEN 'D' ELSE 'E' END AS [Status]
	, #Details.[DOB]  
	, #Details.[Effective Date] 
	, #Details.[Term Date] 
	, #Details.[Filler3] 
	, #Details.[COBRA]
	, #Details.[PCP] 
	, #Details.[PCP Suffix] 
	, #Details.[PCP Last Name] 
	, #Details.[PCP First Name]  
	, #Details.[PCP Effective Date] 
	, #Details.[Filler4] 
	, #Details.[PCP Federal ID] 
	, #Details.[Group]
	, CASE WHEN #Details.[Status] = 'E' THEN 'OCMC ' ELSE SPACE(5) END [Group]
	, #Details.[Member wo Sub]
	, #Details.[PreEx flag]
	, #Details.[PreEx date] 
	, #Details.[Date of Hire] 
	
FROM #Details  

DROP TABLE #Details

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Jeff SAuthor Commented:
I am not getting the 'OCMC ' when I see some Statuses set to E. I wonder if its how I have it pulling in Group and Status in the first SELECT query?
0
 
Ephraim WangoyaCommented:

You are always returning an empty space as status in your first query

'' AS [Status]
0
 
Jeff SAuthor Commented:
ya, I thought that was the issue. How can I work past it?
0
 
Ephraim WangoyaCommented:
Seems you are calculating status from Term Date in which case you can just replace the Status with Term Date
SET NOCOUNT ON 

SELECT
	SPACE(3) AS [Client],
	REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [Run Date], 
	SPACE(1) AS [Activity Flag], 
	SPACE(2) AS [Filler],
	LEFT(ISNULL(pp.PatientId,'') + SPACE(9),9) AS [Employee Number],
	'01' AS [Member Sequence],
	SPACE(2) AS [Filler2],
	CASE 
		WHEN pp.SSN IS NULL THEN RIGHT(SPACE(9) + ISNULL(pp.SSN, '999999999'),9)
    	    	ELSE LEFT(pp.SSN , 9)
	END AS [Member Soc_Sec_No],
	LEFT(ISNULL(pp.Last,'') + SPACE(25),25) AS [Last Name], 
	LEFT(ISNULL(pp.First,'') + SPACE(20),20) AS [First Name],
	LEFT(ISNULL(pp.Middle,'') + SPACE(1),1) AS [Middle Initial],  
	LEFT(ISNULL(pp.Address1,'') + SPACE(25),25) AS [Address 1],
	LEFT(ISNULL(pp.Address2,'') + SPACE(25),25) AS [Address 2], 
	LEFT(ISNULL(pp.City,'') + SPACE(25),25) AS [City],
	LEFT(ISNULL(pp.State,'') + SPACE(2),2) AS [State],
	LEFT(ISNULL(pp.Zip,'') + SPACE(5),5) AS [Zip],
	CASE WHEN pp.Phone1 IS NULL Then SPACE(12) ELSE SUBSTRING(LTRIM(pp.Phone1),1,3)+'/' + SUBSTRING(LTRIM(pp.Phone1),4,3) + '-' + SUBSTRING(LTRIM(pp.Phone1),7,4) END AS [Phone],   
	pp.Sex AS [Sex],  
	'P' AS [Relation],
	'' AS [Status], 
	REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS [DOB], 
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodStartDate , 101) , '/' , '') AS [Effective Date],
	REPLACE(CONVERT(VARCHAR(10) , d.eligibilityPeriodEndDate , 101) , '/' , '') AS [Term Date],
	SPACE(4) AS [Filler3],
	SPACE(1) AS [COBRA],
	LEFT(ISNULL(pcp.DotID,'') + SPACE(9),9) AS [PCP],
	SPACE(4) AS [PCP Suffix],
	LEFT(ISNULL(pcp.Last,'') + SPACE(25),25) AS [PCP Last Name],
	LEFT(ISNULL(pcp.First,'') + SPACE(20),20) AS [PCP First Name], 
	SPACE(8) AS [PCP Effective Date], 
	SPACE(19) AS [Filler4],
	LEFT(ISNULL(pcp.FederalTaxID,'') + SPACE(12),12) AS [PCP Federal ID],
	'' AS [Group],
	SPACE(1) AS [Member wo Sub],
	SPACE(1) AS [PreEx flag],
	SPACE(8) AS [PreEx date] ,
	REPLACE(CONVERT(VARCHAR(10) , pp.created , 101) , '/' , '') AS [Date of Hire] 
INTO #Details	

FROM    
	cusMMEligibilityProfile p
	JOIN cusMMEligibilityApplicationInstance ai ON p.pkid = ai.fkMMEligibilityProfile
	JOIN cusMMEligibilityApplication app ON app.fkMMEligibilityApplicationInstance = ai.pkid
	JOIN cusMMEligibilityDecision d ON app.fkMMEligibilityDecision = d.pkid
	JOIN PatientProfile pp ON p.fkPatientProfileID = pp.PatientProfileId
	LEFT OUTER JOIN DoctorFacility pcp ON pp.PrimaryCareDoctorId = pcp.DoctorFacilityId

WHERE   
	(d.eligibilityPeriodStartDate IS NOT NULL AND d.eligibilityPeriodEndDate IS NOT NULL) 
	AND d.eligibilityPeriodStartDate >= ISNULL(NULL, '1/1/1900') AND d.eligibilityPeriodEndDate < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))
	AND p.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND ai.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND app.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkMMEligibilityRecordStatus = '3C09B544-1E92-48F4-9033-307F2204F26C'
	AND d.fkEligibilityDecisionID = ( SELECT TOP 1 MedListsid  FROM cusCRIMedLists WHERE TableName = 'EligDecision' AND Description = 'Granted' )	

SELECT 
	#Details.[Client] 
	, #Details.[Run Date] 
	, #Details.[Activity Flag]  
	, #Details.[Filler] 
	, #Details.[Employee Number]
	, #Details.[Member Sequence] 
	, #Details.[Filler2] 
	, #Details.[Member Soc_Sec_No]
	, #Details.[Last Name]  
	, #Details.[First Name] 
	, #Details.[Middle Initial]   
	, #Details.[Address 1] 
	, #Details.[Address 2]  
	, #Details.[City] 
	, #Details.[State] 
	, #Details.[Zip] 
	, #Details.[Phone]   
	, #Details.[Sex]   
	, #Details.[Relation] 
	--, #Details.[Status]   
	, CASE WHEN #Details.[Term Date]  <= '12/31/2010' THEN 'D' ELSE 'E' END AS [Status]
	, #Details.[DOB]  
	, #Details.[Effective Date] 
	, #Details.[Term Date] 
	, #Details.[Filler3] 
	, #Details.[COBRA]
	, #Details.[PCP] 
	, #Details.[PCP Suffix] 
	, #Details.[PCP Last Name] 
	, #Details.[PCP First Name]  
	, #Details.[PCP Effective Date] 
	, #Details.[Filler4] 
	, #Details.[PCP Federal ID] 
	, #Details.[Group]
	, CASE WHEN #Details.[Term Date] <= '12/31/2010' THEN SPACE(5) ELSE 'OCMC ' END AS [Group]
	, #Details.[Member wo Sub]
	, #Details.[PreEx flag]
	, #Details.[PreEx date] 
	, #Details.[Date of Hire] 
	
FROM #Details  

DROP TABLE #Details

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now