Dateadd in select clause together with grouping causes problems

Question

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.

Answer 1

In the SELECT clause, you have

case when dateadd(day, NetCashDays, InvoiceDate) < getdate() then 1 else 2 end as InvoiceStatus,

In the GROUP BY clause you have

case when dateDIFF(day, NetCashDays, NETCASHDAYS) < getdate() then 1 else 2 end

(I capitalized the differences). Since they are different, you get the error. To fix this you must make them the same - my guess is that you want the GROUP BY clause to look like the SELECT clause.

Tom

Answer 2

Hi @Nihad Abou-Zid

I guess I could use a temporary table or CTE for this but I don't think it is necessary here.

I don't quite agree with you. Using CTE makes code more readable. And readability makes queries easier to debug.
Also, rewriting your original query into this CTE format will not affect execution and sometimes it may help enhance query performance.

;WITH CTE AS  
(  
 SELECT dateadd(month, datediff(month, 0, InvoiceDate), 0) as PeriodStart,  
        case when dateadd(day, NetCashDays, InvoiceDate) < getdate() then 1 else 2 end as InvoiceStatus,  
        GrossPrice  
 FROM tbl_Invoice  
)  
SELECT PeriodStart,InvoiceStatus,sum(GrossPrice) as TurnoverSum   
FROM CTE  
GROUP BY PeriodStart,InvoiceStatus  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.