SQL select query to return current inventory

Posted on 2013-01-11
Last Modified: 2014-07-25

I have been asked to build a query based on a table in our company database.  The table holds all the transactional history of our inventory, including current inventory and inventory that has been sold.  All records related to the inventory share a common ID number and each transaction has a number.  What I need to do is build a query that shows only the current inventory in the database, but I am not sure how to filter out all the other records :-S

I have made a simplified version of what the table looks like in the attached document.  Basically, I need to return any records where the latest transaction_id for an id is either 11 or 20 and co_pack = Y.  So, from the example, I would want to return the records highlighted in yellow.  I have tried a few things using the MAX function, but I can't get it to work.  Please can someone have a look and possibly point me in the right direction?

Many thanks,

Question by:john-formby
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

Expert Comment

ID: 38766858

I'm not 100% clear on your requirements...  looking at the data you have specified on the spreadsheet, if I selected the records which have co_pack = 'Y' and transaction_id equal to either 11 or 20, as well as the records you have highlighted, I also get the records on row 4 and row 15.  Is there some other filter you need to apply or was this just an oversight?

At the moment, my query is simply this:

SELECT id, [date], transaction_id, co_pack, [status]
FROM inventory_table
WHERE transaction_id in (11,20) AND co_pack = 'Y'

Open in new window

LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38766944
Or does it look more like:

SELECT id, [date], transaction_id, co_pack, [status]
FROM inventory_table
INNER JOIN (SELECT ID, Max([Date]) as MaxDate FROM inventory_Table GROUP BY ID) as Temp
ON inventory_Table.ID = Temp.ID
AND inventory_Table.[Date] = Temp.MaxDate
WHERE transaction_id in (11,20) AND co_pack = 'Y'

This would only use the most recent date for each ID value.  But what is missing here is the quantity field, where does that come in?

Generally, with inventory tables, you have records that record when you add inventory and when you subtract inventory, all in the same table.  In those cases, the way you do it is just sum all of the transaction quantities over time to get the current inventory.
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38767317

In your example don't you want to display the lines 4 and 15 as shown bellow:

1      1/12/2012      20      Y      Stored
4      1/12/2012      11      Y      Stored

If these lines need to be returned then you should modify fyed's query as follows:

SELECT, inventory_table.[date], inventory_table.transaction_id, inventory_table.co_pack, inventory_table.[status]
FROM inventory_table
INNER JOIN (SELECT ID, Max([Date]) as MaxDate FROM inventory_Table WHERE transaction_id in (11,20) AND co_pack = 'Y' GROUP BY ID) as Temp
ON inventory_Table.ID = Temp.ID
AND inventory_Table.[Date] = Temp.MaxDate
WHERE inventory_table.transaction_id in (11,20) AND inventory_table.co_pack = 'Y'

Open in new window

To explain, you should apply the filter in the inner select in order to get the maximum transaction date of the wanted transaction types.

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 32

Expert Comment

ID: 38767524
Assuming you really don't want the records on rows 4 and 15, is it because you're looking for the transaction_id with the max id or because the date contains a time element we can't see and you're looking for the latest date and time or something else? We really need a clarification to provide you with a definitive solution.
LVL 14

Author Comment

ID: 38773309

Thank you for your help.  I have been trying to run the query posted by fyed as this seems to be the closest to what I require.  It is still returning too many and incorrect rows though.  I will try to explain a little clearer what I am trying to do.

"In your example don't you want to display the lines 4 and 15" - I do not want to return rows 4 and 15 in the spreadsheet.  

"We really need a clarification to provide you with a definitive solution." - What I am trying to do is return id numbers with transaction type 11 or 20 where they are the latest transactions for that ID.  

I do have a time field in the database that is stored as an integer (e.g. 00:00:30 = 30; 09:00:00 = 90000; 23:59:30 = 235930).  The date field in the table is also stored as an integer value (e.g. 20120825 = yyyymmdd with leading zeros for month and day where applicable).

"But what is missing here is the quantity field, where does that come in?" - Quantity is an irrelevant field as inventory received is issued out 100%.  The calculations are performed based on the transaction id.  E.g. transaction_id = 5, quantity = 10; transaction_id = 10, quantity = 10; transaction_id = 20, quantity = 10; transaction_id = 62, quantity = 10; transaction_id = 70, quantity = 10.  The reports are generated based on the transaction_id numbers so that we treat 5 as a positive adjustment and 70 as a negative adjustment. All transactions in between are transfers.

I hope this makes it a little bit clearer?  If not, please let me know and I will try to explain further.

Many thanks,

LVL 32

Expert Comment

ID: 38774726
This would be relatively easy if you had a field that was truly a datetime datatype.
Something like -
select id, date, transaction_id, co_pack, status from
(select id, date, transaction_id, co_pack, status,
 row_number() over ( partition by transaction_id order by <datetimefield> desc) rn
 from inventory_tabloe
 where transaction_id in (11,20)
   and co_pack = 'Y') as x
where rn = 1

As it's currently structured, you can create a datetime field, although it gets a little complex, so I'll show it in steps.

First part, using your time field integer -
convertedtime = right(replicate('0',6) + cast(timefield as varchar(6)),6)
This would make the integer timefield of 30 equal to 000030 and the integer timefield of 235930 equal to 235930.

You could then create a new time field using substrings to convert these string to an acceptable time format
timeformat = substring(convertedtime,1,2) + ':' + substring(convertedtime,3,2) + ':' + substring(convertedtime,5,2)
These would result in values of 00:00:30 and 23:59:30 respectively.

Doing something similar with your date field (assuming format of yyyymmdd as you indicate)
convertdate = cast(date as varchar(8))
dateformat = substring(convertdate,1,4) + '-' + substring(convertdate,5,2) + '-' + substring(convertdate,7,2)

A date field with a value of 20120825 would now be 2012-08-25
We can now concatenate these two values to get a recognizable datetime format
datetimeformat = dateformat + ' ' + timeformat
Then to get an actual datetime

actualdatetime = convert(datetime,datetimeformat,120)

You could now use the query I initially showed above substituting the actualdatetime for the <datetimefield>

Good luck.
LVL 26

Accepted Solution

Chris Luttrell earned 500 total points
ID: 38781211
Here is a Common Table Expression version that works on your test data.  
I do not know what the real name of your Time column is, just substitute it in where I have timefield and I guessed at the table name, so replace it if needed also.
		DATEADD(HH,CONVERT(INT,SUBSTRING(RIGHT('000000' + timefield, 6),1,2)),
		DATEADD(MI,CONVERT(INT,SUBSTRING(RIGHT('000000' + timefield, 6),3,2)),
						CONVERT(DATETIME2(0), date )))) RealDateTime
FROM dbo.Inventory
Inv_CTE2 AS (
select id, date, transaction_id, co_pack, status,
 row_number() over ( partition by id order by RealDateTime desc) rn, Inv_CTE1.RealDateTime
 from Inv_CTE1
select id, date, transaction_id, co_pack, status
where transaction_id in (11,20)
and co_pack = 'Y'
AND rn = 1

Open in new window

This produced CTE_and_Windowing_Example Results
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38788197
Just checking in on my open items, did any of these solutions work for you or do you still have questions?

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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