Query Based on a Value Between Two Date Columns

pstrauss
pstrauss Member
edited April 2023 in Beast Mode

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?

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pstrauss
    pstrauss Member

    @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
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pstrauss
    pstrauss Member

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

Categories