Dateadd in select clause together with grouping causes problems

Nihad Abou-Zid 1 Reputation point
2022-11-24T14:18:54.947+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2022-11-24T19:11:53.97+00:00

    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


  2. LiHongMSFT-4306 25,571 Reputation points
    2022-11-25T02:00:49.36+00:00

    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.