?
Solved

Doing two kinds of calculations in 1 SQL command

Posted on 2011-03-18
3
Medium Priority
?
224 Views
Last Modified: 2012-05-11
Is there a way to incorporate two kinds of calculations in one  SQL commands?
What I mean is this:
I have to print invoices based on an InvoiceDetails table.
Most clients have to pay TVA, some don’t.  In my Clients table I have a Boolean field named BlnPayTax. This is set to 'True' for the clients paying TVA and to 'False' for all others.

Simplified my current SQL-command looks like this:

SELECT       ClientId, SUM(Price) AS WithoutTVA, SUM(Price * 0.21) AS TVA
FROM          InvoiceDetails
INNER JOIN Clients
ON      InvoiceDetails.ClientId = Clients.ClientId
GROUP BY   ClientId
This calculates the TVA for all Clients.  Then In a second step I UPDATE the results, setting all TVA’s = 0 where Clients.BlnPayTax = ‘False’.

What would be nice is a command that calculates TVA in case BlnPayTVA is true en does not calculate TVA in all other cases.
Is this possible?
0
Comment
Question by:NoraWil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Expert Comment

by:sgvill
ID: 35165176
try something like this:

SELECT       ClientId, CASE WHERE Clients.BlnPayTax = ‘True' THEN SUM(Price)  ELSE 0 END AS TVA
FROM          InvoiceDetails
INNER JOIN Clients
ON      InvoiceDetails.ClientId = Clients.ClientId
GROUP BY   ClientId

Open in new window

0
 
LVL 4

Accepted Solution

by:
sgvill earned 1500 total points
ID: 35165197
oh, that didn't show up too well... try this

SELECT       ClientId, SUM(Price) AS WithoutTVA,  CASE WHERE Clients.BlnPayTax = 'True' THEN SUM(Price * 0.21)  ELSE 0 END AS TVA
FROM          InvoiceDetails
INNER JOIN Clients
ON      InvoiceDetails.ClientId = Clients.ClientId
GROUP BY   ClientId
0
 

Author Closing Comment

by:NoraWil
ID: 35166404
Thanks for your help.
I had to change the syntax a little bit to make it work
This works:
SELECT       ClientId, SUM(Price) AS WithoutTVA, TVA =
                   CASE WHEN Clients.BlnPayTax= 'True'        
                   THEN SUM(Price * 0.21)  
                   ELSE 0
                   END
FROM          InvoiceDetails
INNER JOIN Clients
ON      InvoiceDetails.ClientId = Clients.ClientId
GROUP BY   ClientId

I changed AS TVA to TVA =
and WHERE to WHEN

But your solution helped me to find the solution. Thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question