Solved

SQL select query to return current inventory

Posted on 2013-01-11
8
240 Views
Last Modified: 2014-07-25
Hi,

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,

John
sql-inventory-tbl.xlsx
0
Comment
Question by:john-formby
8 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 38766858
John,

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

0
 
LVL 47

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.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38767317
Hi,

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.

Giannis
0
 
LVL 31

Expert Comment

by:awking00
ID: 38767524
john-formby,
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 14

Author Comment

by:john-formby
ID: 38773309
Hi,

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,

John
0
 
LVL 31

Expert Comment

by:awking00
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.
0
 
LVL 26

Accepted Solution

by:
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.
;WITH Inv_CTE1 AS (
SELECT * , 
		DATEADD(HH,CONVERT(INT,SUBSTRING(RIGHT('000000' + timefield, 6),1,2)),
		DATEADD(MI,CONVERT(INT,SUBSTRING(RIGHT('000000' + timefield, 6),3,2)),
				DATEADD(SS,CONVERT(INT,RIGHT(timefield,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
FROM Inv_CTE2
where transaction_id in (11,20)
and co_pack = 'Y'
AND rn = 1

Open in new window

This produced CTE_and_Windowing_Example Results
0
 
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?
0

Featured Post

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.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now