All the work item data for our team gets moved to in Azure Data Explorer. We wanted to build a dashboard on top of that data to help us improve our efficiency. The first step was to figure out how long each work item took to complete.
Calculating the delta between the start date and end date of the work item was a reasonable start, but it isn't very precise since it includes weekends. There's already a dayofweek() function but first I have to use mv-expand. That operator makes a row for the range of dates between StartDate and EndDate for every work item id. Then I can extend a column based on dayofweek() and aggregate back to one row per work item id.
let workItemData =
datatable(WorkItemId:long, StartDate:datetime, EndDate:datetime)[
1000, datetime(2019-02-01), datetime(2019-02-09),
1001, datetime(2019-02-09), datetime(2019-02-15),
1002, datetime(2019-02-11), datetime(2019-02-15),
1003, datetime(2019-01-16), datetime(2019-02-27)
]
;
let expandedDays =
workItemData
// create a new dynamic column with the array of dates between StartDate and EndDate
| extend AllDays = range(StartDate, EndDate, 1d)
// expand out to a single row for every value in the array for each work item
| mv-expand SingleDay=AllDays to typeof(datetime)
;
// find the earliest and latest date in the whole list
let minDate = toscalar(expandedDays | summarize min(SingleDay));
let maxDate = toscalar(expandedDays | summarize max(SingleDay));
expandedDays
| join kind = leftouter (
// build a list of all the possible days in the timerange
range SingleDay from startofday(minDate) to maxDate step 1d
// determine the day of week for each date
| extend IsWeekday = dayofweek(SingleDay)/1d % 6 != 0
) on SingleDay
| project-away SingleDay1
| summarize WeekdayCount=sum(IsWeekday) by WorkItemId, StartDate, EndDate
|
WorkItemId
|
StartDate
|
EndDate
|
WeekdayCount
|
|
1003
|
2019-01-16 00:00:00.0000000
|
2019-02-27 00:00:00.0000000
|
31
|
|
1000
|
2019-02-01 00:00:00.0000000
|
2019-02-09 00:00:00.0000000
|
6
|
|
1001
|
2019-02-09 00:00:00.0000000
|
2019-02-15 00:00:00.0000000
|
5
|
|
1002
|
2019-02-11 00:00:00.0000000
|
2019-02-15 00:00:00.0000000
|
5
|
This was a useful bit of code that I wanted to store as a function. The invoke operator was the key because it lets me pass tabular parameter arguments. In the parameter list, I can optionally specify the minimum schema that the incoming rowset must adhere to so that I can safely use those fields in the function.
.create function MarkWeekDays(T:(StartDate:datetime,EndDate:datetime)) {
let expandedDays =
T
| extend AllDays = range(StartDate, EndDate, 1d)
| mv-expand SingleDay=AllDays to typeof(datetime)
;
let minDate = toscalar(expandedDays | summarize min(SingleDay));
let maxDate = toscalar(expandedDays | summarize max(SingleDay));
expandedDays
| join kind = leftouter (
range SingleDay from startofday(minDate) to maxDate step 1d
| extend IsWeekday = dayofweek(SingleDay)/1d % 6 != 0
) on SingleDay
| project-away SingleDay1
}
The final summarize could be included in the function, but inside that function, I don't want to lose any extra columns that the tabular parameter might contain. Using that function now looks like this:
let workItemData =
datatable(WorkItemId:long, StartDate:datetime, EndDate:datetime)[
1000, datetime(2019-02-01), datetime(2019-02-09),
1001, datetime(2019-02-09), datetime(2019-02-15),
1002, datetime(2019-02-11), datetime(2019-02-15),
1003, datetime(2019-01-16), datetime(2019-02-27)
]
;
workItemData
| invoke MarkWeekDays()
| summarize WeekdayCount=sum(IsWeekday) by WorkItemId, EndDate
Now I have an easy-to-use function which can calculate the number of week days between dates in two fields. When doing work like this, it's easy to get stuck trying to call a function for every row and use values from the row for parameters to the function. If that happens, use mv-expand to operate on all the rows at the same time and that will often unblock the scenario.
