Jump to content
#StayAtHome ×
FooKelvin

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.

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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

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

Guest
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.