[ad_1]
I have created this table with this piece of code
select *, round(Total_Subj_SFd/nullif((Total_Actual_of_Patients_Screened-"# of Subj in Screening"),0)*100,2) as "Current SF %"
from
(select *, Total_Actual_of_Patients_Screened - Total_Actual_of_Patients_Randomized - Total_Subj_SFd as "# of Subj in Screening",
cast(coalesce(cast(Weekly_Actual_of_Patients_Screened as float)/nullif(Weekly_Proj_of_Patients_Screened,0)*100,0)as int)as "Weekly % vs Plan Screened",
coalesce(round((Total_Actual_of_Patients_Screened/nullif(Total_Proj_of_Patients_Screened,0))*100 ,2),0) as "Total % vs Plan Screened",
cast(coalesce(cast(Weekly_Actual_of_Patients_Randomized as float)/nullif(Weekly_Proj_of_Patients_Randomized,0)*100,0)as int) as "Weekly % vs Plan Randomized",
coalesce(round((Total_Actual_of_Patients_Randomized/nullif(Total_Proj_of_Patients_Randomized,0))*100 ,2),0) as "Total % vs Plan Randomized"
from
(select
distinct(ivd.date) as Week_Ending_Date,
--sid.siteid as "Site",
wscproj.nume as Weekly_Proj_of_Patients_Screened,
coalesce(ev.count,0) as Weekly_Actual_of_Patients_Screened,
sum(wscproj.nume) over (order by ivd.date) as Total_Proj_of_Patients_Screened,
coalesce(sum(ev.count) over (order by ivd.date),0) as Total_Actual_of_Patients_Screened ,
wscproj2.nume2 as Weekly_Proj_of_Patients_Randomized,
coalesce(ire.c1,0) as Weekly_Actual_of_Patients_Randomized,
coalesce(sum(wscproj2.nume2) over (order by ivd.date),0) as Total_Proj_of_Patients_Randomized,
coalesce(sum(ire.c1) over (order by ivd.date),0) as Total_Actual_of_Patients_Randomized,
coalesce(sum(sf.count) over (order by ivd.date),0) as Total_Subj_SFd,
coalesce(sf.count,0) as "Weekly # of Subj SFd",
psfproj.psf1n as "Predicted SF %"
from
and I am filtering by a site parameter
where ((sid.siteid = ?) or (? = '[all]')) and ivd.date <= current_date::timestamp
order by ivd.date
to obtain this
enter image description here
but they ask me that with the site filter I give all the dates from Nov 19, 2021 to the current one and the sites that did not have a response that day come out with 0 and those that do are filled in as in the previous table
help): thanks
[ad_2]