蜜桃av色欲a片精品一区,麻豆aⅴ精品无码一区二区,亚洲人成网站在线播放影院在线,亚洲 素人 字幕 在线 最新

  SQL算法：一段時(shí)間范圍內(nèi)，去掉定時(shí)的計(jì)劃時(shí)間，計(jì)算有效使用時(shí)間

發(fā)布日期：2022/8/17 10:49:56      瀏覽量：

-----維修時(shí)間去掉計(jì)劃停機(jī)時(shí)間----
declare @s  int,@e int,@st datetime,@et datetime
declare @tmpS int,@tmpE int
declare @tmpSm int,@tmpEm int

---假設(shè)是 1 點(diǎn)到8點(diǎn)是 指定停機(jī)時(shí)間
set @s=1
set @e=8

---維修開始時(shí)間和結(jié)束時(shí)間
set @st=’2022-8-16 6:00:00’
set @et=’2022-8-17 10:00:00’

set @tmpS=(select DATEPART(HH,@st))
set @tmpE=(select DATEPART(HH,@et))
set @tmpSm=(select DATEPART(MI,@st))
set @tmpEm=(select DATEPART(MI,@et))


--select DateDiff(MINUTE,@st,@et)-0

--print @tmpS


select DateDiff(MINUTE,@st,@et)-
(
 ----當(dāng)天內(nèi)----
CASE WHEN DateDiff(Day,@st,@et)=0 THEN

    (CASE WHEN @tmpS<=@s and @tmpE<=@s THEN 0 WHEN  @tmpS<=@s and @tmpE>@s and @tmpE<@e THEN (@tmpE-@s)*60+@tmpEm WHEN @tmpS<=@s and @tmpE>=@e THEN (@e-@s)*60 WHEN @tmpS>=@s and @tmpE>@s and @tmpE<@e THEN DateDiff(MINUTE,@st,@et) WHEN @tmpS>=@s and @tmpS<@e and @tmpE>=@e THEN (@e-@tmpS)*60-@tmpSm WHEN  @tmpS>=@e and @tmpE>=@e THEN 0 ELSE 0 END)

 ELSE
 -----跨天----
    (
(CASE WHEN @tmpS<=@s and @tmpE<=@s THEN 0+(DateDiff(Day,@st,@et)*60*(@e-@s)) WHEN  @tmpS<=@s and @tmpE>@s and @tmpE<@e THEN (@tmpE-@s)*60+@tmpEm+(DateDiff(Day,@st,@et)*60*(@e-@s)) WHEN @tmpS<=@s and @tmpE>=@e THEN (@e-@s)*60+(DateDiff(Day,@st,@et)*60*(@e-@s)) WHEN @tmpS>=@s and @tmpE>@s and @tmpE<@e THEN DateDiff(MINUTE,@st,@et)+(DateDiff(Day,@st,@et)*60*(@e-@s)) WHEN @tmpS>=@s and @tmpS<@e and @tmpE>=@e THEN (@e-@tmpS)*60-@tmpSm+(DateDiff(Day,@st,@et)*60*(@e-@s)) WHEN  @tmpS>=@e and @tmpE>=@e THEN 0+(DateDiff(Day,@st,@et)*60*(@e-@s)) ELSE 0+(DateDiff(Day,@st,@et)*60*(@e-@s)) END)
)
 
 END)  as  [實(shí)際使用時(shí)間(分鐘)]
go