Inventary on hand - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inventary on hand

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-15 : 17:22:22

I have to get the inventory on hand for determined date, we have a table ICTRANS that have all movements of inventory.

For example:

Item date Inv_on_Hand Mov
=====================================
0150002 02/03/2003 23 IS
0150002 02/03/2003 12 VT
0150002 02/03/2003 21 RT
0150002 02/10/2003 17 IS
0150002 02/15/2003 25 RC

The problem is that sometimes in the same date we have different inventory on hand, I have to put the most nearly to the date that the user give me.
If they give me 02/14/2003 I will get 17, if they said 02/05/2003 I will get 21.
How can I do this?
I have this sentence but if in the same day we have more than one inventory on hand it returns more that one row per item.

SELECT DISTINCT TOP 100 PERCENT ITEM, SOH_QTY, TRANS_DATE
FROM dbo.ICTRANS b1
WHERE (TRANS_DATE =
(SELECT MAX(TRANS_DATE) AS EXPR1
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))
ORDER BY ITEM

Edited by - shifis on 04/15/2003 18:05:45

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-15 : 17:49:53

If they give you 2/3/2003, what is the expected result?
I'm guessing it's either the MIN(Inv_on_Hand) or AVG(Inv_on_Hand)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-15 : 17:59:05

I started workign on this, but it started to take too much time.
I think you might need a sequence table to find all the data in-between each date or something.

 
CREATE TABLE #Inventory(ItemNumber INT, InvenDate datetime, OnHand INT)

INSERT INTO #Inventory(ItemNumber, InvenDate, OnHand) VALUES(1, '02/03/2003', 23)
INSERT INTO #Inventory(ItemNumber, InvenDate, OnHand) VALUES(1, '02/03/2003', 12)
INSERT INTO #Inventory(ItemNumber, InvenDate, OnHand) VALUES(1, '02/03/2003', 21)
INSERT INTO #Inventory(ItemNumber, InvenDate, OnHand) VALUES(1, '02/10/2003', 17)
INSERT INTO #Inventory(ItemNumber, InvenDate, OnHand) VALUES(1, '02/15/2003', 25)

DROP TABLE #Inventory

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-15 : 18:18:16

This table have an update_time field, so maybe this can help.

Edited by - shifis on 04/15/2003 18:22:35

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-15 : 18:20:57

Hey shifis,
How about posting something similar to what I posted that creates the table, and some sample data. The show us what the desired result should be from that sample data. This will allow us to help you MUCH better.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-15 : 18:26:22

