Need help for Case statement

[FooKelvin]

I would like to have this +3 apply when the paymentterm if is less than 45.

So the current code is using case statement.

SELECT
SUM(sblPOAmount) AS totalPOAmt,
case 
    when paymentTerm = '45 Days'
    then DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 3, SBLInvoiceDate)) 
    else DATENAME(Day, DATEADD(day, SUBSTRING(paymentTerm, 1, 2) + 0, SBLInvoiceDate))
end 
FROM 
[A_Sys].[dbo].[Eventtbl] 
WHERE DATENAME(MONTH, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='June' 
AND DATENAME(YEAR, DATEADD(day,SUBSTRING(paymentTerm, 1, 2)+3,SBLInvoiceDate))='2017'
Group By paymentTerm,SBLInvoiceDate

The Sum having multiple result due to "Group By", but if i run it without Group By , it will have error message.

 

Please help

[Psycho]

Not exactly sure what you are trying to achieve, but I think I can provide some explanations of your apparent problems. I see two specific things to address:

 

 

 

The Sum having multiple result due to "Group By", but if i run it without Group By , it will have error message.

 

First, I'm not understanding this statement. A GROUP BY will reduce the number of results. Since you are grouping by "paymentTerm & SBLInvoiceDate", you should get a record for every unique paymentTerm and SBLInvoiceDate combination. If this is not what you want, then you need to tell us what you are trying to achieve.

 

Second, a SUM() function requires a GROUP BY (either explicit or assumed - i.e. ALL records). So, there is a separate problem regarding the CASE statement. The query is GROUPing the records for the purpose of getting the SUM(sblPOAmount) however the CASE statements is trying to return a non-grouped value. This is problematic. Let me provide a simpler example for illustrative purposes. Let's say you have the following data:

 

invoice | item_id | amount | date
  5          11       23     5/16/2017
  5          22      105     5/16/2017
  5          17       42     6/26/2017

 

And you run this query

 

SELECT SUM(amount), date
FROM invoices
GROUP BY inv_id

Since it is GROUPing by inv_id, only one record will be returned. So, what would be the 'date' in the returned record? Your query has the same problem, there can be multiple rows (paymentTerm & SBLInvoiceDate) that are used to create the SUM() and those rows may have different values for paymentTerm & SBLInvoiceDate - which are used in the CASE statement. Therefore it is ambiguous as to how to calculate that value - unless the query is grouped by those fields.