sql inventory - want to know how many items left in store

Question

1.00/5 (1 vote)

See more:

hi everyone ,,
my main question is i want ti know how many items left in store like {table1.column - table2.column}
and if my question is not clear i will try say what i want accomplish
i have created view that consist of many table and if i set laptop_quantity=25
the first customers took 5 i want quantity automatically be equaled - assign

ID |Customers: items  | location  |quantity_store| assign|
1  [ johns   ][ Lapto  ] [  ITdep][    20        ] [  5   ]  
2  [ james   ][ Lapto  ] [  ITdep ][   16        ] [  4   ]               
3  [cilena   ][computer] [Accessdep][  16        ] [  0   ]

so is this can be done by trigger or what?

any answer will be so appreciated
and sorry if my question is not clear

Posted 4-May-15 12:15pm

jame01

Updated 4-May-15 20:57pm

Tomas Takac

v3

Add a Solution

Comments

KaushalJB 5-May-15 5:39am

Update your question with the query that you have tried till now.

2 solutions

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)

Anele 'Mashy' Mbanga · Answer 1 · 2015-05-05T01:43:00

From the above you need to have a column that has the item that you want to aggregate with. In your example you have [Customers: items] which has the customer name and the item name together. You need to separate the item name from the customer first.

Secondly, to have a total count of the quantity and assigned you can then run a group by query, e.g.

SQL

select item, sum(quantity_store) as qty, sum(assign) as out from stock group by item;

this will give you a qty of 36 and out of 10 per item in stock

Unfortunately your question is still very unclear however at column level, you can run a query like this

SQL

select item, sum(quantity_store) as qty, sum(assign) as out, (quantity_store - assign) as instock from stock group by item;

this returns the difference between quantity_store and assign. Hope it helps somehow.

jame01 · Answer 2 · 2015-05-05T06:12:00

in first thanks a lot to you guys especially Anele...i knew that my question was not clear enough but what to do that all i could do,,, but after a lot of search i found question it have same issue and the question is if you have table

CSS

Inventory CurrentQuantity OpenedOrder
  SKU1        300             50
  SKU2        100             10

Quote:
Each order will be processed individually. How can I have the database automatically update the inventory tablet after each order is processed?
i.e
If the order has 2 SKU1 in it got processed, the the inventory table will automatically show 298

and the suggest answer is

SQL

CREATE PROC dbo.ProcessOrder
    @Item int,
    @Quantity int
AS
BEGIN
    --Update order table here
    INSERT INTO dbo.Orders(ItemID,Quantity)
    VALUES (@ItemID, @Quantity)

    --Update Inventory here
    UPDATE dbo.Inventory
    SET CurrentQuantity = CurrentQuantity - Quantity
    WHERE ItemID = @ItemID
END

but i still cannot understand how does this works i? i know trigger can insert value to table but store producer how ?
any tutorial or idea will be so appreciated thanks again

sql inventory - want to know how many items left in store

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0