calculation in SQL Server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

i have two table
1. inventory with item_id,Item Name,Rate,Quantity,Total Amount
2. Order with order id,item_name,quantity

now i am making order for 2 item how we calculate with these two table like deduct total amount and quantity from inventory table.

suppose 2 item rate is 10rs per item then 20rs is amount so deduct from inventory table is minus 2 item and minus 20rs from total amount.

how can i make it is possible.

thanks

Posted 15-Sep-10 19:18pm

call to .net

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

nagendrathecoder · Answer 1 · 2010-09-15T19:50:00

Solution 1

I am not too clear about your question.
As far as i understood, you need to use SqlTransactions.
After making orders for items, just update the inventory table for that particular item_id.
Put all Commands under a SqlTransaction.

Posted 15-Sep-10 19:50pm

nagendrathecoder

Comments

call to .net 16-Sep-10 2:16am

Inventory table
item id Name quantity Rate Amt total
1 pepsi 5 10 50
2 coke 6 2 12
3 soda 2 100 200

Order Table
order id Name quantity
1 coke 1
2 soda 2
3 soda 2

now calculate how many items order that will be deduct from inventory table and calculate rate for this order and deduct from total amount from inventory table

nagendrathecoder 16-Sep-10 2:21am

Ok, refer my solution, it'll exectly do your need.
Basically, your tables are not normalised, instead of item_name, use item_id in your Order table.
Then after every entry in Order table, update subsequent entry in Inventory table for that item_id.