How to Use Between and Not Between in Kusto Query | Kusto Query Language Tutorial (KQL)

Topic:  How to Use Between and Not Between in Kusto Query Language

In this article, we are going to learn about the Between operator, between operator filters the record set for data matching and the values in an exclusive range, so you can provide the range, and then it will give you the required data after filtering that between can operate on any numeric date time and the time span expressions, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.


 //Between - Filters a record set for data matching the values in an inclusive range.  
 //between can operate on any numeric, datetime, or timespan expression.  
   
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary:int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
   
 //use between to get number range value  
 Customer  
 | where Salary between(int(1000) .. int(4000))  
 | project CustomerId, FName, LName, Salary, hiredate  
   
 //you can use toint  
 Customer  
 | where Salary between (toint(1000)..toint(4000))  
   
   
 //use datetime range  
 Customer  
 | where hiredate between(todatetime("2015-01-20T04:52:43.8745893Z") .. todatetime("2021-12-20T04:52:43.8745893Z"))  
 | project CustomerId, FName, LName, Salary, hiredate  
   
   
 //between startdate and today  
 Customer  
 | where hiredate between(todatetime("2010-01-20T04:52:43.8745893Z") .. now())  
 | project CustomerId, FName, LName, Salary, hiredate  
   
   
 //Using Not between  
 Customer  
 | where hiredate !between(todatetime("2015-01-20T04:52:43.8745893Z") .. now())  
 | project CustomerId, FName, LName, Salary, hiredate  

Video Demo: How to Use Between and Not Between in Kusto Query | Kusto Query Language Tutorial (KQL)

2 comments: