/* exec p_EquipmentRegisterAndUseStatistic */ --统计某地区某时间段内的核发和注销的设备（备案和使用）数量 alter proc p_EquipmentRegisterAndUseStatistic @cityid int=null--城市id ,@areaid int=null--区县id ,@datestart nvarchar(10)=null--起始日期 ,@dateend nvarchar(10)=null--截止日期 as

--所有的设备类型 select distinct EquipmentType into #equipTypes from dbo.EquipmentRecordApplication union all select '合计' as EquipmentType

--每种类型登记的数量 select EquipmentType ,count(1) as RegisterCount into #regcnt from dbo.EquipmentRecordApplication a join dbo.WorkflowRecords b on b.EquipmentID =a.ID and b.WorkflowCode='W0101' and b.StatusCode='S05' where a.IsActive=1 group by EquipmentType union all select '合计' as EquipmentType ,count(1) as RegisterCount from dbo.EquipmentRecordApplication a join dbo.WorkflowRecords b on b.EquipmentID =a.ID and b.WorkflowCode='W0101' and b.StatusCode='S05' where a.IsActive=1

--每种类型注销登记的数量

--每种类型使用申请的数量 select b.EquipmentType ,COUNT(1) as UseCount into #usecnt from dbo.UseApplyInfo a join dbo.EquipmentRecordApplication b on b.ID = a.EquipmentID and b.IsActive=1 join dbo.WorkflowRecords c on c.EquipmentID =a.EquipmentID and c.WorkflowCode='W0203' and c.StatusCode='S05' where a.IsActive=1 group by b.EquipmentType union all select '合计' as EquipmentType ,count(1) as UseCount from dbo.UseApplyInfo a join dbo.EquipmentRecordApplication b on b.ID = a.EquipmentID and b.IsActive=1 join dbo.WorkflowRecords c on c.EquipmentID =a.EquipmentID and c.WorkflowCode='W0203' and c.StatusCode='S05' where a.IsActive=1

--每种类型注销使用的数量

--结果 select a.EquipmentType ,b.RegisterCount ,c.UseCount from #equipTypes a left join #regcnt b on b.EquipmentType=a.EquipmentType left join #usecnt c on c.EquipmentType = a.EquipmentType

drop table #equipTypes,#regcnt,#usecnt