Create Widget that Passes Date to Query in Azure Databricks

Question

I need to create a widget in Azure Databricks that allows me to pass a date to a query.

The MSSQL version of this code is (note I have simplified the problem greatly, this is part of larger query where I am attempting to avoid a self join):

DECLARE @MAXstartdate DATE = (SELECT MAX(startdate) FROM table
SELECT *
FROM table
WHERE startdate = @MAXstartdate

I've tried:

SET mbr.MAX = (SELECT MAX(startdate) FROM table);

CREATE WIDGET mbr.MAX DEFAULT SELECT MAX(startdate) FROM table

However Azure turns an error on both of these.

Answer 1

I tried to replicate the issue in my environment with some sample data:

create table sampletable(Id int , startdate Date)
insert into sampletable(id,startdate) values(1, '2023-04-04'),(2,'2023-04-23'),(3,'2023-04-12')

with your code:

DECLARE @MAXstartdate DATE = (SELECT MAX(startdate) FROM sampletable
SELECT *
FROM sampletable
WHERE startdate = @MAXstartdate

I got below error:

I resolved the issue by following code:

SELECT *FROM sampletable
WHERE startdate = (SELECT MAX(startdate) FROM sampletable)

I created a widget with the result of above query using below code:

CREATE WIDGET DROPDOWN maxdate DEFAULT "2023-04-23"  CHOICES SELECT  MAX(startdate) FROM sampletable ;

I was able to access the widget value using below code:

SELECT * 
FROM sampletable
WHERE startdate = getArgument('maxdate')