1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| drop table #kosu2
declare @coef numeric
declare @date1 datetime , @date2 datetime, @date3 datetime,@date4 datetime
set @date1=DATEADD(hour,09,CONVERT(VARCHAR(10),DATEADD(d,-0,CONVERT(VARCHAR(10),GETDATE(),20)),20))
set @date2=DATEADD(hour,10,CONVERT(VARCHAR(10),DATEADD(d,-0,CONVERT(VARCHAR(10),GETDATE(),20)),20))
--select (cast(sum (prodtime) as numeric)) / ( count (ksk) * 11460 )
set @coef = (select( cast(min (prodtime) as numeric ) / 125)
--( count (ksk) * MAX (prodtime))
from lep_his.productiontime
where ksk in
(SELECT distinct [kSK]
FROM [LEPS].[LEP_HIS].[WorkTimes]
where ksk in ( SELECT distinct [KSK]
FROM [LEPS].[LEP_HIS].[WorkTimes]
where StartTime between @date1 and @date2
and Workplace = 'ass12'
and DispositionCode = 'ok')))
--and ksk not in ( SELECT distinct [KSK]
--FROM [LEPS].[LEP_HIS].[WorkTimes]
--where StartTime < @date1
--and Workplace = 'ass12'
--and DispositionCode = 'ok')
create table #kosu2 (total varchar(20),coef numeric)
insert into #kosu2 values('kosu', @coef)
select * from #kosu2 |
Partager