How to use two queries in one?

[tahasefiani]

Hello,

Today i have this query in my dashboard :

| loadjob savedsearch="myquery"
 |eval FromDate = "2020-01-23"
 |eval ToDate = "2020-01-23"
 | eval ToDateexpir = strptime("2020-01-23", "%Y-%m-%d")
 | eval expiring = ToDate + 86400*4
 | eval expiring = strftime(expiring, "%Y-%m-%d")
 | where (strftime(_time, "%Y-%m-%d") >= FromDate) AND (strftime(_time, "%Y-%m-%d") <= ToDate)
 | stats count(eval(if(MESSAGE="show",STEP,NULL))) AS showed,
 count(eval(if(MESSAGE="send",STEP,NULL))) AS sent,
 count(eval(if(MESSAGE="delete",STEP,NULL))) AS deleted by client |where showed>0
 |stats sum(showed) AS showed,sum(sent) AS sent,sum(deleted) AS deleted
 | eval AVG= round(((showed - (sent+deleted))/showed*100),2)." %"

I want to keep the same query and logic, but for the calculation of the fields "sent" i want it to be from "FromDate" to "expiring"
Thank you 🙂

[to4kawa]

| where (strftime(_time, "%Y-%m-%d") >= FromDate) AND (strftime(_time, "%Y-%m-%d") <= ToDate)

Is this query really work?

[tahasefiani]

| loadjob savedsearch="myquery"
|eval FromDate = "2020-01-23"
|eval ToDate = "2020-01-23"
| eval ToDateexpir = strptime("2020-01-23", "%Y-%m-%d")
| eval expiring = ToDate + 86400*4
| eval expiring = strftime(expiring, "%Y-%m-%d")
| where (strftime(_time, "%Y-%m-%d") >= FromDate) AND (strftime(_time, "%Y-%m-%d") <= ToDate)
| stats count(eval(if(MESSAGE="show",STEP,NULL))) AS showed,
count(eval(if(MESSAGE="send",STEP,NULL))) AS sent,
count(eval(if(MESSAGE="delete",STEP,NULL))) AS deleted by client |where showed>0
|stats sum(showed) AS showed,sum(sent) AS sent,sum(deleted) AS deleted
| eval AVG= round(((showed - (sent+deleted))/showed*100),2)." %"