Go Premium for a chance to win a PS4. Enter to Win


SQL select query to return current inventory

Posted on 2013-01-11
Medium Priority
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

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 49

Expert Comment

by:Dale Fye
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.id, 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.

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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 27

Accepted Solution

Chris Luttrell earned 2000 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 27

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

971 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