select
 --disp.cDLCode as 发货单号,
 --disp.dDate as 发货日期,
 convert(varchar(7),disp.dDate,120) as 月份,
 year(disp.dDate) as 年份,
 dep.cDepName as 部门编码,
 per.cPersonName as 业务员编码,
 cus.cCusName as 客户名称,
 GYtwen.cinvstd as 规格型号,
 disps.cFree1 as 品牌,
 (disps.iQuantity )as 发货数量
from dispatchlist disp inner join DispatchLists disps on  disps.DLID=disp.dlid
left join UFDATA_102_2019.DBO.GY_twentysevenbitcode GYtwen on GYtwen.cInvCode=disps.cInvCode
left join UFDATA_102_2019.dbo.Customer cus on cus.cCusCode=disp.cCusCode
left join UFDATA_102_2019.dbo.inventory inven on inven.cInvCode=GYtwen.cInvCode
left join UFDATA_102_2019.dbo.Department dep on dep.cDepCode=disp.cDepCode
left join UFDATA_102_2019.dbo.Person per on per.cPersonCode=disp.cPersonCode
where year(disp.dDate) in ('${年份}')
and (charindex(dep.cDepName,'${部门}')>0 or '${部门}'='') 
and (charindex(per.cPersonName,'${业务员}')>0 or '${业务员}'='') 
and (charindex(cus.cCusName,'${客户}')>0 or '${客户}'='') 
--group by convert(varchar(7),disp.dDate,120) ,
-- year(disp.dDate) ,
-- dep.cDepName ,
 --per.cPersonName ,
 --cus.cCusName ,
-- GYtwen.cinvstd ,
-- disps.cFree1