Hello,
I have following query that calculates some turnoverData based on an invoiceTable. This is a simplified version of my query which still leads to the same error. Basically the query calculates a sum on some grouped data. The grouping is done dynamically per month/day/year/week - PeriodStart column. The second column is a derived status depending on the InvoiceDate column (datetime) and and the NetCashDays column (int).
SELECT
dateadd(month, datediff(month, 0, InvoiceDate), 0) as PeriodStart,
case when dateadd(day, NetCashDays, InvoiceDate) < getdate() then 1 else 2 end as InvoiceStatus,
sum(GrossPrice) as TurnoverSum FROM tbl_Invoice
GROUP BY
dateadd(month, datediff(month, 0, InvoiceDate), 0),
case when datediff(day, NetCashDays, NetCashDays) < getdate() then 1 else 2 end
When executing this query in SSMS v18.9.2, SQL Server Microsoft SQL Server 2016 (SP3-GDR) (KB5014355) - 13.0.6419.1 (X64) I get following errors:
Msg 8120, Level 16, State 1, Line 3
Column 'tbl_Invoice.NetCashDays' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'tbl_Invoice.InvoiceDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I replace the dateadd function in the InvoiceStatus column in the select clause it works. The other column does also contain the InvoiceDate column and it does not cause any trouble.
I guess I could use a temporary table or CTE for this but I don't think it is necessary here.