Not sure I understand what you are trying to achieve.
Something like creating reference timestamps?
(with timestamps instead of integer dates, but same principle applied)?
Could you post some sample input records and what you expect to see?
I do not need a Reference Timestamp. Some might refer to it as a matrix, commonly used in call centers for interval reporting.
Assuming an associate has 50-minutes of time logged in, I need to distribute it across 15-minute intervals. The fields I'm working with only give the Shift Start Time and Duration(mins). So, if the associate logged in at 8:00, I would need the 50-minute distribution to look like this:
Start Stop #Mins 8:00 8:15 15 8:15 8:30 15 8:30 8:45 15 8:45 9:00 5
If they logged in at 8:03, I would need it to look like this:
Start Stop #Mins 8:00 8:15 12 8:15 8:30 15 8:30 8:45 15 8:45 9:00 8
I need to apply this to many different fields, (Scheduled Shift, lunch, breaks, etc... vs Actual Shift, lunch, breaks, etc...) and compare them to deduce Scheduled vs. Actual % Adherence.
Here is some of the code I'm using:
Date(Floor(schedate),'YYYY-MM-DD')&'|'&'I'&agent_ as %DateEmpKey
,agent_&'|'&schedID_ as schShiftKey
,schedID_ as schedID_key
,if(schShiftExc = 46,'Yes','No') as Shift
SELECT schedheader.agent_ AS agent_
,schedheader.date_ as date_
,'20' + SUBSTRING(convert(VARCHAR(7), schedheader.[date_]), 2, 2) + '-' + SUBSTRING(convert(VARCHAR(7), schedheader.[date_]), 4, 2) + '-' + SUBSTRING(convert(VARCHAR(7), schedheader.[date_]), 6, 2) schedate
,schedheader.excode_ AS schShiftExc
,excpt.name_ AS schShiftName
,Convert(VARCHAR(5), DateAdd(Minute, schedheader.startMinute_, 0), 108) AS schShiftStart
,cast(schedheader.length_ as int) AS schShiftlength_
,Convert(VARCHAR(5), DateAdd(Minute, schedheader.length_, 0), 108) AS schShiftDuration
,Convert(VARCHAR(5), DateAdd(Minute, schedheader.startMinute_ + schedheader.length_, 0), 108) AS schShiftEnd
FROM [DB] schedheader
LEFT JOIN [DB] excpt on excpt.code_ = schedheader.excode_
Where schedheader.date_ >= 1170401;
Does this help? Perhaps there is opportunity for me to explain it better.
Yes, I think I understood now a little better your requirement.
So your first task is to distribute an amount across several buckets. You can find some threads here in the forum that show how you can do this, AFAIR it's most often associated with budget distribution.
There are different approaches, here is one possible:
// Note that I create a 'minutes from daystart' based on the time, I think you already start with this type of data
LOAD *, RecNo() as RecID, Num#(Interval(Start,'mm')) as StartMinute INLINE [
// Create a record per minute of the task (RecID) using a WHILE loop, then create 15 min buckets using Floor and
// aggregate the expanded records down to the bucket granularity
LOAD RecID, IntervalMinute, Time(IntervalMinute/ 1440) as Interval, Count(RecID) as DurationInterval
GROUP BY RecID, IntervalMinute;
LOAD RecID, Floor(StartMinute+Iterno()-1, 15) as IntervalMinute
WHILE IterNo() <= Duration;
RecID IntervalMinute Interval DurationInterval 1 480 8:00:00 AM 15 1 495 8:15:00 AM 15 1 510 8:30:00 AM 15 1 525 8:45:00 AM 5 2 480 8:00:00 AM 12 2 495 8:15:00 AM 15 2 510 8:30:00 AM 15 2 525 8:45:00 AM 8