Jump to content


Photo

Need help for Case statement

sql sqlserver case

  • Please log in to reply
1 reply to this topic

#1 FooKelvin

FooKelvin
  • Members
  • PipPipPip
  • Advanced Member
  • 141 posts

Posted 17 July 2017 - 03:11 AM

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



#2 Psycho

Psycho
  • Moderators
  • Advanced Member
  • 11,822 posts
  • LocationCanada

Posted 17 July 2017 - 03:50 PM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users