i have 3 tables...
CREATE DATABASE STORE_TEST;
USE STORE_TEST;
CREATE TABLE ITEM
(
item_id int,
item_name varchar(50),
available_qty int,
added_date datetime,
CONSTRAINT PK_ITEM PRIMARY KEY (item_id)
);
CREATE TABLE STOCK_IN
(
in_id int,
in_item_id int,
in_qty int,
in_date datetime,
CONSTRAINT PK_STOCK_IN PRIMARY KEY (in_id),
CONSTRAINT FK_STOCK_IN FOREIGN KEY (in_item_id) REFERENCES ITEM (item_id)
);
CREATE TABLE STOCK_OUT
(
out_id int,
out_item_id int,
out_qty int,
out_date datetime,
CONSTRAINT PK_STOCK_OUT PRIMARY KEY (out_id),
CONSTRAINT FK_STOCK_OUT FOREIGN KEY (out_item_id) REFERENCES ITEM (item_id)
);
i want to select item_id ,item_name, sum of out_qty,sum of in_qty and available_qty
in a specified date period.
How can i do it..
Please help me..
How can i do it..
Please help me..
Thank you
Sandeep Singh ShekhawatPosted Mar 27, 2013, 3:07 AM
1) item according to stock in date
2) item according to stock out date
3) item according to both stock in and stock out date
so use a parameter which check what you want to do and apply three condition. According to that parameter query will be execute.
Tharake GunatilakePosted Mar 27, 2013, 2:41 AM
But i want to get them within a specified time period.
Just say between 3-03-2013 and 3-27-2013.
will it give a problem if there is only a stock_in for
an item within that period.
And also if the stock_in date is before that period and stock
out date is within that period, will it give a problem..
Thank you Sandeep Singh Shekhawat.
Sandeep Singh ShekhawatPosted Mar 27, 2013, 2:12 AM
SELECT item.item_id as [ITEM ID],item.item_name as [ITEM NAME],
SUM(stock_in.in_qty)as [STOCK IN],
SUM(stock_out.out_qty) AS [STOCK OUT],
item.available_qty as [AVAIBALE QUANTITY]
FROM ITEM AS item
INNER JOIN STOCK_IN AS stock_in ON stock_in.in_item_id=item.item_id
INNER JOIN STOCK_OUT AS stock_out ON stock_out.out_item_id=item.item_id
GROUP by item.item_name,item.item_id,
item.available_qty