The function splits the input rows into equal, periodical pieces.
It also provides back the weight of the actual part if it is not a full part.
The function is used with table valued expressions (executed row by row). eg. CROSS APPLY
the current Starttime of the record: note it may not fit to the current period start. e.g: you want to split the record for each day at 00:00, but Your record starts
at 22:00 and ends at next day 06:00, it will end up in two pieces: 22:00-24:00, 00:00-06:00
the current Endtime of the record: note it may not fit to the current period start+n(period). e.g: you want to split the record for each day at 01:00, but Your record starts
at 00:00 and ends in 02:00 it will end up in two pices: 00:00-01:00, and 01:00-02:00
period length. eg. 1 day >>1, 1 hour: 1/24
zero time determinate the exact start of the period. Even you define a dailiy (24hrs) period, You can set when does it start e.g: you can get a new row for each day at 2:00.
RETURNS @List TABLE (SUBROW_INDEX INTEGER, SUBROW_WEIGHT FLOAT, SUBROW_START FLOAT, SUBROW_END FLOAT, SUBROW_LENGTH FLOAT , SUBROW_SESSION INTEGER )
index of the current partweight of the current partstart of the current part
end onf the current part
length of the current part, note it can be shorter than the period
begining part 0:middle part 1:end part
Example:
SELECT
*,
(REASON_DURATION*SUBROW_WEIGHT) AS REASON_DURATION_WEIGHTED FROM
(
SELECT * FROM V_Reporting_All_Events A
CROSS APPLY dbo.fnMicroSplitRecordToPeriods(A.Event_start,A.Event_end ,1,43831)
) SPLITTED_TABLE
Source:
DROP FUNCTION dbo.fnMicroSplitRecordToPeriods;
GO
CREATE FUNCTION dbo.fnMicroSplitRecordToPeriods(
@aTimeStart FLOAT -- Period Start Time
, @aTimeEnd FLOAT -- Period End Time
, @aTimeStep FLOAT -- TimeStep Between Records
, @aTimeZero FLOAT -- StartTime of the period
) RETURNS @List TABLE (SUBROW_INDEX INTEGER, SUBROW_WEIGHT FLOAT, SUBROW_START FLOAT, SUBROW_END FLOAT, SUBROW_LENGTH FLOAT , SUBROW_SESSION INTEGER )
BEGIN
DECLARE @lCurTime FLOAT
DECLARE @lSubRowIndx INTEGER
DECLARE @lCurWeight FLOAT
DECLARE @lCurTimePart FLOAT
DECLARE @lWorkInt INTEGER
--Parameter Check
IF (@aTimeEnd=@aTimeStart)
BEGIN
INSERT INTO @List SELECT 0,0,@aTimeStart,@aTimeEnd,0,NULL
RETURN
END
--Initialization
SET @lSubRowIndx=1
SET @lCurTime=@aTimeStart
--Start Time Part
IF (FLOOR(@aTimeStart/@aTimeStep)<>(@aTimeStart/@aTimeStep)) --does not start at limit
BEGIN
SET @lWorkInt=FLOOR(@aTimeStart/@aTimeStep)+1 --next time
SET @lCurTimePart=((@aTimeStep*@lWorkInt)-@aTimeStart)
IF (@lCurTime+@lCurTimePart>@aTimeEnd)
BEGIN
SET @lCurTimePart=@aTimeEnd-@aTimeStart;
END
SET @lCurWeight=1 /( (@aTimeEnd-@aTimeStart) / @lCurTimePart )
INSERT INTO @List SELECT @lSubRowIndx,@lCurWeight,@aTimeStart,@lCurTime+@lCurTimePart,@lCurTimePart,-1
SET @lCurTime=@aTimeStart+@lCurTimePart
SET @lSubRowIndx=@lSubRowIndx+1
END
--Middle Part
SET @lCurTimePart=@aTimeStep
SET @lCurWeight=1 /( (@aTimeEnd-@aTimeStart) / @lCurTimePart )
WHILE ((@lCurTime+@lCurTimePart)<=@aTimeEnd)
BEGIN
INSERT INTO @List SELECT @lSubRowIndx,@lCurWeight,@lCurTime,@lCurTime+@lCurTimePart,@lCurTimePart, 0
SET @lCurTime=@lCurTime+@lCurTimePart
SET @lSubRowIndx=@lSubRowIndx+1
END
--End Time Part
SET @lCurTimePart=(@aTimeEnd-@lCurTime) --does not end at limit
IF (@lCurTimePart>0)
BEGIN
SET @lCurWeight=1 /( (@aTimeEnd-@aTimeStart) / @lCurTimePart )
INSERT INTO @List SELECT @lSubRowIndx,@lCurWeight,@aTimeStart,@lCurTime+@lCurTimePart,@lCurTimePart, 1
END
RETURN
END