I am trying to show the previous two days not including today for my query and I need for each facility to show both days even if the data being populated into the columns PCR and Rapid are both 0. What I have below will show all the data that comes in but not if a certain date doesn't have the data.
with cte_res as (
select
Facility
,cast([created date] as date) 'CDate'
,case when Rapid = 'False' then 1 else 0 end as 'PCR'
,case when Rapid = 'True' then 1 else 0 end as 'Rapid'
,'Resident' as 'Patient'
from sqlee.mentor.dbo.covidresidenttest
where [Created Date] between cast(DATEADD(day,-2, getdate()) as date) and
cast(DATEADD(day,-1, getdate()) as date)
) ,
cte_emp as (
select
Facility
,cast([created date] as date) 'CDate'
,case when Rapid = 'False' then 1 else 0 end as 'PCR'
,case when Rapid = 'True' then 1 else 0 end as 'Rapid'
,'Employee' as 'Patient'
from sqlee.mentor.dbo.covidemployeetest
where [Created Date] between cast(DATEADD(day,-2, getdate()) as date) and cast(DATEADD(day,-1,
getdate()) as date)
)
select Facility,CDate,Patient,sum(PCR) as 'PCR',sum(Rapid) as 'Rapid'
from cte_res group by Facility,CDate,Patient
union all
select Facility,CDate,Patient,sum(PCR) as 'PCR',sum(Rapid) as 'Rapid'
from cte_emp group by Facility,CDate,Patient
order by Facility,CDate,Patient