SELECT DISTINCT TOP 100 PERCENT ITEM, SOH_QTY, TRANS_DATE, UPDATE_TIME
FROM dbo.ICTRANS b1
WHERE (TRANS_DATE =
(SELECT MAX(TRANS_DATE) AS EXPR1
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-04-15 00:00:00', 102)) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))
ORDER BY ITEM
This is what we get

Item----- SOH_QTY ----TRANS_DATE-----UPDATE_TIME
-----------------------------------------------------------------
0010057 27 1/20/2002 0
0010057 47 1/20/2002 1
0010088 21 1/20/2002 0
0010088 69 1/20/2002 1
0010090 32 1/20/2002 0
0010090 56 1/20/2002 1
0010091 21 1/20/2002 0
0010093 7 1/6/2002 0
0010096 39 1/20/2002 0
0010096 147 1/20/2002 1
0010096 383 1/20/2002 2
0010096 503 1/20/2002 3
0010096 679 1/20/2002 4
0010096 779 1/20/2002 5
0010096 939 1/20/2002 6
0010096 1259 1/20/2002 7
0010098 17 1/20/2002 0
0010098 41 1/20/2002 1

This is what I need
Item----- SOH_QTY ----TRANS_DATE-----UPDATE_TIME
-----------------------------------------------------------------
0010057 47 1/20/2002 1
0010088 69 1/20/2002 1
0010090 56 1/20/2002 1
0010091 21 1/20/2002 0
0010093 7 1/6/2002 0
0010096 1259 1/20/2002 7
0010098 41 1/20/2002 1

Edited by - shifis on 04/15/2003 18:28:31

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-15 : 18:39:38

I do something like this, but I guess it have to be a better way.

SELECT DISTINCT TOP 100 PERCENT ITEM, SOH_QTY, TRANS_DATE, UPDATE_TIME
FROM dbo.ICTRANS b1
WHERE (TRANS_DATE =
(SELECT MAX(TRANS_DATE) AS EXPR1
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-04-15 00:00:00', 102)) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM)) AND (UPDATE_TIME =
(SELECT MAX(UPDATE_TIME) AS EXPR1
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-04-15 00:00:00', 102)) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))
ORDER BY ITEM

Result:

Item----- SOH_QTY ----TRANS_DATE-----UPDATE_TIME
--------------------------------------------------
0010057 47 1/20/2002 1
0010088 69 1/20/2002 1
0010090 56 1/20/2002 1
0010091 21 1/20/2002 0
0010093 7 1/6/2002 0
0010098 41 1/20/2002 1

But I have something wrong because the code 0010096 disappear.

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-16 : 18:42:11

I am still having problem to have the inventory on hand.
I was doing this Query:
SELECT
DISTINCT ITEM, SOH_QTY
FROM dbo.ICTRANS b1
WHERE (CONVERT(CHAR(10), TRANS_DATE, 101) + CAST(UPDATE_TIME AS CHAR(6)) =
(SELECT CONVERT(CHAR(10), MAX(TRANS_DATE), 101) + CAST(MAX(UPDATE_TIME) AS CHAR(6)) AS FECH_HORA
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102))
AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))

But that is grown because if I have this information

Item Date Time
=======================
0010041 17.0000 01/06/2002 0
0010041 8.0000 01/13/2002 0
0010043 14.0000 01/13/2002 0
0010043 26.0000 01/13/2002 1
0010043 5.0000 01/06/2002 0
0010043 13.0000 01/20/2002 0
0010043 29.0000 01/06/2002 1
0010044 .0000 01/20/2002 0
0010044 17.0000 01/13/2002 0
0010044 29.0000 01/06/2002 1
0010044 5.0000 01/06/2002 0

When I run the query in the item 010043, it returns me date=01/20/2002 and time=1 instead of date=01/20/2002 time=0, so 01/20/20020 <> 01/20/20021, and it disappear the item 0010043 from the results.

Results:
item soh_qty date-time
-----------------------------------------------
0010041 8.0000 01/13/20020
0010057 41.0000 01/27/2002100100
0010088 75.0000 01/27/2002100101
0010090 67.0000 01/27/2002100101
0010091 20.0000 01/27/2002100100
0010093 7.0000 01/06/20020
0010096 1362.0000 01/27/2002100108
0010098 29.0000 01/27/2002100102
0010099 26.0000 01/27/2002100102
0010102 15.0000 01/27/2002100101

That is because I return the max time in all the range and not in the max time of the max date.
How can I do this?

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-16 : 20:27:12

I do this query and it works, but I want to know it there a better way to do this.
Thanks.

SELECT DISTINCT ITEM, SOH_QTY, CONVERT(CHAR(10), TRANS_DATE, 101), CAST(UPDATE_TIME AS CHAR(6))
FROM dbo.ICTRANS b1
WHERE (TRANS_DATE=
(SELECT Max(TRANS_DATE)AS FECH_HORA
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)

GROUP BY LOCATION, ITEM))
AND
(UPDATE_TIME=
(SELECT Max(UPDATE_TIME)AS HORA
FROM dbo.ICTRANS
WHERE (TRANS_DATE = b1.TRANS_DATE) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))
ORDER by ITEM

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-16 : 21:13:49

shifis,

When we ask for the sameple table and data, could you PLEASE do it
SQL Server syntax and not some "other" representation?

Have a look at Michael's post with a proper CREATE TABLE and the
subsequent INSERT statements.

Everybody here has no problems helping you out but you must help us!!!!!


DECLARE @Date DATETIME
SET @Date = '2002-04-15'

SELECT * 
FROM ICTRANS I
WHERE EXISTS
(
SELECT 1
FROM ICTRANS
WHERE DATEDIFF(d,@Date, TRANS_DATE) <=0 AND Item = I.Item
HAVING MAX(DATEDIFF(d,@Date, TRANS_DATE)) + MAX(Update_Time) = 
DATEDIFF(d,@Date, I.TRANS_DATE) + I.Update_Time
)

DavidM

"SQL-3 is an abomination.."

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-17 : 11:29:48

I truly a apprecite your comment and in the future I promise to do that.
Thanks a lot.
Now I am going to try the query and I will let you know what happen.

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-17 : 15:14:26

I test the query and it works fine but I still having problem with the MAX(UPDATE_TIME), that this because if the item 0010043 has this registers (between the first movent of invetary until 01/31/2002).
The max UPDATE_TIME will be 1, and in the date 2002-01-20 whe don't have UPDATE_TIME=1, is 0, so in the result this item is missing.

ITEM SOH_QTY DATE TIME
-----------------------------------------
0010043 5.0000 2002-01-06 00:00:00.000 0
0010043 29.0000 2002-01-06 00:00:00.000 1
0010043 14.0000 2002-01-13 00:00:00.000 0
0010043 26.0000 2002-01-13 00:00:00.000 1
0010043 13.0000 2002-01-20 00:00:00.000 0

CREATE TABLE #IC_TRANS( ITEM CHAR(32), SOH_QTY DECIMAL(13,4), TRANS_DATE datetime, UPDATE_TIME CHAR(6))

INSERT INTO #IC_TRANS VALUES('0010043', 5.0000,'2002-01-06 00:00:00.000',0)
INSERT INTO #IC_TRANS VALUES('0010043', 29.0000,'2002-01-06 00:00:00.000',1)
INSERT INTO #IC_TRANS VALUES('0010043', 14.0000,'2002-01-13 00:00:00.000',0)
INSERT INTO #IC_TRANS VALUES('0010043', 26.0000,'2002-01-13 00:00:00.000',1)
INSERT INTO #IC_TRANS VALUES('0010043', 13.0000,'2002-01-20 00:00:00.000',0)
SELECT * FROM #IC_TRANS
DROP TABLE #Inventory

Query result:
ITEM SOH_QTY DATE TIME DATEDIFF(d,@Date, TRANS_DATE)
====================================================================
0010041 8.0000 2002-01-13 00:00:00.000 0 -18
0010057 41.0000 2002-01-27 00:00:00.000 100100 -4
0010088 75.0000 2002-01-27 00:00:00.000 100101 -4
0010090 67.0000 2002-01-27 00:00:00.000 100101 -4
0010091 20.0000 2002-01-27 00:00:00.000 100100 -4
0010093 7.0000 2002-01-06 00:00:00.000 0 -25
0010096 1362.0000 2002-01-27 00:00:00.000 100108 -4
0010096 251.0000 2002-02-03 00:00:00.000 100101 3
0010098 29.0000 2002-01-27 00:00:00.000 100102 -4

And I don't know why if we have the condition DATEDIFF(d,@Date, TRANS_DATE) <=0 on the item 0010096 it returns two rows and one is positive (3)

Item 10096 information unti 2002-02-10
ITEM SOH_QTY DATE TIME
-----------------------------------------
0010096 228.0000 2002-01-06 00:00:00.000 0
0010096 376.0000 2002-01-06 00:00:00.000 1
0010096 456.0000 2002-01-06 00:00:00.000 2
0010096 624.0000 2002-01-06 00:00:00.000 3
0010096 840.0000 2002-01-06 00:00:00.000 4
0010096 1020.0000 2002-01-06 00:00:00.000 5
0010096 83.0000 2002-01-13 00:00:00.000 0
0010096 283.0000 2002-01-13 00:00:00.000 1
0010096 519.0000 2002-01-13 00:00:00.000 2
0010096 699.0000 2002-01-13 00:00:00.000 3
0010096 779.0000 2002-01-13 00:00:00.000 4
0010096 979.0000 2002-01-13 00:00:00.000 5
0010096 1059.0000 2002-01-13 00:00:00.000 6
0010096 1299.0000 2002-01-13 00:00:00.000 7
0010096 39.0000 2002-01-20 00:00:00.000 0
0010096 147.0000 2002-01-20 00:00:00.000 1
0010096 383.0000 2002-01-20 00:00:00.000 2
0010096 503.0000 2002-01-20 00:00:00.000 3
0010096 679.0000 2002-01-20 00:00:00.000 4
0010096 779.0000 2002-01-20 00:00:00.000 5
0010096 939.0000 2002-01-20 00:00:00.000 6
0010096 1259.0000 2002-01-20 00:00:00.000 7
0010096 82.0000 2002-01-27 00:00:00.000 100100
0010096 362.0000 2002-01-27 00:00:00.000 100101
0010096 514.0000 2002-01-27 00:00:00.000 100102
0010096 666.0000 2002-01-27 00:00:00.000 100103
0010096 782.0000 2002-01-27 00:00:00.000 100104
0010096 922.0000 2002-01-27 00:00:00.000 100105
0010096 1042.0000 2002-01-27 00:00:00.000 100106
0010096 1242.0000 2002-01-27 00:00:00.000 100107
0010096 1362.0000 2002-01-27 00:00:00.000 100108
0010096 59.0000 2002-02-03 00:00:00.000 100100
0010096 251.0000 2002-02-03 00:00:00.000 100101
0010096 367.0000 2002-02-03 00:00:00.000 100102
0010096 527.0000 2002-02-03 00:00:00.000 100103
0010096 755.0000 2002-02-03 00:00:00.000 100104
0010096 835.0000 2002-02-03 00:00:00.000 100105
0010096 963.0000 2002-02-03 00:00:00.000 100106
0010096 1175.0000 2002-02-03 00:00:00.000 100107
0010096 1375.0000 2002-02-03 00:00:00.000 100108
0010096 4.0000 2002-02-10 00:00:00.000 100100
0010096 196.0000 2002-02-10 00:00:00.000 100101

In this case I just put some examples of all the information of item 0010096

INSERT INTO #IC_TRANS VALUES('0010096', 82.0000,'2002-01-27 00:00:00.000',100100)
INSERT INTO #IC_TRANS VALUES('0010096', 362.0000,'2002-01-27 00:00:00.000',100101)
INSERT INTO #IC_TRANS VALUES('0010096', 514.0000,'2002-01-27 00:00:00.000',100102)
INSERT INTO #IC_TRANS VALUES('0010096', 666.0000,'2002-01-27 00:00:00.000',100103)
INSERT INTO #IC_TRANS VALUES('0010096', 782.0000,'2002-01-27 00:00:00.000',100104)
INSERT INTO #IC_TRANS VALUES('0010096', 922.0000,'2002-01-27 00:00:00.000',100105)
INSERT INTO #IC_TRANS VALUES('0010096', 1042.0000,'2002-01-27 00:00:00.000',100106)
INSERT INTO #IC_TRANS VALUES('0010096', 1242.0000,'2002-01-27 00:00:00.000',100107)
INSERT INTO #IC_TRANS VALUES('0010096', 1362.0000,'2002-01-27 00:00:00.000',100108)
INSERT INTO #IC_TRANS VALUES('0010096', 59.0000,'2002-02-03 00:00:00.000',100100)
INSERT INTO #IC_TRANS VALUES('0010096' , 251.0000,'2002-02-03 00:00:00.000',100101)
INSERT INTO #IC_TRANS VALUES('0010096' , 963.0000,'2002-02-03 00:00:00.000',100106)
INSERT INTO #IC_TRANS VALUES('0010096', 1175.0000,'2002-02-03 00:00:00.000',100107)
INSERT INTO #IC_TRANS VALUES('0010096', 1375.0000,'2002-02-03 00:00:00.000',100108)
INSERT INTO #IC_TRANS VALUES('0010096' , 4.0000,'2002-02-10 00:00:00.000',100100)
INSERT INTO #IC_TRANS VALUES('0010096', 196.0000,'2002-02-10 00:00:00.000',100101)

The result should be:
ITEM SOH_QTY DATE TIME
=========================================
0010041 8.0000 01/13/2002 0
0010043 13.0000 01/20/2002 0
0010044 .0000 01/20/2002 0
0010057 41.0000 01/27/2002 100100
0010088 75.0000 01/27/2002 100101
0010090 67.0000 01/27/2002 100101
0010091 20.0000 01/27/2002 100100
0010093 7.0000 01/06/2002 0
0010096 1362.0000 01/27/2002 100108

Query
DECLARE @Date DATETIME

SET @Date = CONVERT(DATETIME, '2002-01-31 00:00:00', 102)

SELECT *
FROM #IC_TRANS I
WHERE EXISTS
(
SELECT 1
FROM #IC_TRANS
WHERE DATEDIFF(d,@Date, TRANS_DATE) <=0 AND ITEM = I.ITEM
HAVING MAX(DATEDIFF(d,@Date, TRANS_DATE)) + MAX(UPDATE_TIME) =
DATEDIFF(d,@Date, I.TRANS_DATE) + I.UPDATE_TIME
)

Edited by - shifis on 04/17/2003 15:17:02

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-17 : 15:18:24

shifis,
Please start posting CREATE TABLE and INSERT INTO statements instead of your text representation of your table. Help us help you by doing that. Look at my other post. We need something that looks like that.

When posting your code, use the

and

tags. It's the "#" sign at the top of this post window.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-17 : 16:43:40

Mmm let see if I got it:

CREATE TABLE #IC_TRANS( ITEM CHAR(32), SOH_QTY DECIMAL(13,4), TRANS_DATE datetime, UPDATE_TIME CHAR(6))
INSERT INTO #IC_TRANS VALUES('0010043', 5.0000,'2002-01-06 00:00:00.000',0)
INSERT INTO #IC_TRANS VALUES('0010043', 29.0000,'2002-01-06 00:00:00.000',1)
INSERT INTO #IC_TRANS VALUES('0010043', 14.0000,'2002-01-13 00:00:00.000',0)
INSERT INTO #IC_TRANS VALUES('0010043', 26.0000,'2002-01-13 00:00:00.000',1)
INSERT INTO #IC_TRANS VALUES('0010043', 13.0000,'2002-01-20 00:00:00.000',0)
INSERT INTO #IC_TRANS VALUES('0010096', 82.0000,'2002-01-27 00:00:00.000',100100)
INSERT INTO #IC_TRANS VALUES('0010096', 362.0000,'2002-01-27 00:00:00.000',100101)
INSERT INTO #IC_TRANS VALUES('0010096', 514.0000,'2002-01-27 00:00:00.000',100102)
INSERT INTO #IC_TRANS VALUES('0010096', 666.0000,'2002-01-27 00:00:00.000',100103)
INSERT INTO #IC_TRANS VALUES('0010096', 782.0000,'2002-01-27 00:00:00.000',100104)
INSERT INTO #IC_TRANS VALUES('0010096', 922.0000,'2002-01-27 00:00:00.000',100105)
INSERT INTO #IC_TRANS VALUES('0010096', 1042.0000,'2002-01-27 00:00:00.000',100106)
INSERT INTO #IC_TRANS VALUES('0010096', 1242.0000,'2002-01-27 00:00:00.000',100107)
INSERT INTO #IC_TRANS VALUES('0010096', 1362.0000,'2002-01-27 00:00:00.000',100108)
INSERT INTO #IC_TRANS VALUES('0010096', 59.0000,'2002-02-03 00:00:00.000',100100)
INSERT INTO #IC_TRANS VALUES('0010096' , 251.0000,'2002-02-03 00:00:00.000',100101)
INSERT INTO #IC_TRANS VALUES('0010096' , 963.0000,'2002-02-03 00:00:00.000',100106)
INSERT INTO #IC_TRANS VALUES('0010096', 1175.0000,'2002-02-03 00:00:00.000',100107)
INSERT INTO #IC_TRANS VALUES('0010096', 1375.0000,'2002-02-03 00:00:00.000',100108)
INSERT INTO #IC_TRANS VALUES('0010096' , 4.0000,'2002-02-10 00:00:00.000',100100)
INSERT INTO #IC_TRANS VALUES('0010096', 196.0000,'2002-02-10 00:00:00.000',100101)

DECLARE @Date DATETIME

SET @Date = CONVERT(DATETIME, '2002-01-31 00:00:00', 102)

SELECT *
FROM #IC_TRANS I
WHERE EXISTS
(
SELECT 1
FROM #IC_TRANS
WHERE DATEDIFF(d,@Date, TRANS_DATE) <=0 AND ITEM = I.ITEM
HAVING MAX(DATEDIFF(d,@Date, TRANS_DATE)) + MAX(UPDATE_TIME) =
DATEDIFF(d,@Date, I.TRANS_DATE) + I.UPDATE_TIME
)

DROP TABLE #IC_TRANS

Edited by - shifis on 04/17/2003 17:11:35

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources