select *
from(
SELECT 
	i.[IREQ_PID] as HN
	,i.[IRESI_LN] as LN
    ,r.IREQ_OD_NO as OrderNumber
	,CONVERT(varchar, i.IREQ_LAST_CHK_DT, 25) as ReceiveDateTime
	,r.IREQ_FULLNAME as Name
	,i.[IRESI_RESULT] as [Specimen Type]
	
	
	,(select top 1 y.IREQ_OD_NO
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IREQ_PID = i.IREQ_PID
	and CONVERT(date, z.IREQI_OD_DT) = CONVERT(date, i.IREQI_OD_DT)
	and z.IRESI_CODE in ('SP702')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'SputumC(5(suction)'
	--,(select top 1 a.Result from @temp as a where a.HN = i.IREQ_PID and CONVERT(date, a.orderDate) = CONVERT(date, i.IREQI_OD_DT) and a.TestCode = 'SP702') as 'SputumC(5(suction)'
	
	,(select top 1 y.IREQ_OD_NO
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IREQ_PID = i.IREQ_PID
	and CONVERT(date, z.IREQI_OD_DT) = CONVERT(date, i.IREQI_OD_DT)
	and z.IRESI_CODE in ('SP523')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'Sputum culture 35101'
	--,(select top 1 b.Result from @temp as b where b.HN = i.IREQ_PID and CONVERT(date, b.orderDate) = CONVERT(date, i.IREQI_OD_DT) and b.TestCode = 'SP523') as 'Sputum culture 35101'
	
	,(select z.IRESI_RESULT
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IRESI_LN = i.IRESI_LN
	and z.IRESI_CODE in ('MB002')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'Gram Stain 1'
	
	,(select z.IRESI_RESULT
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IRESI_LN = i.IRESI_LN
	and z.IRESI_CODE in ('MB085')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'Gram Stain 2'
	
	,(select z.IRESI_RESULT
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IRESI_LN = i.IRESI_LN
	and z.IRESI_CODE in ('MB101')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'Gram Stain 3'
	
	,(select z.IRESI_RESULT
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IRESI_LN = i.IRESI_LN
	and z.IRESI_CODE in ('MB047')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'PMN'
	
	,(select z.IRESI_RESULT
	from [view_lab_information_Result_Items] as z join view_lab_information_Request as y on z.IRESI_LN = y.IREQ_LN
	where z.ISP_STATE = 'C' and z.IRESI_INACTIVE = 'N' and z.IREQI_STATE <> 'R' and z.iresi_state = 'A' 
	and z.IRESI_LN = i.IRESI_LN
	and z.IRESI_CODE in ('MB062')
	and  z.IREQI_OD_DT between 'd1' and 'd2') as 'Epithelial cell'
	
	FROM [view_lab_information_Result_Items] as i join view_lab_information_Request as r on i.IRESI_LN = r.IREQ_LN
	where i.IRESI_RESULT = 'Sputum' and iresi_code = 'MC065' and i.IRESI_STATE = 'A' and i.IREQI_STATE <> 'R' 
	and i.IRESI_INACTIVE = 'N' and i.ISP_STATE = 'C' and r.IREQ_INACTIVE = 'N'
and  i.IREQI_OD_DT between 'd1' and 'd2'
) as x
where x.ln = x.ln