SuiteQL question. Is there a trick to group by transaction.createdDate? I keep getting an unsupported query error. Can provide query if needed but it’s based off of a custom_transaction that’s referenced in the transactionline.

not sure if this is your issue, but createdDate will be a timestamp so not much will group by the millisecond

Sorry, I was typing on my phone and wasn’t at my computer. Here’s the SQL:

SELECT DISTINCT
	b.id as con_internal_id,
	b.tranid as con_tranid,
	b.custbody_division_id as div_contract_id,
	TO_CHAR(b.createddate, 'MM/DD/YYYY') as con_created_date,
	BUILTIN.DF(b.custbody_contract_type) as contract_type,
	BUILTIN.DF(a.subsidiary) as subsidiary,
	BUILTIN.DF(b.custbody_vendor) as vendor,
	b.trandate as contract_start_date,
	b.custbody_end_date as contract_end_date,
	b.custbody_amount as con_NTE,
	SUM(a.netamount)
FROM 
	TransactionLine a
LEFT OUTER JOIN 
	Transaction b 
ON
	a.custcol_contract_tran = b.id AND b.recordType = 'customtransaction_contract'
WHERE
	a.custcol_contract_tran IS NOT NULL
GROUP BY
	b.id, 
	b.tranid, 
	b.custbody_division_id, 
	TO_CHAR(b.createddate, 'MM/DD/YYYY')
	BUILTIN.DF(b.custbody_contract_type), 
	BUILTIN.DF(a.subsidiary), 
	BUILTIN.DF(b.custbody_vendor), 
	b.trandate, 
	b.custbody_end_date, 
	b.custbody_amount

yeah i got nothing man, sorry 😕

have you tried without the distinct keyword? I think that might cause errors sometimes with grouped queries.

@Michael Why are you doing distinct? When I did the dates as Anthony suggested and removed distinct per rtanner, I was able to get it working

select
	b.id as con_internal_id,
	b.tranid as con_tranid,
	BUILTIN.DF(a.subsidiary) as subsidiary,
	TO_CHAR(b.createddate, 'MM/DD/YYYY'),
	TO_CHAR(b.trandate, 'MM/DD/YYYY'),
from transaction b
inner join transactionLine a 
	on a.transaction = b.id
where
 b.trandate > '8/1/2024' AND b.trandate < '9/7/2024'
GROUP BY
b.id,
b.tranid,
BUILTIN.DF(a.subsidiary),
TO_CHAR(b.createddate, 'MM/DD/YYYY'),
TO_CHAR(b.trandate, 'MM/DD/YYYY')

Ah, that did it, thanks!