Multiple 'Where' conditions

[sweety1309]

Hi I have the below query.But its output is "no results found".I dont know what mistake am I making.Please help

index="entab_due" Session=2019 ClassName="* *"
| join type=outer AdmissionNo, FeeInstallmentName, Session
[search index="entab_collection"]
| eval start = strptime(DueDate, "%d/%m/%Y")
| eval end = strptime(RecDate, "%d/%m/%Y")
| eval duration = round((end-start)/86400)
| where (duration > 45 and duration <= 75) AND (duration > 105 and duration <= 120) AND (duration > 120 and duration <= 180) AND (duration > 180)
| table AdmissionNo,StudentName

[danspav]

Hi @sweety1309 

I think the issue is with the where clause. 

Say the duration is 108.

The where clause will not match on:  (duration > 45 and duration <= 75)

But will match on:  (duration > 105 and duration <= 120)

However, the where clause uses AND between these terms, so the duration must be both less than 75 AND greater than 105.

Try replacing your ANDs with ORs:

| where (duration > 45 AND duration <= 75) OR (duration > 105 AND duration <= 120) OR (duration > 120 AND duration <= 180) OR (duration > 180)

Seeing as you don't use the duration field, you could simplify it further:

| where (duration > 45 AND duration <= 75) OR (duration > 105)

[sweety1309]

I need common data which occurs in all the duration set thats why i m using AND here

[renjith_nair]

Possibly conditions in the where clause is negating each other.

What's your expected result?

[sweety1309]

I need all data which is available in all three durations set

[renjith_nair]

try OR instead of AND

[sweety1309]

but OR will not give me the common data which occurs in all the duration set.I need common data

[renjith_nair]

As mentioned earlier, conditions are negating each other.

For e.g. take first and last condition

sample adata :

duration = 1, 2,55,160,180

condition:

where (duration > 45 and duration <= 75)

AND (duration > 180)

In the above case, first condition will result in 55 and will be negated by AND in the second condition which > 180. So you wont get any result

sample search

|makeresults|eval duration="1 2 55 160 180 200"|makemv duration|mvexpand duration
| where (duration > 45 and duration <= 75)
 OR (duration > 105 and duration <= 120) 
 OR (duration > 120 and duration <= 180) 
 OR (duration > 180)

If this is not your requirement, please provide sample input duration and expected output