Hello @Villa, Carissa,
welcome to this moderated Azure community forum.
Better questions with more information lead to better answers from our community members.
Please provide information about the steps you have taken, the guide you follow, and the errors you receive.
Where do you want to compare the dates, in Azure Data Explorer? Have you imported this datasheet in Azure Data Explorer already?
If so, you will have a table like:
.create table shippingtest
(
item: string,
ship_date : datetime,
expected_delivery_date : datetime
)
.create table shippingtest ingestion json mapping "JsonMapping"
'['
' { "column" : "item", "datatype" : "string", "Properties":{"Path":"$.item"}},'
' { "column" : "ship_date", "datatype" : "datetime", "Properties":{"Path":"$.ship_date"}},'
' { "column" : "expected_delivery_date", "datatype" : "datetime", "Properties":{"Path":"$.expected_delivery_date"}}'
']'
.ingest inline into table shippingtest with (format = "json", ingestionMappingReference = "JsonMapping") <|
{"item": "item1", "ship_date": "2024-02-17", "expected_delivery_date": "2024-02-20" }
{"item": "item2", "ship_date": "2024-02-18", "expected_delivery_date": "2024-02-20" }
{"item": "item3", "ship_date": "2024-02-19", "expected_delivery_date": "2024-02-20" }
{"item": "item4", "ship_date": "2024-02-20", "expected_delivery_date": "2024-02-20" }
{"item": "item5", "ship_date": "2024-02-29", "expected_delivery_date": "2024-02-26" }
As you can see, I already added a few rows.
You can query the data for success or failure like this:
shippingtest
| extend shippingSuccess = ship_date < datetime_add('day', -1, expected_delivery_date)
| order by ship_date asc
Here I extend the table with an extra (Boolean) column but the equation could have been a 'where clause' too.
Notice how 1 day is subtracted using "datetime_add('day', -1, expected_delivery_date)".
The result looks like this:
So, only the shippings of the first two rows are marked successful.
If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.