43
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