bmkdubai
asked on
Sql Query
Hi experts
I have a table which have inventory transactions ( in and out)
I need a query which shows list of last all IN transactions where the sum of quantities equal to stock balance.
regards
Benny
I have a table which have inventory transactions ( in and out)
I need a query which shows list of last all IN transactions where the sum of quantities equal to stock balance.
regards
Benny
first, the query of all the items with the "in" sum qty :
in "stock" table, I presume you have also itecode and a closing_balance field:
if anything is unclear, please clarify more about the problem and the expected results.
select itecode, sum(qty)
from transactions
where [in/out] = 'I'
group by itecode
in "stock" table, I presume you have also itecode and a closing_balance field:
select s.itecode, s.closing_balance, t.sum_qty
from stock_table s
left join ( select itecode, sum(qty) sum_in
from transactions
where [in/out] = 'I'
group by itecode ) t
on t.itecode = s.itecode
so, if now you want to have only those items where the closing_balance = sum(qty)select s.itecode, s.closing_balance, t.sum_qty
from stock_table s
join ( select itecode, sum(qty) sum_in
from transactions
where [in/out] = 'I'
group by itecode ) t
on t.itecode = s.itecode
and t.sum_qty = s.closing_balance
if anything is unclear, please clarify more about the problem and the expected results.
> I need to get what are I transaction qty total is matching with the closing balance of a item.
1. Where do you take closing balance from?
2. let's say, the closing balance is 3, and you have these "in" transactions:
# Qty
1 1
2 1
3 2
4 2
Which ones do you expect to be shown in the result?
1. Where do you take closing balance from?
2. let's say, the closing balance is 3, and you have these "in" transactions:
# Qty
1 1
2 1
3 2
4 2
Which ones do you expect to be shown in the result?
Definitely need more info. "Data is like docno, docdate, itecode, description, in/out, qty" tells us nothing. "there are many receipts and issues marked I for in and O for out" tells little more. What is/are the table schema? Is there more than one table involved? Where is the stock balance derived.
We like to call ourselves experts, not psychics. :-)
What version of SQL Server are you using?
Clearly define the problem. For example (using my psychic abilities),
"I have a Transaction table with DocNo, DocDate, ItemCode, Description, I_0_Indicator and Qty. This table ties to a Stock table on DocNo and has a StockBalance. I need to identify rows in the Stock table where the sum of rows in the Transaction table with an I_O value of 'IN' equals the StockBalance value. Ignore I-O values of 'OUT'."
We can start to work with that.
We like to call ourselves experts, not psychics. :-)
What version of SQL Server are you using?
Clearly define the problem. For example (using my psychic abilities),
"I have a Transaction table with DocNo, DocDate, ItemCode, Description, I_0_Indicator and Qty. This table ties to a Stock table on DocNo and has a StockBalance. I need to identify rows in the Stock table where the sum of rows in the Transaction table with an I_O value of 'IN' equals the StockBalance value. Ignore I-O values of 'OUT'."
We can start to work with that.
I have been duly (and properly) admonished for my last post, and to all, I offer an apology. Although the facts are sketchy, it should be our job to 'guide' the customer into defining the problem so that we can assist with a solution.
I use EE both to garner information on problems that I encounter (and for that I am very grateful) but also to return to the community knowledge that I've gleaned over the years. There should be no excuse to posting an inflammatory comment.
Please accept my apology and know that I will continue to assist in any possible way.
I use EE both to garner information on problems that I encounter (and for that I am very grateful) but also to return to the community knowledge that I've gleaned over the years. There should be no excuse to posting an inflammatory comment.
Please accept my apology and know that I will continue to assist in any possible way.
ASKER
Dear All
I am using sqlserver 2008 r2
The purpose of the query is to find stock aging. there will be many in and out transactions.
Based on the date I can find out the closing balance as on a day or month or year whatever.
But for the financial analysis we need stock aging. In Manual process we find stock aging as on a particulat day will be .
1. find out the closing balance of all the items as on a particular day.
2. get the last IN transactoins for all the items with date, document number etc., (be sure when we sum all the quantities of IN of a particular item should match with the closing balance of the item.)
3. then make the stock aging based on the date.
example
closing balance as on 30/4/2014 is 13 and in quantities are
1/1/2013 2
10/2/2013 10
20/3/2014 6
30/4/2014 4
30/5/2014 10
30/6/2014 1
the result will be
10/2/2013 3
20/3/2014 6
30/4/2014 4
Please support
regards
Benny
I am using sqlserver 2008 r2
The purpose of the query is to find stock aging. there will be many in and out transactions.
Based on the date I can find out the closing balance as on a day or month or year whatever.
But for the financial analysis we need stock aging. In Manual process we find stock aging as on a particulat day will be .
1. find out the closing balance of all the items as on a particular day.
2. get the last IN transactoins for all the items with date, document number etc., (be sure when we sum all the quantities of IN of a particular item should match with the closing balance of the item.)
3. then make the stock aging based on the date.
example
closing balance as on 30/4/2014 is 13 and in quantities are
1/1/2013 2
10/2/2013 10
20/3/2014 6
30/4/2014 4
30/5/2014 10
30/6/2014 1
the result will be
10/2/2013 3
20/3/2014 6
30/4/2014 4
Please support
regards
Benny
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
idea is good. I need extend a lot. like find stock balance as on user selection, there are many inventory items, I need to make aging based on the user parameter like (90,60,120 etc.,)
ASKER
good work
ASKER
data is like
docno,docdate,itecode,desc
there are many receipts and issues marked I for in and O for out.
I need to get what are I transaction qty total is matching with the closing balance of a item.
regards