Using Date Filter with sql pivot

Get Help
1

I have some code which works to pivot my output (via the sql query rather than using metabase pivot) until I enter a dynamic date range using date filters. When I do this I get a 'The index 3 is out of range.' error. Is this a limitation of Metabase or am I missing something?
I can enter some example code if anyone thinks they have a solution.
Thanks

2

please post diagnostic info and sql so we can test

3

Here is the sql so you can test - thanks
I've used a '&' instead of an 'at' character as this was not allowing me to reply (saying too many users)

DECLARE &columns NVARCHAR (MAX ) = '' ,
&query NVARCHAR (MAX ) = '' ,
&fromdate date = [[ {{createdOn1}},--]] '2023-01-01',
&todate date = [[ {{createdOn2}};--]] '2023-01-31' ;

with base as (
select
MainID ,
Client,
cast(Debt.CreatedOn as date ) as CreatedOn,
Value
from MainTable
join client as c
on c.ClientID = MainTable.ClientID
where cast (MainTable.createdon as date ) between @fromdate and @todate
)

SELECT &columns+= quotename(client) + ','
FROM (select
distinct Client
from base) as t
;
-- remove the last comma
SET &columns = LEFT (&columns, LEN(&columns) - 1);

set &query = '
DECLARE
&fromdate date = [[ {{createdOn1}},--]] ''2023-01-01'',
&todate date = [[ {{createdOn2}};--]] ''2023-01-31'';

with base as (
select
MainID ,
Client,
cast(Debt.CreatedOn as date ) as CreatedOn,
Value
from MainTable
join client as c
on c.ClientID = MainTable.ClientID
where cast (MainTable.createdon as date ) between &fromdate and &todate
)
select * from
(
select
MainID,
Createdon,
Value,
Client
from base
) t
pivot (
sum(Value)
for Client in ('+ &columns +')

)
as pivot_table;';
Execute (&query)

4

please post diagnostic info

5

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "Cp1252",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "17.0.1+12",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "17.0.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "17.0.1+12",
"os.name": "Windows Server 2016",
"os.version": "10.0",
"user.language": "en",
"user.timezone": "Europe/London"
},
"metabase-info": {
"databases": [
"sqlserver",
"postgres",
"mysql"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "14.0"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.5.0"
}
},
"run-mode": "prod",
"version": {
"date": "2023-02-19",
"tag": "v0.45.3",
"branch": "release-x.45.x",
"hash": "070f57b"
},
"settings": {
"report-timezone": "Europe/London"
}
}
}