Edit Question - KnowledgeHub

Title Body

I have a SQL Server stored procedure that doesn't give me all the results when I add in the union and the second half. The first half will give me all 6 results, using the union, I only get 5. There are 3 patients with their own [chart number], each have 2 items that should be displayed. if the [CPTCode] is the same for both entries of a patient, only one of the two entries show up when i add the union (it gives me all 6 with the same [cptcode] without the union). The second half isn't actually pulling any information with what i'm doing right now, but it is needed. select /*Patients with chart numbers*/ B1.[chart Number], '0' as newPatient, isnull(Lytec.[Last Name],'') as [Last Name], isnull(Lytec.[First Name],'') as [First Name], isnull(Address.[Name], Lytec.[Facility Code]) as [Facility], isnull(B1.DOS,'') as [DOS], isnull(Ins.[Name], Lytec.[Primary Code]) as [Primary Code], isnull(B1.[CPTCode],'') as cptCode, isnull(B1.[Units],'') as [Units], isnull(B1.[Modifiers],'') as [Modifiers], isnull(B1.[cptCodeOther],'') as [cptCodeOther], isnull(B1.[cptCode2],'') as cptCode2, isnull(B1.[Units],'') as [Units2], isnull(B1.[Modifiers2],'') as [Modifiers2], isnull(B1.[cptCodeOther2],'') as [cptCodeOther2], 'name' as ProviderName from [sandboxmr].dbo.patient Lytec left join [dbo].[Billing] B1 on B1.[Chart Number] = Lytec.[Chart Number] left join [sandboxmr].dbo.[Address] Address on Lytec.[Facility Code] = Address.[Code] left join [sandboxmr].dbo.[Insurance] Ins on Lytec.[Primary Code] = Ins.[Code] where b1.[userid] = 54 and (b1.[DateSubmitted] >= convert(varchar(25),'2011-8-31',101) and b1.[DateSubmitted] <= convert(varchar(25),'2011-9-22',101)) union select /*Patients without chart numbers*/ cast(P.id as varchar(15)) as [chart number], '1' as newPatient, isnull(P.[Last Name],'') as [Last Name], isnull(P.[First Name],'') as [F

Tags (comma-separated) Cancel