Using WHERE, CASE, AND, LIKE together in the same SQL query

Question

I'm having trouble coming up with the right query that does the following: Let's say we somehow take input from user for the value of two columns, datestamp and brand (what happens is a Looker UI feeds this to the query). For simplicity let's say our schema includes both columns and I only want to get them back, so:

SELECT brand, datestamp
FROM my_table
WHERE

I'm not sure how I should write the rest. Here's what's desired:

• The user be able to type yesterday, today, last x days for some x or a string like 20181001 and the right date gets passed to the query.

• The user be able to specify what brand should be equal to and that gets passed to the query.

  ( CASE
      WHEN position(' TO ' IN UPPER({% parameter filter_datestamp %})) > 0 THEN
              datestamp >= SUBSTR({% parameter filter_datestamp %}, 0, strpos(UPPER({% parameter filter_datestamp %}), ' TO ') - 1) AND
              datestamp <= SUBSTR({% parameter filter_datestamp %}, strpos(UPPER({% parameter filter_datestamp %}), ' TO ')  + 4)
              AND
              brand LIKE {% parameter filter_brand %}
  
      WHEN filter_datestamp = 'today' THEN datestamp = date_format(CURRENT_DATE,'yyyyMMdd')
      AND brand LIKE {% parameter filter_brand %}
  
  
      WHEN datestamp = 'yesterday' THEN datestamp = date_format(DATE_ADD('day', -1, CURRENT_DATE),'%Y%m%d')
      AND brand LIKE {% parameter filter_brand %}
  
      WHEN datestamp LIKE 'last % days' THEN datestamp >= date_format(DATE_ADD('day', -CAST(split({% parameter filter_datestamp %}, ' ')[2] AS INT), CURRENT_DATE),'%Y%m%d')
      AND brand LIKE {% parameter filter_brand %}
  
      ELSE datestamp LIKE {% parameter filter_datestamp %}
      AND brand LIKE {% parameter filter_brand %}
  
    END )
  

If the query works, the result should be a number of rows with brand and datestamp equal to what the user wants. All rows will be identical (I actually want something else but think I can do that if the CASE is resolved).

Please write the complete query for me. I'm not sure where WHERE goes with respect to CASE, WHEN, AND, and other keywords present in this case.

Answer 1

In Looker, I think this can all be done fairly simply just by using templated filters instead of parameters like you have now.

Basically, templated filters automatically apply the condition chosen to the query, so instead of having to explicitly account for all the possible forms of user input with the CASE statements you currently have, you could just have

{% condition filter_datestamp %} datestamp {% endcondition %}

That one line can replace all of the datestamp case statements-- You can include the brand filter as well by adding it on to the end like:

 {% condition filter_datestamp %} datestamp {% endcondition %} 
 AND brand LIKE {% parameter filter_brand %}

That should write the SQL desired! If you input "foo" for filter_brand and "yesterday" for filter_datestamp, it will write:

SELECT brand, datestamp
FROM my_table
WHERE ((( datestamp ) >= ((DATE_ADD(CURDATE(),INTERVAL -1 day))) AND ( datestamp ) < ((DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -1 day),INTERVAL 1 day)))))
 AND brand LIKE 'foo'

The full LookML to have that as a derived table (what I guessed you were trying to do) looks like

view: test {
  derived_table: {
    sql:
    SELECT brand, datestamp
    FROM my_table
    WHERE {% condition filter_datestamp %} datestamp {% endcondition %}
     AND brand LIKE {% parameter filter_brand %};;
  }


  filter: filter_datestamp {
    type: date
  }

  filter: filter_brand {
    type: string
  }

  dimension: brand {
    type: string
    sql: ${TABLE}.brand ;;
  }
  dimension: datestamp {
    type: date
    sql: ${TABLE}.datestamp ;;
  }
}

Let me know if that scans or if there's anything that doesn't make sense about that! Also, in the future I'd invite you to come post on our Community Forums at discourse.looker.com :) We've got lots of Looker Experts there who'd love to answer stuff like this.

Answer 2

This could be your Query:

SELECT brand, datestamp   FROM my_table
 WHERE (DATE (datestamp) = DATE (NOW ())
     OR DATE (datestamp) >= DATE_SUB (DATE (NOW ()), INTERVAL 'x' DAY) DATE(NOW()))
       AND brand = '<>';

but for this u will have to pas x every time. for today pass 0 for yesterday pass 1 for last 'x' pass that 'x'