Query Based on a Value Between Two Date Columns

pstrauss

I've got a data set that includes job tickets with create_date and last_update columns. If the date range for a card is set to include a date in between the create_date and last_update, the ticket should be considered "open" at that point in time, and any tickets outside of the date range should be considered "closed" at that point in time. The goal is a report that shows the number of "open" tickets based on a given date range (i.e., the number of open tickets in March 2023).

Can you think of a way this could be accomplished in Domo?

MarkSnodgrass

You could use a variable that would allow the user to select a date. You would then create a beast mode that creates the open/closed logic. Something like this:

CASE when variablefield >= create_date and varialbefield <= last_update then 'Open'

ELSE 'Closed'

END

You would then put this beast mode in your filters section and either filter to open, or make it a quick filter and allow people to choose which is open or closed. Or just drag it in as another field on your card.

MarkSnodgrass

you can use the date that they select with the variable and use some functions in your beast mode to apply to the entire month.

This would get you the first day of the month:

DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)

This would get you the last day of the month

LAST_DAY(`dt`)

You can use these functions while comparing to your create and last updated dates.

MarkSnodgrass

You could use a variable that would allow the user to select a date. You would then create a beast mode that creates the open/closed logic. Something like this:

CASE when variablefield >= create_date and varialbefield <= last_update then 'Open'

ELSE 'Closed'

END

You would then put this beast mode in your filters section and either filter to open, or make it a quick filter and allow people to choose which is open or closed. Or just drag it in as another field on your card.

pstrauss

@MarkSnodgrass thanks for the quick reply. Can you think of a way to use this approach to query for a date range? So not just all open tickets on a specific date, but open tickets during a date range (i.e. 3/1/2023 to 3/31/2023)

MarkSnodgrass

you can use the date that they select with the variable and use some functions in your beast mode to apply to the entire month.

This would get you the first day of the month:

DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)

This would get you the last day of the month

LAST_DAY(`dt`)

You can use these functions while comparing to your create and last updated dates.

pstrauss

@MarkSnodgrass thanks for the suggestions. I'll give this a shot and see if I can make it work.