Have an Idea not sure how to do it? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Have an Idea not sure how to do it?

Author

Topic

Jaeson23
Starting Member

3 Posts

Posted - 2010-12-23 : 17:13:37

First post so bare with me.

Here is my Query:

SELECT AccountNumber = A.Number,
COUNT(*) AS TotalAccounts, AccountName = A.Name,
ROUND(SUM(T.Amount), 2) AS ChildAccountTotals
FROM Accounting.Accounts AS A
join Accounting.Transactions AS T
ON T.AccountID = A.ID
GROUP BY A.Name, A.Number

RESULT:

AccountNumber TotalAccounts AccountName ChildAccountTotals
------------- ------------- ----------- ------------------

1, 14, EUR Cash Drawer, 14150.0000000000
1, 73, Multi Currency - EUR, 1404286.4100000000

OK, so to explain what I am working on and trying to get. I am creating a Balance sheet report, the above results are the childAccounts to a ParentAccount. As you can see there are multiple transactions for each account (TotalAccounts). I have the SUM(T.Amount) foreach of these accounts which is what I need.

The second part to this is that I need to Sum any childAccount to a Parent that is NULL. To try and clarify this the table I am working with puts this out from this query:

SELECT A.ID, A.ParentAccountID, A.Number, A.Name
FROM Accounting.Accounts as A
ORDER BY A.Number

RESULT:

ID: B6DCFBD7-DB53-4E72-8E00-04648CC20832
ParentAccountID: 4ED4AA7A-A3C8-4360-9457-F8DBC3FC4EA2
Number: 1
Name: Common Stock

A little wide so you get the idea.

Here are some parents and what they return from this query:

ID: DF85475C-EFE5-4403-88D2-0C5232E33B7D
ParentAccountID: NULL
Number: 1011
Name: Domestic Checking
-------------------------------------------
ID: DFDFED5F-1EC0-4C2B-9BB9-A2F4BA31E545
ParentAccountID: NULL
Number: 1012
Name: Foreign Checking

You can see they are the parent cause of the ParentAccountID is NULL.
Now, I need to sum all the ChildAccounts for their given parent, then give that parent a Total.

The first set of 8 digits in the ParentAccountID will match the ID of the ParentAccount, if that makes sense.

Any help Ideas will help.

Thank You for reading.

Jaeson23
Starting Member

3 Posts

Posted - 2010-12-28 : 15:53:15

No one ???

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-28 : 16:07:23

I was having a hard time following you. So, I'd suggest you provide some consumable sample data. This link might be helpfull to guide you with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-28 : 16:33:19

"A problem well stated is a problem half solved." -- Charles F. Kettering

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

What you did post was pretty bad.
1) You alias columns to vague names or to improper names (learn ISO-11179 rules). Think aobut thigns like amount, id, name, etc. -- can you be more vague or generic?

2) Data elements change names from table to table. Example: the account_id in Transactions is also the vague, magical "id" in accounts. Then you have both an account_id and and account_nbr -- what is the real identifier? Or are there two? if so, how you keep them in synch?

3) is the account structure an adjacency list hierarchy? Or nested sets? or something else?

4) Your sample data looks like you used GUIDs as fake pointers. That is totally non-recreational.

Try again and maybe someone can help you if you help us.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Jaeson23
Starting Member

3 Posts

Posted - 2010-12-28 : 17:56:22

Thank you for the replies. To clear a couple things up. I did not create this table or tables in this DB. I just graduated with my Software Engineering degree in September. This is my third project in my first job out of school. Sorry for not being clear in my questions.

The way this DB was setup is very much "Magic Number" programming, it's terrible and hard to work with.

I will look into the provided links to see what it is that I should post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources