How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Steve Your Own Question
Steve
Steve, Consultant
Category: Programming
Satisfied Customers: 289
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Programming Question Here...
Steve is online now

T-SQL: i'm updating a table with a case expression and then

Customer Question

T-SQL: i'm updating a table with a case expression and then having trouble finding a way to sum a column to get a total amount, would appreciate any input. thanks in advance.
--Declare & Set Variables--
DECLARE @10001 INT = 20
DECLARE @10002 INT = 40
DECLARE @10003 INT = 60
DECLARE @10004 INT = 80
DECLARE @10005 INT = 100
--Update--
UPDATE COA
SET Amount =
CASE
When GL_Account_name = ProductA Then @10001 * @10005
When GL_Account_name = ProductB Then @10001 * @10004
When GL_Account_name = ProductC Then @10001 * @10002
When GL_Account_name = ProductD Then @10001 * @10003
When GL_Account_name = ProductE Then @10001 * @10005
When GL_Account_name = Sales Total Then sum(ProductA+ProductB+ProductC+ProductD+ProductE)
END
---Desired output below
--GL_Account_Name Amount
--Sales_ProductA 120,000
--Sales_ProductB 96,000
--Sales_ProductC 48,000
--Sales_ProductD 72,000
--Sales_ProductE 120,000
--Sales Total 456,000 (this amount = the sum of all the above)
Submitted: 1 year ago.
Category: Programming
Expert:  Steve replied 1 year ago.

Hi there, my name is ***** ***** I can help you with your TSQL problem. There are a number of issues here and it's not clear what you're trying to do, but let me put some things down here and you can chat me back and we'll figure it out.

Ultimately, I think you should be writing a query with GROUP BY to get subtotals and WITH ROLLUP to get your grand total. For example, something similar to the following:

SELECT CASE GROUPING(Account_Name)

WHEN 1 THEN 'Total'

ELSE Account_Name END AS "Account Name",

SUM(Amount) AS "Total"

FROM TABLENAME

GROUP BY Account_name WITH ROLLUP

But I'm not sure what you're trying to do with the multiplication.

So chat me back and we'll figure it out once you explain that to me.

Customer: replied 1 year ago.

Hi Steve,

I am trying to develop a financial projection model, the database links to excel with input and output sheets.

Basically i have 2 SQL tables.

table 1 - stores assumption data used for variable values

eg. @10001 INT = 20

table 2 (COA) - has financial accounting data eg. GL account name etc.

The assumption data in table 1 has a live link to excel which can be changed at anytime. (To try and keep things simple i have left out the code that represents the variable value and used a hard keyed number)

With this in mind I have created a trigger so when a variable value is changed in table 1, the trigger is released and the new variable values goto table 2(COA) to update the financial calculations eg. @10001 * @10005.

Once finished the output in excel is updated to reflect the changes.

This all works except i cant get totals for the Gl accounts hence what i am trying to show you in the above.

Apologies if this isn't clear, its my first attempt.

thank you

Expert:  Steve replied 1 year ago.

What's confusing me are the @10001 * @10005. I don't even think those are valid names? Is that what your query looks like, exactly what you've put down above? Hopefully you can get back online and we'll be here at the same time. I was helping someone else when you sent me this message.

Customer: replied 1 year ago.

@10001 * @10005 represent sales volume assumptions ie.

10001 is the account code for assumption X with a value of 20

10005 is the account code for assumption Y with a value of 100

There for -

'When GL_Account_name = ProductA Then @10001 * @10005'

This means goto column GL_Account_name, find Product A then put the result of calculation @10001 * @10005' in the corresponding record in column 'Amount'

Customer: replied 1 year ago.

Would it be easier if i uploaded the table data and SQl query?

Expert:  Steve replied 1 year ago.

Yes, please!

Customer: replied 1 year ago.

Just answer wont allow me to upload .bak files, any suggestions?

Customer: replied 1 year ago.

Attachment: 2016-05-26_093313_finmodelv1.zip

ok here we go, i zipped it up

Expert:  Steve replied 1 year ago.

Hi there. I can't tell what's in the zip file. It's just a single .bak and I'm not sure what it is?

Customer: replied 1 year ago.

Hi, The .bak is a back up file from SQL server management studio, how would you prefer to receive the table data and query?

Customer: replied 1 year ago.

I've attached the relevant data in an excel spreadsheet including a screenshot of the SQL server Database/table structure.

If you need anything else please let me know.

Thank you

Attachment: 2016-05-26_144317_finmodelv1.xlsx

Expert:  Steve replied 1 year ago.

Hi there, sorry, I can't get it to restore for some reason. The Excel screen print was very helpful. Can you add a column to the COA table for amount and just process the first part of the query by itself to populate that field? I think then you'll be able to see what's happening. You can always remove the column when you're done.

Customer: replied 1 year ago.

here you go..

Attachment: 2016-05-26_182627_finmodelv1.xlsx

Expert:  Steve replied 1 year ago.

I'm not sure what's going on here, I keep putting in comments but they're not appearing in the threaded discussion.

Customer: replied 1 year ago.

mmm, maybe try again as the last message came through ok..

Customer: replied 1 year ago.

Hi, any news?

thnx

Customer: replied 1 year ago.

Hi, are you still there??

Expert:  Steve replied 1 year ago.

Hi there, sorry I was unavailable on Friday.

If you go back to the query I originally provided, I think it gives you what you're looking for after you populate the table with the Amount column. I recreated the data since I couldn't get your backup to restore, and put the data in a table called TABLE1, so you will have to change the name of the table. But the following query produces the results shown in the screen print I attached to this message:

SELECT CASE GROUPING(GL_Account_Name)
WHEN 1 THEN 'Total'
ELSE GL_Account_Name END AS "Account Name",
SUM(Amount) AS "Total"
FROM TABLE1
GROUP BY GL_Account_Name WITH ROLLUP

