比如。設定成： 作業時間為： 上午8:30到12:00， 下午13:30到17:30.
那么11:50 到 14:00 就是差了40分鐘。
今天17:00到第二天的8:45. 就是差了45分鐘。
不考慮周六日， 也就是周六日也是作業日。
這個sql函式怎么寫？
uj5u.com熱心網友回復：
DECLARE @t TABLE(timeStart TIME NOT NULL, timeEnd TIME NOT NULL);
INSERT @t(timeStart, timeEnd)VALUES('08:30:00', '12:00:00'),('13:30:00','17:30:00');
DECLARE @start TIME='11:50:00',@end TIME='14:00:00'
;WITH tt AS (
SELECT DATEDIFF(MINUTE, @start,t.timeEnd) AS [Minutes], t.timeEnd FROM @t t WHERE @start BETWEEN t.timeStart AND t.timeEnd
UNION ALL SELECT DATEDIFF(MINUTE,t.timeStart, @end),t.timeEnd FROM @t t INNER JOIN tt ON t.timeEnd<>tt.timeEnd WHERE @end BETWEEN t.timeStart AND t.timeEnd)--) tt
SELECT SUM([Minutes]) [Minutes] FROM tt;
SET @start='17:00'
SET @end='8:45'
;WITH tt AS (
SELECT DATEDIFF(MINUTE, @start,t.timeEnd) AS [Minutes], t.timeEnd FROM @t t WHERE @start BETWEEN t.timeStart AND t.timeEnd
UNION ALL SELECT DATEDIFF(MINUTE,t.timeStart, @end),t.timeEnd FROM @t t INNER JOIN tt ON t.timeEnd<>tt.timeEnd WHERE @end BETWEEN t.timeStart AND t.timeEnd)--) tt
SELECT SUM([Minutes]) [Minutes] FROM tt;
uj5u.com熱心網友回復：
測驗不正確
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @t TABLE(timeStart TIME NOT NULL, timeEnd TIME NOT NULL);
INSERT @t(timeStart, timeEnd)VALUES('08:30:00', '12:00:00'),('13:30:00','17:30:00');
DECLARE @start TIME='11:50:00',@end TIME='11:55:00'
;WITH tt AS (
SELECT DATEDIFF(MINUTE, @start,t.timeEnd) AS [Minutes], t.timeEnd FROM @t t WHERE @start BETWEEN t.timeStart AND t.timeEnd
UNION ALL SELECT DATEDIFF(MINUTE,t.timeStart, @end),t.timeEnd FROM @t t INNER JOIN tt ON t.timeEnd<>tt.timeEnd WHERE @end BETWEEN t.timeStart AND t.timeEnd)--) tt
SELECT SUM([Minutes]) [Minutes] FROM tt;
我就用11：50到11:55分，算出來是10，而正確結果應該是5
uj5u.com熱心網友回復：
declare @st time,@et time
select @st = '11:50',@et = '13:55'
select st,et,(case when sm=em then datediff(minute,st,et) else datediff(minute,st,'12:00')+datediff(minute,'13:30',et) end)
from (
select *
,(case when dst<='12:00' then 'am' else 'pm' end) as sm
,(case when det<='12:00' then 'am' else 'pm' end) as em
from (
select @st as st,@et as et
,(case when @st<'8:30' then convert(time,'8:30') when @st>'17:30' then convert(time,'17:30') else @st end) as dst
,(case when @et<'8:30' then convert(time,'8:30') when @et>'17:30' then convert(time,'17:30') else @et end) as det
) a
) a
uj5u.com熱心網友回復：
declare @st time,@et time
select @st = '7:50',@et = '13:55'
select st,et,(case when sm=em then datediff(minute,dst,det) else datediff(minute,dst,'12:00')+datediff(minute,'13:30',det) end)
from (
select *
,(case when dst<='12:00' then 'am' else 'pm' end) as sm
,(case when det<='12:00' then 'am' else 'pm' end) as em
from (
select @st as st,@et as et
,(case when @st<'8:30' then convert(time,'8:30') when @st>'17:30' then convert(time,'17:30') else @st end) as dst
,(case when @et<'8:30' then convert(time,'8:30') when @et>'17:30' then convert(time,'17:30') else @et end) as det
) a
) a
前邊datediff參考變數有點問題
uj5u.com熱心網友回復：
我估計他要做的是一個請假，或缺勤機制。上面的陳述句能計算當日的缺勤，但是跨日缺勤就不能算了，譬如starttime 15:00打卡,endtime 9:00 算出來是負數的
uj5u.com熱心網友回復：
真要進行跨日期計算，那很麻煩的，尤其碰到跨多天的時候declare @st datetime,@et datetime
select @st = '2019-12-1 17:00',@et = '2019-12-2 8:55'
select *
,(case
when convert(date,st)<>convert(date,et) then (datediff(d,st,et)+1)*(datediff(minute,'8:30','12:00')+datediff(minute,'13:30','17:30'))
- datediff(minute,'8:30',dst)
+ (case when sm='pm' then 90 else 0 end)
- datediff(minute,det,'17:30')
+ (case when em='am' then 90 else 0 end)
else (case
when sm=em then datediff(minute,dst,det)
else datediff(minute,dst,'12:00')+datediff(minute,'13:30',det)
end
)
end
) as df
from (
select *
,(case when dst<='12:00' then 'am' else 'pm' end) as sm
,(case when det<='12:00' then 'am' else 'pm' end) as em
from (
select @st as st,@et as et
,(case
when convert(time,@st)<'8:30' then convert(time,'8:30')
when convert(time,@st)>'17:30' then convert(time,'17:30')
else convert(time,@st)
end) as dst
,(case
when convert(time,@et)<'8:30' then convert(time,'8:30')
when convert(time,@et)>'17:30' then convert(time,'17:30')
else convert(time,@et)
end) as det
) a
) a
uj5u.com熱心網友回復：
declare @st datetime,@et datetime,@ams time,@ame time,@pms time,@pme time
select @ams = '8:30',@ame = '12:00',@pms = '13:30',@pme = '17:30'
select @st = '2019-12-1 17:20',@et = '2019-12-4 8:35'
select st,et
,(case
when convert(date,st)<>convert(date,et) then (datediff(d,st,et)+1)*(datediff(minute,ams,ame)+datediff(minute,pms,pme))
- datediff(minute,ams,dst)
+ (case when sm='pm' then datediff(minute,ame,pms) else 0 end)
- datediff(minute,det,pme)
+ (case when em='am' then datediff(minute,ame,pms) else 0 end)
else (case
when sm=em then datediff(minute,dst,det)
else datediff(minute,dst,ame)+datediff(minute,pms,det)
end
)
end
) as df
from (
select *
,(case when dst<=ame then 'am' else 'pm' end) as sm
,(case when det<=ame then 'am' else 'pm' end) as em
from (
select @st as st,@et as et,@ams as ams,@ame as ame,@pms as pms,@pme as pme
,(case
when convert(time,@st)<@ams then @ams
when convert(time,@st)>@pme then @pme
else convert(time,@st)
end) as dst
,(case
when convert(time,@et)<@ams then convert(time,@ams)
when convert(time,@et)>@pme then convert(time,@pme)
else convert(time,@et)
end) as det
) a
) a
變數全部抽取出來
uj5u.com熱心網友回復：
--CREATE TABLE #T
--(ID INT IDENTITY(1,1),
-- START_TIME TIME,
-- END_TIME TIME)
--INSERT INTO #T
--SELECT '08:30','12:00' UNION ALL
--SELECT '13:30','17:30'
--GO
DECLARE @START_TIME DATETIME
DECLARE @END_TIME DATETIME
SET @START_TIME='2019-12-12 11:50:00'
SET @END_TIME='2019-12-13 11:45:00'
;WITH CTE_1
AS
(SELECT 1 AS ID
FROM MASTER.DBO.SPT_VALUES A
JOIN MASTER.DBO.SPT_VALUES B ON 1=1
WHERE A.TYPE='P' AND B.TYPE='P'
AND A.NUMBER<=99 AND B.NUMBER<=999),
CTE_2
AS
(SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS SEQ FROM CTE_1),
CTE_3
AS
(SELECT CONVERT(VARCHAR(10),DATEADD(MINUTE,SEQ,@START_TIME),120) AS ACT_DATE,
CONVERT(VARCHAR(5),DATEADD(MINUTE,SEQ,@START_TIME),114) AS ACT_TIME
FROM CTE_2 A
JOIN (SELECT DATEDIFF(MINUTE,@START_TIME,@END_TIME) DIFF) AS B ON SEQ<=DIFF),
CTE_4
AS
(SELECT START_TIME,CONVERT(VARCHAR(5),DATEADD(MINUTE,SEQ,START_TIME),114) AS REF_MINUTE
FROM #T A
JOIN CTE_2 ON SEQ<=DATEDIFF(MINUTE,START_TIME,END_TIME))
SELECT COUNT(1)-COUNT(DISTINCT ACT_DATE+CAST(START_TIME AS VARCHAR))
FROM CTE_4 AS A
JOIN CTE_3 AS B ON A.REF_MINUTE=B.ACT_TIME
uj5u.com熱心網友回復：
謝謝諸位大神，沒想到能這么多回復。 我先研究研究。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/87593.html
標籤：疑難問題
上一篇：求大神