How to count range of dates?

[ositaumeozulu]

hello team

please i need solution to these question

i have three column fields, startDate,endDate, ARTstartDate. i want to count the number of ARTstartDate that falls inbetween the two dates (i.e from startDate to endDate)

[yuanliu]

Assuming all fields use the format '2022-06-08' (%F, or %Y-%m-%d), it can be as simple as

| eval nARTStartDate = strptime(ARTstartDate, "%F")
| where strptime(startDate, "%F") < nARTstartDate AND nARTstartDate < strptime(endDate, "%F")

then perform a count.

[ositaumeozulu]

@yuanliu 

| eval ASD = strptime(ARTStartDate, "%m/%d/%Y")
| where strptime('10/1/2021', "%m/%d/%Y") < ASD AND ASD < strptime('5/27/2022', "%m/%d/%Y")
| stats count(ASD) as "Tx_new" by FacilityName

i used the above code as directed, still no result, is there a way i can eval the where clause as a separate field so i can run a count on it

[yuanliu · Accepted solution]

Before you run stats, try observe actual data fields including extracted fields.  You would notice that no events were left after "where".  In this case, instead of using strptime() directly in where command, eval them into variables.  You'll see that the strptime() doesn't give you any value.

---

| where strptime('10/1/2021', "%m/%d/%Y") < ASD AND ASD < strptime('5/27/2022', "%m/%d/%Y")

That's because SPL uses double quotes to delimit literal strings; single quote is used to delimit field names.  If you change to

| where strptime("10/1/2021", "%m/%d/%Y") < ASD AND ASD < strptime("5/27/2022", "%m/%d/%Y")

there should be some events. (If your data satisfies the condition, of course.)

[ositaumeozulu]

@yuanliu 

wow, just wow, many thanks dear. code is working perfectly

i really appreciate