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: 263
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: 10 months ago.
Category: Programming
Expert:  Steve replied 10 months 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 10 months 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 10 months 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 10 months 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 10 months ago.

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

Expert:  Steve replied 10 months ago.

Yes, please!

Customer: replied 10 months ago.

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

Customer: replied 10 months ago.

Attachment: 2016-05-26_093313_finmodelv1.zip

ok here we go, i zipped it up

Expert:  Steve replied 10 months 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 10 months 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 10 months 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 10 months 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 10 months ago.

here you go..

Attachment: 2016-05-26_182627_finmodelv1.xlsx

Expert:  Steve replied 10 months 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 10 months ago.

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

Customer: replied 10 months ago.

Hi, any news?

thnx

Customer: replied 10 months ago.

Hi, are you still there??

Expert:  Steve replied 10 months 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 10 months 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 10 months ago.

ERROR

Customer: replied 10 months ago.

what do you mean

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

Expert:  Steve replied 10 months 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 10 months 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 10 months 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 10 months 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 10 months 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 10 months ago.

OK, sorry:

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

Customer: replied 10 months 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 10 months 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 10 months 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 9 months 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, Consultant
Category: Programming
Satisfied Customers: 263
Experience: Steve is a consultant in the areas of computer software and programming, information management and networking.
Steve and other Programming Specialists are ready to help you
Customer: replied 9 months 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 9 months 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

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7260
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Previous | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7260
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5514
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    936
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    766
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    558
    Founder of StockCanvas.com
  • http://ww2.justanswer.com/uploads/FA/fael/2011-1-15_104041_profile500x500.64x64.jpg Rafael Martins's Avatar

    Rafael Martins

    Computer Software Engineer

    Satisfied Customers:

    446
    Desktop, Mobile and Web Developer. 7+ years of experience. Creative solutions provider.
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Software Engineer

    Satisfied Customers:

    320
    Extensive Knowledge in PHP, MYSQL, CSS & Javascript