Solved

How do you run formulas in SQL to update a table based on new information?

Posted on 2009-05-06
18
177 Views
Last Modified: 2013-11-27
Hello Experts.

I have a database (or am designing a database) for SQL compact 3.5, and as I haven't done this before, I know that the planning is everything.

In my attachment, you will see my tables and the primary key and foreign key relationships in the first page.  And in the second page you will see an example of the tables in use.  I didn't upload or print the row and column headings as I need to understand the SQL table code.

Question:
How can I calculate the "Current Balances" in the "Expense Accounts" and "Payment Types" tables?  How should I setup a table to show the deposits/transactions for an account/payment type by month?  I am not sure if I should have a separate month table to log that data or not.  In the program, I will generate a code that automatically creates a new month feild/column that will store that months date.

This is my first database and I am trying to understand how they work.

Thank You.

Mike
Database-Tables.pdf
0
Comment
Question by:missionarymike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
18 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24321418
>> In the program, I will generate a code that automatically creates a new month feild/column that will store that months date.

No, no.  More rows, not more columns.

Your transactions table looks good.  You have the date.  Reporting on it by month will not be difficult.

>>How can I calculate the "Current Balances" in the "Expense Accounts" and "Payment Types" tables?

You could set up a trigger on your Transactions table to update those ... but it is probably better not to even have those fields in the Expense Accounts table.  Calculate them with a SUM() function when you report on the data.

0
 

Author Comment

by:missionarymike
ID: 24321474
This main issue is complex.

First, when a deposit is entered (unless it is for a specific account) will be split up by percentages that are setup in the allowances table.  So the database will need to take the deposits from that month only and divide them up by a preset % and dumped into the expense accounts.  Therefor to get the accurate balance of the expense account, the database will need to sum all of the "monthly" columns that are created.  If an error is found in a transaction 3 months back, the user can change it and the deposits will automatically update all the %'s of that month and then recalculate the totals.

Does that make sense?

Mike
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 24321480
Agreed with DanielWilson. Does not seems to be necessary to have those fields in the tables.

To report on data, you should create SELECT statements, like this one:
SELECT datepart('yyyy',date), datepart('m',date), expense_code, sum(payment)
from transaction
group by datepart('yyyy',date), datepart('m',date), expense_code

The syntax could be wrong, but this is the principle is that.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:missionarymike
ID: 24321527
Thanks guys.  I did not provide an example for the allowances table (my mistake).  I need to be able to let the user select the monthly allowance for each individual month (% of total income for the month),  The allowances may be different month-to-month.  So somehow I need a table that will allow the user to enter how much of their income they want allocated to each expense code for the month.  I agree that to calculate, this could all be dynamic, but I need the database to calculate the deposits for a month multiplied by the % for each month and then add the total.

Look at the attached file for the allowances.  I need a table/field/column/row/?? for each month.

Mike

Database-Tables-2.pdf
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24321584
A row for each month/expense code combination.
0
 

Author Comment

by:missionarymike
ID: 24321605
Do you mean, have the expense codes as the column headings and have the monthly info as the rows/records?  How would that work if the user adds/deletes/modifies the expense codes?

Thanks.

Mike
0
 

Author Comment

by:missionarymike
ID: 24327122
Is this a better use of the allowances table.  I have adjusted the table in the examples attachment.

Will this cause a problem with the redundancy of the use of expense codes?

Mike

Database-Tables-3.pdf
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24329676
Yeah, that one looks better.

You won't be able to set the expense codes as a key ... but they could and probably should be part of a key/unique index that would include the month & year to which they apply.
0
 

Author Comment

by:missionarymike
ID: 24329719
Can you elaborate.  I don't quite understand.  What prevents them from being a key?

Thanks.

Mike
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24329949
They won't be unique.

Auto Expenses     2009   May
Groceries               2009 May
Auto Expenses     2009  June
0
 

Author Comment

by:missionarymike
ID: 24330048
Okay, so they won't be unique.  But can they still be a foriegn key to the Expense Code table so that they would be updated/changed if the expene code is renamed?

Mike
0
 

Author Comment

by:missionarymike
ID: 24330066
That is also why i had the ID field.  That might be the only unique feild.  I thought the primary/foreign key relationship would allow you to connect fields togehter so they could be update more easily.

Mike
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24330145
Using the ID field as your key is certainly an option ... probably your best in this case.  But I would probably also create a unique index on that set of 3 fields to prevent duplication -- e.g. 2 Auto Expense entries for the same month.
0
 

Author Comment

by:missionarymike
ID: 24330276
How do you create this "index" for the 3 fields together and not individually?

Mike
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 24332030
The DDL command would be (if I recall your field names correctly)
Create Unique Index unq_Allowance on Allowance (ExpenseAccount, [Year], [Month])
0
 

Author Comment

by:missionarymike
ID: 24332214
Great.  1 last question.  This table/tables will be able to take:

1) all transaction deposits to a type code (ARVEST) and
2) multiply by the percentage for each allowance, each month and
3) add all the months together so that a grand total can be acheived

Correct?

Mike
0
 

Author Closing Comment

by:missionarymike
ID: 31578807
I will be a little clearer, and repost the tables for a final review.  Thanks again.

Mike
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24334975
correct
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Use SSRS to email customers? 4 29
HIghlights of SSIS? 3 42
T-SQL: Trying to use a "NOT IN (Subquery)" in CASE Statement 2 29
database audit for object access 6 40
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question