通过存储过程查询的SQL，数据库设计器可以执行。帆软不支持调用，有没有遇到类似问题的小伙伴存储过程proc_productionUSE
GO
/****** Object: StoredProcedure . Script Date: 2020/10/17 11:57:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc .
as
declare @start datetime = '2020-09-16 08:00',@end datetime = '2020-09-16 18:00'
declare @step int = 2;
-------------------------------------------------------
declare @dates table(
sDate datetime,
eDate datetime
)
declare @curDate datetime = @start;
while(@curDate < @end)
begin
insert into @dates
select @curDate,
case when
@end > DATEADD(hour,@step,@curDate)
then
DATEADD(hour,@step,@curDate)
else
@end
end
set @curDate = DATEADD(hour,@step,@curDate)
end
--select * from @dates
select
rt.*
into #Routes
from ProductionTaskSheetMlotRouteEntities rt
where
rt.PassQuantity > 0 and
exists(
select * from ProductionTaskSheetMlotRouteFinishRecordEntities ft where
ft.ProductionTaskSheetMlotRouteId = rt.Id and
ft.CRDT between @start and @end
) and
exists(
select * from ProductionTaskSheetMlotEntities mt where
mt.id = rt.ProductionTaskSheetMlotId and
exists(
select * from ProductionTaskSheetEntities tt where
tt.id = mt.ProductionTaskSheetId and
exists(
select * from ProductionOrderDetailEntities dt where
dt.id = tt.ProductionOrderDetailId and
exists(
select * from ProductionOrderEntities pt where
pt.id = dt.OrderId and
exists(
select * from Tfbase043300 lt where
lt.Id = pt.ProductLineId and
exists(
select * from tfbase008300 mate where
mate.id = pt.MaterialId and
mate.PRST = 2
)
)
)
)
)
)
;with inQty as (
select
a.logDate
from #Routes zt
outer apply(
select max(a.CRDT) logDate from ProductionTaskSheetMlotRouteFinishRecordEntities a where a.ProductionTaskSheetMlotRouteId = zt.id
) a
where
zt. = 1
),
outQty as (
select
a.logDate,zt.*
from #Routes zt
outer apply(
select max(a.CRDT) logDate from ProductionTaskSheetMlotRouteFinishRecordEntities a where a.ProductionTaskSheetMlotRouteId = zt.id
) a
where
not exists(
select * from ProductionTaskSheetMlotRouteEntities rt where
zt.ProductionTaskSheetMlotId = rt.ProductionTaskSheetMlotId and
zt. < rt.
)
)
select
zt.sDate as ,
zt.eDate as ,
(
select count(*) from inQty a where a.logDate between zt.sDate and zt.eDate
) as ,
(
select count(*) from outQty a where a.logDate between zt.sDate and zt.eDate
) as
from @dates zt
drop table #Routes
--exec proc_production
GO错误信息
