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

LVL 7
Jeff SAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.