In my query, when column "acceptedtestdrive" is 0, the column "accepetedwithouttestdrivepercentage" returned 0 as well.
But the column "accepetedwithouttestdrivepercentage" should return 1 as 1-0 = 1.
Can you help me on it? Thanks.
select d.submittedtimestamp, case when isnull(t.acceptedtestdrive,0) =0 then 0 else cast (t.acceptedtestdrive as float)/cast(count(d.dealid) as float ) end as accepetedtestdrivepercentage, case when isnull(t.acceptedtestdrive,0) =0 then 0 else 1- (cast (t.acceptedtestdrive as float)/cast(count(d.dealid) as float )) end as accepetedwithouttestdrivepercentage from [MMD_Feed].[dbo].[DealView] d left join #acceptedtestdrive t on d.submittedtimestamp= t.submittedtimestamp where d.dealstatus='sent' and d.alternatedeal='no' and d.manualdeal='no' and d.shopperaccepts>=1 and cast(d.submittedtimestamp as date)>=@StartDate and cast(d.submittedtimestamp as date)<=@EndDate group by d.submittedtimestamp,t.acceptedtestdrive order by d.submittedtimestamp desc
at: ... else 1- (cast (t.acceptedtestdrive as float)/cast(count(d.dealid
it could be else 1- 1 ... end as thus prodicing 0