Conditional count in stats function

[willial]

I need to do the following:

Get a distinct count of serial numbers where a selected date falls within a particular range. Figuring out if a serial number is valid per table row is easy:

eval inService=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),1,0)

The problem is I want to sum up all of these 1s but only once for each distinct serial number (a serial number can appear in the table multiple times).

Currently my stats line looks like this:

stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass

I'm trying to get a ratio between sumQuarter (the amount something is used) and countSerNum (the number of things available for use). This is set up so that I can look at any historical quarter for sumQuarter, but sadly dc(serialNumber) only gives me the current count, not the historical count for the selected timeframe.

This is coming from a database query, so I can't just restrict the time range ahead of the search. I want to do something like this:

dc(eval if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),serNum))

Obviously this doesn't exactly work.

[somesoni2 · Accepted solution]

May be something like this...

your base search | eval inService=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),1,0) | stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass,inService |eval countSerNum=countSerNum*inService | stats sum(sumQuarter) as sumQuarter max(countSerNum) as countSerNum by modelClass

OR

your base search | eval serialNumber=if(enteredService<beginTime AND (isnull(leftService) OR leftService>beginTime),serialNumber,null())| stats sum(quarterPortion) AS sumQuarter dc(serialNumber) AS countSerNum by modelClass

[willial]

I think #2 is working properly (as far as I can tell). I'll revisit this if I discover I'm wrong. Otherwise, high marks for cleverness.