*!* Time Period 09.00 - 18.59 = SUM(x)
*!* Time Period 19.00 - 08.59 = SUM(y)
FUNCTION JimFavorHot2(dtStart As DateTime, dtEnd As DateTime)
LOCAL h, mTotal1, mTotal2 As Integer
STORE 0 TO h, mTotal1, mTotal2
SET CENTURY ON && 2014
SET DATE YMD && 2014-12-31
SET HOURS TO 24 && 00:59 - 23:59
DO WHILE dtStart < dtEnd
h = HOUR(dtStart)
IF h >=9 AND h < 19 THEN
mTotal1 = mTotal1 + 1 && Minutes Mesure (Count)
ELSE
mTotal2 = mTotal2 + 1 && Minutes Mesure (Count)
ENDIF
dtStart = dtStart + 60 && Add 1 Minutes
ENDDO
RETURN LTRIM(STR(INT(mTotal1/60))) + ":" + PADL(INT(MOD(mTotal1, 60)), 2, "0") + "@" + ;
LTRIM(STR(INT(mTotal2/60))) + ":" + PADL(INT(MOD(mTotal2, 60)), 2, "0") + "@"
END FUNCTION
หรือลองแบบนี้ดู (ไม่แน่ใจว่าจะได้หรือไม่)
Code (SQL)
SELECT COUNT(CASE WHEN DATEPART(hour, IN_Date) BETWEEN 1 AND 2 THEN 1 ELSE 0 END ) AS '1 - 2',
SELECT COUNT(CASE WHEN DATEPART(hour, IN_Date) BETWEEN 3 AND 4 THEN 1 ELSE 0 END ) AS '3 - 4'
...
...
...
FROM yourTable WHERE IN_Date BETWEEN '2014-04-10' AND '2014-04-11'
SELECT MAX(This_is_a_chair) As MaxPlate FROM
(
SELECT ExactStartTime, ExactEndTime, This_is_a_chair
FROM CLR.Mymind(@myFunction, 30) -- 1/2 Hours. (Interval 30 minute.)
ORDER BY ExactStartTime ASC
) a
Load balance : Server 04
