Jump to content

Need help for Case statement


Recommended Posts

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

So the current code is using case statement.

SUM(sblPOAmount) AS totalPOAmt,
    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))
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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.