Запрос такой:

select t.tapid, isnull(m.family,'') as family, isnull(m.name,'') as name, isnull(m.ot,'') ot, convert(date, isnull(m.date_bd, '01.01.1901')) as date_db, isnull(se.servicemedicalcode,'') as scode,
substring(rtrim(ltrim(isnull(se.ServiceMedicalName,''))),1,250) as sName, isnull(mkb.ds, '') as ds, substring(ltrim(rtrim(isnull(d.DepartmentName,''))),1,30) as DName, isnull(do.FAM_V, '') as fam_v, convert(date, isnull(s.DATE_P,'01.01.1901')) as date_p, substring(isnull(p.NAME,''),1,3) as profit
from r_hlt_TAP as t
inner join r_hlt_SMTAP as s on s.rf_TAPID=t.tapid
inner join r_hlt_mkab as m on m.MKABID=t.rf_MKABID
inner join r_oms_department as d on d.DepartmentId=s.rf_DepartmentID
left join r_hlt_LPUDoctor as do on do.LPUDoctorID=s.rf_LPUDoctorID
inner join r_oms_servicemedical as se on se.servicemedicalId=s.rf_omsservicemedicalId
left join r_oms_kl_ProfitType as p on p.kl_ProfitTypeID=t.rf_kl_ProfitTypeID
left join r_oms_mkb as mkb on mkb.mkbid=s.rf_MKBID
where ((convert (date, s.DATE_P)>@d2 or convert(date,s.date_p)<convert(date,@PrevDate)) and convert(date,t.DateClose)>=@d1 and convert(date,t.DateClose)<=@d2)
and se.servicemedicalname like 'D%'
and t.IsClosed=1

Не выдает ничего

А Запрос в таком виде выдает много строчек, где se.servicemedicalname начинается именно на символ D
select t.tapid, isnull(m.family,'') as family, isnull(m.name,'') as name, isnull(m.ot,'') ot, convert(date, isnull(m.date_bd, '01.01.1901')) as date_db, isnull(se.servicemedicalcode,'') as scode,
substring(rtrim(ltrim(isnull(se.ServiceMedicalName,''))),1,250) as sName, isnull(mkb.ds, '') as ds, substring(ltrim(rtrim(isnull(d.DepartmentName,''))),1,30) as DName, isnull(do.FAM_V, '') as fam_v, convert(date, isnull(s.DATE_P,'01.01.1901')) as date_p, substring(isnull(p.NAME,''),1,3) as profit
from r_hlt_TAP as t
inner join r_hlt_SMTAP as s on s.rf_TAPID=t.tapid
inner join r_hlt_mkab as m on m.MKABID=t.rf_MKABID
inner join r_oms_department as d on d.DepartmentId=s.rf_DepartmentID
left join r_hlt_LPUDoctor as do on do.LPUDoctorID=s.rf_LPUDoctorID
inner join r_oms_servicemedical as se on se.servicemedicalId=s.rf_omsservicemedicalId
left join r_oms_kl_ProfitType as p on p.kl_ProfitTypeID=t.rf_kl_ProfitTypeID
left join r_oms_mkb as mkb on mkb.mkbid=s.rf_MKBID
where ((convert (date, s.DATE_P)>@d2 or convert(date,s.date_p)<convert(date,@PrevDate)) and convert(date,t.DateClose)>=@d1 and convert(date,t.DateClose)<=@d2)
and se.servicemedicalname like '%'
and t.IsClosed=1

Где искать причину?

CrazHunt, '%D%' тоже не выдает строки.
'%90%' тоже не выдает строки.

Konst_One, varchar(500)

Просто перепутал поля: нужно ServicemedicalCode вместо ServicemedicalName

Близорукий глаз