Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql Query

Posted on 2014-02-19
11
Medium Priority
?
323 Views
Last Modified: 2014-02-21
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
0
Comment
Question by:bmkdubai
[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
  • 4
  • 2
  • 2
  • +1
11 Comments
 

Author Comment

by:bmkdubai
ID: 39873066
database is sqlserver.

data is like

docno,docdate,itecode,description,in/out,qty

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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39873126
first, the query of all the items with the "in" sum qty :
select itecode, sum(qty) 
  from transactions
where [in/out] = 'I'
  group by itecode 

Open in new window


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 

Open in new window

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

Open in new window


if anything is unclear, please clarify more about the problem and the expected results.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39873348
> 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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:dbbishop
ID: 39874199
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.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 39875156
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.
0
 

Author Comment

by:bmkdubai
ID: 39875996
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
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 39876937
I'm sure it's possible to do with clever long sql, in one query, but I think the following is more straightforward and thus more maintainable. It's for one item.

/* create and populate the table to try out */
create table transactions(d datetime,qty int)
insert into transactions(d,qty) select '1/1/2013'  ,  2
insert into transactions(d,qty) select '2/10/2013' , 10
insert into transactions(d,qty) select '3/20/2014' ,  6
insert into transactions(d,qty) select '4/30/2014' , 4
insert into transactions(d,qty) select '5/30/2014' , 10
insert into transactions(d,qty) select '6/30/2014',  1

/* the query to find last transactions on or before @the_date totaling @closing_balance */

declare @closing_balance int, @the_date datetime
select @closing_balance=13, @the_date='2014-4-30'

declare c cursor local fast_forward for
	select d,qty from transactions where d <= @the_date
	order by d desc

create table #tmp(d datetime,qty int)

declare @running_total int, @qty int, @d datetime
select @running_total=0
open c
fetch c into @d,@qty
while @@fetch_status=0 and  @running_total < @closing_balance begin
	insert into #tmp(d, qty) 
	select @d,
		case when @running_total + @qty <= @closing_balance then @qty else @closing_balance-@running_total end
	set @running_total = @running_total + @qty
	fetch c into @d,@qty
end
close c; deallocate c
select * from #tmp order by d
drop table #tmp

Open in new window

Making it in one query would be much easier if not your requirement to include only partial qty of the earliest included transaction, in order to achieve exact match.
0
 

Author Comment

by:bmkdubai
ID: 39877042
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.,)
0
 

Author Closing Comment

by:bmkdubai
ID: 39877044
good work
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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