SQL算法：一段时间范围内，去掉定时的计划时间，计算有效使用时间

发布日期：2022/8/17 10:49:56      浏览量：

-----维修时间去掉计划停机时间----
declare @s  int,@e int,@st datetime,@et datetime
declare @tmpS int,@tmpE int
declare @tmpSm int,@tmpEm int

---假设是 1 点到8点是 指定停机时间
set @s=1
set @e=8

---维修开始时间和结束时间
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)-
(
 ----当天内----
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  [实际使用时间(分钟)]
go