Avatar of tanj1035
tanj1035
 asked on

SQL the column should return 1

Hi Experts,

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

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
awking00

8/22/2022 - Mon
Mike Eghtebas

re> But the column "accepetedwithouttestdrivepercentage" should return 1 as 1-0 = 1

at:  ... else 1- (cast (t.acceptedtestdrive as float)/cast(count(d.dealid) as float )) end as

it could be else 1- 1 ... end as thus prodicing 0
ASKER CERTIFIED SOLUTION
awking00

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck