Hello, I'm using the following SQL expression and getting an OLEDB error returned that I cannot figure out. If I remove the lines that I've highlighted, of course I don't get my full data set, but it does return without error. Any suggestions?
PS this script does work in DatabaseBrowser.
select bsc.id,
salesdate,
bsc.entrydate,
cast(datediff(day, bsc.entrydate, salesdate) / 10 as int) as daycount,
commissionedsalesperson,
cl.name as clname,
bmtdefaultloc,
splitpercentage,
store_name,
worksheetprofit,
bsc.name as customer,
bsc_commissions.bsc_id,
bmi.year,
bmi.description,
CAST(LEFT(length,2)AS int) as lengthft,
(cast(datediff(day, bsc.entrydate, salesdate) / 10 as int) * CAST(LEFT(length,2)AS int)) as points
from bsc
left outer join bsc_commissions
on bsc.id = bsc_commissions.bsc_id
left outer join cl
on bsc_commissions.commissionedsalesperson = cl.cl_id
left outer join store_info
on cl.bmtdefaultloc = store_info.store_id
left outer join bsc_boats
on bsc.id = bsc_boats.bsc_id
left outer join bmi
on bsc_boats.boatmodelid = bmi.id
where writtendate >= '01/01/2016'
and commissionedsalesperson is not null
and cl.name is not null
and bsc.status = 'o'
and splitpercentage >= '0.5'
order by commissionedsalesperson asc;
