研发费用查询

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter procedure GF_2017研发费用(
    @FDateStart datetime,
    @FDateEnd datetime )
as
set nocount on

--可调节腰靠座椅的研发
if object_id('tempdb..#a') is not null Begin
    drop table #a
End
select * into #a from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart 
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '可调节腰靠座椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #a

--站立式办公简易伸缩座椅的研发
if object_id('tempdb..#b') is not null Begin
    drop table #b
End
select * into #b from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '站立式办公简易伸缩座椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #b

--扶手集成开关可调前后靠背办公椅的研发
if object_id('tempdb..#c') is not null Begin
    drop table #c
End
select * into #c from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart 
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '扶手集成开关可调前后靠背办公椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #c

--扶手集成开关可调上下高度的办公椅的研发
if object_id('tempdb..#d') is not null Begin
    drop table #d
End
select * into #d from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '扶手集成开关可调上下高度的办公椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #d

--基于人机工程学设计的智能多变机器人椅的研发
if object_id('tempdb..#e') is not null Begin
    drop table #e
End
select * into #e from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '基于人机工程学设计的智能多变机器人椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #e

--多功能培训椅的研发
if object_id('tempdb..#f') is not null Begin
    drop table #f
End
select * into #f from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart 
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '多功能培训椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #f

--椅背倾仰可调的办公椅（午休椅）的研发
if object_id('tempdb..#g') is not null Begin
    drop table #g
End
select * into #g from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart 
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '椅背倾仰可调的办公椅（午休椅）的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #g

--座面多段式调节健康办公椅的研发
if object_id('tempdb..#h') is not null Begin
    drop table #h
End
select * into #h from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,FEntrySelfB0455 from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart 
and a.FDate <= @FDateEnd
and FEntrySelfB0455 = '座面多段式调节健康办公椅的研发'
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName,FEntrySelfB0455
) a;
--select * from #h

--3月汇总
if object_id('tempdb..#hz') is not null Begin
    drop table #hz
End
select * into #hz from (
select a.FDate,avg(b.FPrice) as FPrice,t2.FNumber,t2.FName,max(t3.FName) as FunitName,avg(b.FPrice)*sum(b.FauxQty) as FAmount,sum(b.FauxQty) as FauxQty,'最后合计' as '合计' from icstockbill a
inner join icstockbillentry b on a.FinterID = b.FinterID
inner join T_department t1 on a.FHeadSelfB0435 = t1.FitemID
inner join t_icitem t2 on b.FitemID = t2.FitemID
inner join T_MeasureUnit t3 on b.FunitID = t3.FMeasureUnitID
where  FTranType = 24
and a.FHeadSelfB0435 = '1583' 
and b.FEntrySelfB0455 <> ''
and a.FDate >= @FDateStart
and a.FDate <= @FDateEnd
--and t2.FNumber = '1011.1003.1090'
group by a.FDate,t2.FNumber,t2.FName
) a;

select z.*,
a.FAuxQty as '可调节腰靠座椅的研发',b.FAuxQty as '站立式办公简易伸缩座椅的研发',c.FAuxQty as '扶手集成开关可调前后靠背办公椅的研发',
d.FAuxQty as '扶手集成开关可调上下高度的办公椅的研发',e.FAuxQty as '基于人机工程学设计的智能多变机器人椅的研发',f.FAuxQty as '多功能培训椅的研发',
g.FAuxQty as '椅背倾仰可调的办公椅（午休椅）的研发',h.FAuxQty as '座面多段式调节健康办公椅的研发' 
from #hz z
left join #a a on z.Fdate = a.FDate and z.FNumber = a.FNumber
left join #b b on z.Fdate = b.FDate and z.FNumber = b.FNumber
left join #c c on z.Fdate = c.FDate and z.FNumber = c.FNumber
left join #d d on z.Fdate = d.FDate and z.FNumber = d.FNumber
left join #e e on z.Fdate = e.FDate and z.FNumber = e.FNumber
left join #f f on z.Fdate = f.FDate and z.FNumber = f.FNumber
left join #g g on z.Fdate = g.FDate and z.FNumber = g.FNumber
left join #h h on z.Fdate = h.FDate and z.FNumber = h.FNumber
order by z.FDate,z.FNumber

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

--查询分析工具执行 exec GF_2017研发费用 '********','########'