tanj1035
asked on
SQL the column should return 1
Hi Experts,
In my query, when column "acceptedtestdrive" is 0, the column "accepetedwithouttestdrive percentage " returned 0 as well.
But the column "accepetedwithouttestdrive percentage " should return 1 as 1-0 = 1.
Can you help me on it? Thanks.
In my query, when column "acceptedtestdrive" is 0, the column "accepetedwithouttestdrive
But the column "accepetedwithouttestdrive
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
at: ... else 1- (cast (t.acceptedtestdrive as float)/cast(count(d.dealid
it could be else 1- 1 ... end as thus prodicing 0