We have component monitors for our O365 licenses, however I've been asked to create a daily report that lists the change in the number of assigned licenses (License count at first poll of the day minus license count at last poll of day). Min/Max is not an accurate representation when the consumed licenses roller coaster throughout the day. Pretty sure nested select statements is the answer, but my SQL skills are fairly basic still.
SELECT (MAX(a.StartOfDay) - MAX(a.EndOfDay)) AS License_Diff
FROM (
Select
StartOfDay = MIN(CASE WHEN DateTime <= GETDATE() THEN AvgNumericData END), /* This part is not correct */
EndOfDay = MAX(CASE WHEN DateTime <= GETDATE() THEN AvgNumericData END) /* This part is also not correct */
FROM APM_AlertsAndReportsData INNER JOIN APM_MultiValueReportsData ON (APM_AlertsAndReportsData.ComponentId = APM_MultiValueReportsData.ComponentID)
WHERE (APM_MultiValueReportsData.DateTime >= DATEADD(day, -1, convert(date, GETDATE())) AND
(APM_MultiValueReportsData.DateTime < convert(date, GETDATE()))) AND
(APM_MultiValueReportsData.ComponentID = 51016) AND NOT /* License component */
(APM_MultiValueReportsData.AvgNumericData = 0) /* remove the polls that return 0, msonline powershell fun */
) a
It's close, but it's still just returning the Min/Max of the licenses throughout the day, not the net license change of the previous day.