Expert:  Steve replied 1 year ago.

You can combine the two queries if you don't want to populate the table with the Amount column.Just substitue the calculation for "Amount".

Customer: replied 1 year ago.

ERROR

Customer: replied 1 year ago.

what do you mean

"Just substitue the calculation for "Amount"."?

Expert:  Steve replied 1 year ago.

SET Amount =
CASE
When GL_Account_name = ProductA Then @10001 * @10005
When GL_Account_name = ProductB Then @10001 * @10004
When GL_Account_name = ProductC Then @10001 * @10002
When GL_Account_name = ProductD Then @10001 * @10003
When GL_Account_name = ProductE Then @10001 * @10005
When GL_Account_name = "Sales Total" Then sum(ProductA+ProductB+ProductC+ProductD+ProductE)
END

Expert:  Steve replied 1 year ago.

You did a query before and populated the Amount column in the table. I just used that Amount column in what I provided to you rather than calculating it on the fly. If you don't want to populate the column in the table first, include that part of the query.

I think we had it right from the get-go.

Customer: replied 1 year ago.

Ok, i tried that and whilst it works it appears to work with a SELECT statement it returns an error message with an UPDATE statement which is what i need it for in the TRIGGER.

please see tab 'screenshot with result 1.3' attached.

Attachment: 2016-05-31_135537_finmodelv1.xlsx

Expert:  Steve replied 1 year ago.

The error is because of the sales total line. Try this:

When GL_Account_Name = 'Sales Total' Then Sum(Amount) As 'Sales Total'

Customer: replied 1 year ago.

new message

'Incorrect syntax near the keyword 'AS'.'

ive attached a screenshot of the error message and then scroll down the page for the results screenshot

Attachment: 2016-05-31_144937_finmodelv1.xlsx

Expert:  Steve replied 1 year ago.

OK, sorry:

When GL_Account_Name = 'Sales Total' Then Sum(Amount)

Customer: replied 1 year ago.

looks like aggregates can't be used in an UPDATE..

please see error message below..

''An aggregate may not appear in the set list of an UPDATE statement.'

Thank you

Expert:  Steve replied 1 year ago.

But you got it to work before? I'm trying to find the exact place in our discussion. I asked you to run the first part of the query and update the Amount field in the table, and that worked. Then, this query will get you what you want from your original question:

WHEN 1 THEN 'Total'
ELSE GL_Account_Name END AS "Account Name",
SUM(Amount) AS "Total"
FROM TABLE1
GROUP BY GL_Account_Name WITH ROLLUP

Customer: replied 1 year ago.

The original post I displayed was an UPDATE statement and your code used a SELECT statement as per below. It needs to sum up the total of the Amount column within an UPDATE statement.

SELECT CASE GROUPING(Account_Name)

WHEN 1 THEN 'Total'

ELSE Account_Name END AS "Account Name",

SUM(Amount) AS "Total"

FROM TABLENAME

GROUP BY Account_name WITH ROLLUP

Thank you

Expert:  Steve replied 1 year ago.

Is it not an option to have it first do the UPDATE and then the SELECT right after?

An UPDATE statement is a statement to update the data in the database. It's not a statement you use to create a report or a summary of data in the database. The UPDATE statement has an OUTPUT option, but that's mainly to summarize what happened during the update.

A SELECT statement is what you use to get data from the database, summarize it and report it. The SELECT statement includes options for you to group and summarize the data you select.

So the best option would be to first perform the UPDATE to update the data in the database, and then issue a SELECT statement to extract that data, summarize it and present the updated data the way you want to see it.

You can see the conflict in the Sales Total part of your query. What you want to do is to sum the Amount field for ProductA, ProductB, ProductC, ProductD and ProductE. You can't sum ProductA+ProductB+ProductC+ProductD+ProductE because those are GL_Account_Names, not Amounts. But you're updating the amount field so you can't summarize it yet. Does that make sense?

I included links to the documentation for UPDATE and SELECT. This stuff is really confusing, so I hope I've explained my reasoning so that it makes sense. If it doesn't or that's not an option, we can find a solution!

Steve and other Programming Specialists are ready to help you
Customer: replied 1 year ago.

Thanks, ***** ***** the late reply.

In the end i discovered that using the UPDATE then SELECT like you suggested resulted in the totals which is what i was after however when an assumption was changed the output didnt change.

So i found that after the UPDATE i dropped the table then with SELECT INTO i recreated it with GROUP BY and the everything worked.

thanks for your help and patience.

I have a couple more steps i need help with on this, can call on your help again?

Customer: replied 1 year ago.

Hi Steve,

I've decided to take a different approach to the totals.

Screenshot attached.

What i need to to do is..

1. In row 6, Sales Total, GL_Account_No 11005, currently = null in the 'Amount' column.

NULL needs to be replaced with the sum total amount where GL_Account_No = 11000, 11001, 1002, 11003, 1104.

2. In row 10, COST OF SALES , GL_Account_No 11203, currently = null in the 'Amount' column.

NULL needs to be replaced with the sum total amount where GL_Account_No = 11200, 11201, 11202.

3. In row 11, GROSS PROFIT , GL_Account_No 11300, currently = null in the 'Amount' column.

NULL needs to be replaced with the SALES TOTAL amount minus COST OF SALES amount.

4. In row 12, GROSS PROFIT %, GL_Account_No 11301, currently = null in the 'Amount' column.

NULL needs to be replaced with the GROSS PROFIT amount divided by SALES TOTAL amount.

I hope you can help.

Attachment: 2016-06-02_154042_finmodelv1.xlsx

Kind regards

David

Related Programming Questions