How to use a variable in a LIKE query

Get Help
1

I have a column of string kpi_date, which follows the format of dd-mm-yyyy. I need to group the table on to each kpi_date within the month. I tried to use a LIKE query this way:

SELECT  
        kpi_date, sum(tickets)
FROM    
        database
    WHERE 
        client_id = {{client_id}} AND kpi_date LIKE "%"||{{month}}||"%"
GROUP BY   
        kpi_date

With the input variable being month (example: ‘11-2019’, ‘12-2019’, etc.)

The result is that Metabase printed out every single kpi_date within every month, regardless of what month I inputted to it. However, when I tried changing the related query to:

... AND kpi_date LIKE "%12-2019%"

it successfully printed out each kpi_date within that specific month.

What am I doing wrong with my variable usage?

2

Hi @sagungrp
That’s because you group by kpi_date.
I don’t know what database your querying, but you need to change kpi_date to something that only returns the year and month, like LEFT(kpi_date, 7)
You should really consider have a real date type instead of using a string.
You can probably find better help on stackoverflow.com or similar sites, since it’s not really specific to Metabase.