MSSQL IN Statement in WHERE clause no results

Queries and Resources
, ,
1

Goal:
I’m trying to use a SQL query in Retool to group data by camera_id and the hour of the day. The expected result is to retrieve the total number of visitors (in and out) for each camera and each hour within a specific date range.

Steps:
I’ve set up a multi-select component (select_Kamera) to allow users to choose multiple camera IDs. However, the query doesn’t seem to return any data, and I’m struggling to figure out why.

Here’s the query I was initially using:

SELECT 
    DATEPART(HOUR, timestamp) AS hour_of_day,
    SUM([in]) AS total_in,
    SUM([out]) AS total_out
FROM 
    visitor_count
WHERE 
    CAST(timestamp AS DATE) BETWEEN {{ select_Datumrange.value.start }} 
    AND {{ select_Datumrange.value.end }} 
    AND camera_id IN ({{ select_Kamera.value.map(id => `'${id}'`).join(',') }}) 
GROUP BY 
    camera_id, DATEPART(HOUR, timestamp)
ORDER BY 
    hour_of_day;

I suspect the issue is coming from the way I’m trying to pass the camera_ids from the multi-select, but I can't seem to figure out how to resolve it.

i have read about the "ANY" from here-> Using SQL IN statement within Query Library - Queries and Resources - Retool Forum
Which isnt working for me sadly - probably due to using MSSQL and not postgres

Any guidance on how to better handle multi-select values in Retool’s query builder, or whether there’s a better way to pass arrays to SQL queries, would be greatly appreciated!

2

** the query shown in the state field is:
query
"SELECT DATEPART(HOUR, timestamp) AS hour_of_day, SUM([in]) AS total_in, SUM([out]) AS total_out FROM visitor_count WHERE CAST(timestamp AS DATE) BETWEEN 2024-10-15 AND 2024-10-29 AND camera_id IN (${id},${id}) GROUP BY camera_id, DATEPART(HOUR, timestamp) ORDER BY hour_of_day;"

3

funny.. i have just found the cause of the problem.
So rather then deleting the topic i post the answer.
MSSQL is not able to handle arrays in an IN statement because IN is not a function its an operator. (All explained here -> Arrays and Lists in SQL Server (Short version) )

So the working query looks like this:

SELECT 
    DATEPART(HOUR, timestamp) AS hour_of_day,
    SUM([in]) AS total_in,
    SUM([out]) AS total_out
FROM 
    visitor_count
WHERE 
    CAST(timestamp AS DATE) BETWEEN {{ select_Datumrange.value.start }} 
    AND {{ select_Datumrange.value.end }}
    AND camera_id IN (
        SELECT value FROM string_split({{ select_Kamera.value.join(',') }}, ',')
    )
GROUP BY 
 DATEPART(HOUR, timestamp)
ORDER BY 
    hour_of_day;
1 Like