Need to create a GL Trial Balance using raw GL data from MS Sql

I am looking to create a GL TrialBalance report using raw GL data in MS SQL using the parameters of GLYEar and GLPeriod
I need the following;

these are the needed columns for the report,
DeptNum, GLAccount, GL Desc, Begining Balance Debit, Begining Balance Credit, Current Period Debit, Current Period Credit, Ending Balance Debit, Ending Balance Credit

Keep in mind I will also have Balance Sheet accounts that have running balances as part of the report and are identified by the column called BALANCESHEET (Y\N)

Sample of the raw data
TB-Columns.JPG
Juan AcevedoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
Your select statement will look something like this.  From the column names I am thinking you are missing GLTRIAL table.

Select DeptNum, glacct as GLAccount, gldesc as 'GL Desc', <what column> as Begining Balance Debit, amount as  Begining Balance Credit, <what column> asCurrent Period Debit, <what column>  as Current Period Credit, <what column> as Ending Balance Debit, Ending Balance Credit
From GLDATA inner join glTrial
where Balancesheet = 'Y'

Open in new window


Without having the other tables data I cannot put a statement join together.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan AcevedoAuthor Commented:
There are no other tables. I have to build it from scratch in terms of  getting all beg balances etc..
0
yo_beeDirector of Information TechnologyCommented:
Ok.  So I am not sure what columns are what based on your image vs the columns you listed.

If you want to select only certain columns, but the name is not the desired display name you can use As 'Name' after the actual column name.

What is Begining Balance Debit column supposed to be?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Juan AcevedoAuthor Commented:
This is the report that I need to re-produce. When a user requests a trial balance they input the GL year and GL Period. The returned data should have the beginning balance for the gl account, the current period activity and the ending balance information


current report
0
yo_beeDirector of Information TechnologyCommented:
I am still trying to figure out your column association.  

Accout = GLACCT
Title = GLDESC or TransDesc  (I am not sure)
unknown column = values with  R or E
Beginning Balance = Amount
Debit = ?
Credit = ?
Debit = ?
Credit = ?
 
Without knowing that I am not able to build you a statement.  
Please file in the question and validate the column name.
0
Juan AcevedoAuthor Commented:
The project was put on hold however some valuable insight given by yo_bee
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.