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"
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.
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.
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.
@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'
Would it be easier if i uploaded the table data and SQl query?
Just answer wont allow me to upload .bak files, any suggestions?
ok here we go, i zipped it up
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?
Hi, The .bak is a back up file from SQL server management studio, how would you prefer to receive the table data and query?
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.
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.
here you go..
I'm not sure what's going on here, I keep putting in comments but they're not appearing in the threaded discussion.
mmm, maybe try again as the last message came through ok..
Hi, any news?
Hi, are you still there??
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 TABLE1GROUP BY GL_Account_Name WITH ROLLUP
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".
what do you mean
"Just substitue the calculation for "Amount"."?
SET Amount =CASEWhen GL_Account_name = ProductA Then @10001 * @10005When GL_Account_name = ProductB Then @10001 * @10004When GL_Account_name = ProductC Then @10001 * @10002When GL_Account_name = ProductD Then @10001 * @10003When GL_Account_name = ProductE Then @10001 * @10005When GL_Account_name = "Sales Total" Then sum(ProductA+ProductB+ProductC+ProductD+ProductE)END
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.
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.
The error is because of the sales total line. Try this:
When GL_Account_Name = 'Sales Total' Then Sum(Amount) As 'Sales Total'
'Incorrect syntax near the keyword 'AS'.'
ive attached a screenshot of the error message and then scroll down the page for the results screenshot
When GL_Account_Name = 'Sales Total' Then Sum(Amount)
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.'
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 TABLE1GROUP BY GL_Account_Name WITH ROLLUP
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.
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!
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?
I've decided to take a different approach to the totals.
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.