绩效系统报表sql语句

select  companyname, *   from PDM_SalesOrderHD where docdate  between '2020-05-01' and '2020-06-01'  order by docdate desc

--统计各个分厂在某个时间段内的画图总数
select b.companyname,count(*) as digit  from PDM_WOMFGOrder a  inner join PDM_SalesOrderHD b on a.refdoccode = b.DocCode where status = 6 
 and  deldate between '2020-06-01' and '2021-12-01' group by b.companyname
--按时间统计各个字母开头的型号编了多少个编码
select substring(a.cv3,0,patindex('%[0-9]%',a.cv3)) as mstr,count(*) as digit  from PDM_sSODeliveryDT  a inner join PDM_sSODeliveryHd b on a.doccode = b.doccode and b.formid = 75108 
where isnull(sfcstatus,0)=6  group by  substring(a.cv3,0,patindex('%[0-9]%',a.cv3)) order by  substring(a.cv3,0,patindex('%[0-9]%',a.cv3)) asc
--项目汇总那里统计指定时间内各个字母开头的项目别名的项目总数
select substring(cprj,0,patindex('%[0-9]%',cprj)),count(*) from PM_ProjectDocHD where DocStatus >=100 and  enterdate between '2020-06-01' and '2021-12-01' 
group by  substring(cprj,0,patindex('%[0-9]%',cprj)) order by substring(cprj,0,patindex('%[0-9]%',cprj)) asc
--统计业务经理的图纸确认率
select  isnull(b.sdgroupname,'') as sdgroupname,sum(case when a.sfcstatus in (1,2,6,7) then 1 else 0 end) as digit, sum(case when a.sfcstatus in (6,7) then 1 else 0 end) as deliverdigit,
case when sum(case when a.sfcstatus in (1,2,6,7) then 1 else 0 end) =0 then '--%' else
  RTRIM(CONVERT(float,cast(round(sum(case when a.sfcstatus in (6,7) then 1 else 0 end)*100.00/sum(case when a.sfcstatus in (1,2,6,7) then 1 else 0 end),2) as decimal(18,2))))+'%'
  end as pct
 from PDM_salesOrderDT2 a inner join PDM_salesOrderHd b on a.Doccode = b.DocCode where a.PickBeginDay between '2020-06-01' and '2021-12-01' 
group by isnull(b.sdgroupname,'')
--经销商图纸确认率
select  isnull(b.cltcode,'') as cltcode, isnull(b.CltName,'') as cltname,sum(case when a.sfcstatus in (1,2,6,7) then 1 else 0 end) as completdigit, sum(case when a.sfcstatus in (6,7) then 1 else 0 end) deliverdigit,
case when sum(case when a.sfcstatus in (1,2,6,7) then 1 else 0 end) =0 then '--%' else
  RTRIM(CONVERT(float,cast(round(sum(case when a.sfcstatus in (6,7) then 1 else 0 end)*100.00/sum(case when a.sfcstatus in (1,2,6,7) then 1 else 0 end),2) as decimal(18,2))))+'%'
  end as pct from PDM_salesOrderDT2 a inner join PDM_salesOrderHd b on a.Doccode = b.DocCode where a.PickBeginDay between '2020-06-01' and '2021-12-01' 
group by isnull(b.cltcode,''),isnull(b.CltName,'')
--按月份和图纸类型，统计出各个月份的图纸份数

select SUBSTRING(CONVERT(CHAR(23), deldate, 121),0,8) as fd ,count(*) as digit 
,sum(case when isnull(c.MatCode,'')='P999999' then 1 else 0 end) as [组合型材]
,sum(case when isnull(c.MatCode,'')='P120001' then 1 else 0 end) as [门窗类]
,sum(case when isnull(c.MatCode,'')='P110001' then 1 else 0 end) as [幕墙类]
,sum(case when isnull(c.MatCode,'')='P130001' then 1 else 0 end) as [工业材]
 from PDM_WOMFGOrder a  inner join PDM_SalesOrderHD b on a.refdoccode = b.DocCode inner join PDM_SalesOrderDT2 c on a.refrowid = c.Rowid
where status = 6  and  deldate between '2020-01-01' and '2020-12-31' group by SUBSTRING(CONVERT(CHAR(23), deldate, 121),0,8)  order by SUBSTRING(CONVERT(CHAR(23), deldate, 121),0,8) 

--按分厂和图纸类型，统计出各个月份的图纸份数

select b.companyname as fd ,count(*) as digit 
,sum(case when isnull(c.MatCode,'')='P999999' then 1 else 0 end) as [组合型材]
,sum(case when isnull(c.MatCode,'')='P120001' then 1 else 0 end) as [门窗类]
,sum(case when isnull(c.MatCode,'')='P110001' then 1 else 0 end) as [幕墙类]
,sum(case when isnull(c.MatCode,'')='P130001' then 1 else 0 end) as [工业材]
 from PDM_WOMFGOrder a  inner join PDM_SalesOrderHD b on a.refdoccode = b.DocCode inner join PDM_SalesOrderDT2 c on a.refrowid = c.Rowid
where status = 6  and  deldate between '2020-01-01' and '2020-12-31' group by b.companyname

--新单/非新单画图数汇总
select ''''+SUBSTRING(CONVERT(CHAR(23), deldate, 121),0,8) as [月份] ,
count(*) as [总画图数(含组合二维码)] ,
sum(case when 
(
 (b.doctype!='新增组合' and isnull(c.MatCode,'')!='P999999')
 or 
 b.doctype='新增组合'
 )
 then 1 else 0 end) as [画图数(不含组合二维码)] ,
sum(case when isnull(c.MatCode,'')='P999999' and b.doctype!='新增组合' then 1 else 0 end) as [组合二维码] ,
sum(case when isnull(b.doctype,'')='新增组合' then 1 else 0 end) as [新增组合] ,
sum(case when isnull(c.MatCode,'')='P120001' then 1 else 0 end) as [门窗类] ,
sum(case when isnull(c.MatCode,'')='P110001' then 1 else 0 end) as [幕墙类] ,
sum(case when isnull(c.MatCode,'')='P130001' then 1 else 0 end) as [工业材]  from PDM_WOMFGOrder a  inner join PDM_SalesOrderHD b on a.refdoccode = b.DocCode inner join PDM_SalesOrderDT2 c on a.refrowid = c.Rowid 
where status = 6  and  deldate between '2021-01-01' and '2022-01-01' 
 and isnull(b.statisTypeName,'') != '新单'  
group by SUBSTRING(CONVERT(CHAR(23), deldate, 121),0,8)  order by SUBSTRING(CONVERT(CHAR(23), deldate, 121),0,8)