August 19, 2011 at 6:01 pm
Sql Gurus,
I have an Inventory trail table where I am going to capture all orders and sales and log/track them here
with latest QOH value.
step 1 - There will be batch process running everynight that will pickup all orders/sales came in that day
step 2 - Take the last qoh from the Inventory_Trail for each product that matches products in order/sale that came in step 1 and add a new entry to Inventory_Trail with newly calculated Qoh.
Is there best way to do this without using cursor or looping through?
I have put in sample table and sample table for creating a query
CREATE TABLE [dbo].[tbl_Inventory_Trail_test](
[INTR_ProdID] [int] NOT NULL,
[INTR_QuantityOnHand] [decimal](8, 2) NOT NULL,
[INTR_QuantityAvailable] [decimal](8, 2) NOT NULL,
[INTR_TranType_Lookup_ID] [tinyint] NOT NULL,
[INTR_TranTypeIDValue] [varchar](20) NOT NULL,
[INTR_TranTypeQty] [int] NOT NULL,
[INTR_TranTypeDate] [datetime] NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[Sale_test](
[saleid] [int] NOT NULL,
[qty] [int] NULL,
[saledt] [datetime] NULL,
[prodid] [int] NULL
)
CREATE TABLE [dbo].[Order_test](
[orderid] [int] NOT NULL,
[qty] [int] NULL,
[orderdt] [datetime] NULL,
[prodid] [int] NULL
) ON [PRIMARY]
-- Order data Insert
INSERT INTO [PMDC].[dbo].[Order_test]([orderid],[qty],[orderdt],[prodid])
VALUES(1,70,'05/01/2011',1)
-- Sale data Insert
INSERT INTO [PMDC].[dbo].[sale_test]([saleid],[qty],[saledt],[prodid])
VALUES(1,20,'05/01/2011',1)
INSERT INTO [PMDC].[dbo].[sale_test]([saleid],[qty],[saledt],[prodid])
VALUES(1,30,'05/01/2011',1)
GO
INSERT INTO [PMDC].[dbo].[tbl_Inventory_Trail_test]([INTR_ProdID],[INTR_QuantityOnHand],[INTR_QuantityAvailable],[INTR_TranType_Lookup_ID],[INTR_TranTypeIDValue],[INTR_TranTypeQty],[INTR_TranTypeDate])
VALUES (1,-20,-20,1,'1',20,'02/01/2011') -- Sale data
GO
INSERT INTO [PMDC].[dbo].[tbl_Inventory_Trail_test]([INTR_ProdID],[INTR_QuantityOnHand],[INTR_QuantityAvailable],[INTR_TranType_Lookup_ID],[INTR_TranTypeIDValue],[INTR_TranTypeQty],[INTR_TranTypeDate])
VALUES (1,-50,-50,1,'2',30,'02/01/2011') -- Sale data
INSERT INTO [PMDC].[dbo].[tbl_Inventory_Trail_test]([INTR_ProdID],[INTR_QuantityOnHand],[INTR_QuantityAvailable],[INTR_TranType_Lookup_ID],[INTR_TranTypeIDValue],[INTR_TranTypeQty],[INTR_TranTypeDate])
VALUES (1,-50,20,2,'1',70,'02/01/2011') -- Order data
INTR_prod_ID INTR_QuantityOnHand INTR_QuantityAvailable INTR_TranType_Lookup_ID INTR_TranTypeIDValue INTR_TranTypeQty INTR_TranTypeDate
1 -20.00 -20.00 1 1 20 2011-02-01 00:00:00.000
1 -50.00 -50.00 1 2 30 2011-03-01 00:00:00.000
1 -50.00 20.00 1 1 70 2011-04-01 00:00:00.000
August 19, 2011 at 9:01 pm
Well, this is a good start. It would be nice to see what you have tried so far to solve your problem. That gives us a point of reference from which to help you.
August 19, 2011 at 9:03 pm
Not sure, but are the last three entries in your post the expected results when processing is completed? If not, need that also based on the sample data.
August 20, 2011 at 2:35 am
Hi
If you can please provide what Lynn has asked for, then I am sure that will help us.
Also:
Am I correct in thinking that rows in Order_test table are "inward" movements (eg purchases) and that Sales_test are "outward" movements?
Is the Inventory_Trail data as provided, what you expect it to be after processing the data in Order and sales tables?
Could you please explain what each column in Inventory_Trail represents....I am probably mistaken, but I dont think it corresponds with the data from the Order/Sales tables. Could you please confirm.
Will you ever be considering processing data for more than one warehouse/location?
What volume of data are you going to be loading on a regulr basis?
Have you got primary keys on any of the tables....none in the test data
In particular in the Sale_test table you have two rows with same sale_id/prod_id/saledt...is this correct?
Ideally can you provide us with a little more data...eg a few different prod_id and dates.
Well done for providing the intital code..I have very slightly altered it to use TEMP and show initial data.
USE [TEMPDB]
GO
--===== conditionally drop the test table
IF OBJECT_ID('TempDB.dbo.Order_test','U') IS NOT NULL
DROP TABLE TempDB.dbo.Order_test
CREATE TABLE [dbo].[Order_test](
[orderid] [int] NOT NULL,
[qty] [int] NULL,
[orderdt] [datetime] NULL,
[prodid] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Order_test]([orderid], [qty], [orderdt], [prodid])
SELECT 1, 70, '20110105 00:00:00.000', 1
select * from order_test
--===== conditionally drop the test table
IF OBJECT_ID('TempDB.dbo.Sale_test','U') IS NOT NULL
DROP TABLE TempDB.dbo.Sale_test
CREATE TABLE [dbo].[Sale_test](
[saleid] [int] NOT NULL,
[qty] [int] NULL,
[saledt] [datetime] NULL,
[prodid] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Sale_test]([saleid], [qty], [saledt], [prodid])
SELECT 1, 20, '20110105 00:00:00.000', 1 UNION ALL
SELECT 1, 30, '20110105 00:00:00.000', 1
select * from sale_test
--===== conditionally drop the test table
IF OBJECT_ID('TempDB.dbo.tbl_Inventory_Trail_test','U') IS NOT NULL
DROP TABLE TempDB.dbo.tbl_Inventory_Trail_test
CREATE TABLE [dbo].[tbl_Inventory_Trail_test](
[INTR_ProdID] [int] NOT NULL,
[INTR_QuantityOnHand] [decimal](8, 2) NOT NULL,
[INTR_QuantityAvailable] [decimal](8, 2) NOT NULL,
[INTR_TranType_Lookup_ID] [tinyint] NOT NULL,
[INTR_TranTypeIDValue] [varchar](20) NOT NULL,
[INTR_TranTypeQty] [int] NOT NULL,
[INTR_TranTypeDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tbl_Inventory_Trail_test]([INTR_ProdID], [INTR_QuantityOnHand], [INTR_QuantityAvailable], [INTR_TranType_Lookup_ID], [INTR_TranTypeIDValue], [INTR_TranTypeQty], [INTR_TranTypeDate])
SELECT 1, -20.00, -20.00, 1, N'1', 20, '20110102 00:00:00.000' UNION ALL
SELECT 1, -50.00, -50.00, 1, N'2', 30, '20110102 00:00:00.000' UNION ALL
SELECT 1, -50.00, 20.00, 2, N'1', 70, '20110102 00:00:00.000'
SELECT * FROM tbl_Inventory_Trail_test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 20, 2011 at 11:13 am
Thanks guys. Currently, I do not have access to my machine right now to provide the details but I will put them soon.
The tables I have provided are just samples which is just a snapshot of more bigger tables with other details and keys defined. They have millions of records in them.
I created these to make it more simpler to form queries out of this.
Will Post details soon.
August 22, 2011 at 2:26 pm
[INTR_ProdID],-- Product ID
[INTR_QuantityOnHand],-- Actual Quantity on Hand
[INTR_QuantityAvailable], -- Quantity available (tracks/accounts for the ordered qty where products are not received yet)
[INTR_TranType_Lookup_ID], -- Trantype tells if it is Order or Sale or adjustment etc..
[INTR_TranTypeIDValue], -- Trantype ID gives the ORder ID or Sale ID etc.
[INTR_TranTypeQty], -- actual qty for the sale or order (optional)
[INTR_TranTypeDate] -- Transaction of sale or order
[INTR_Add date]-- QOH trail add date
--This was the query I was trying but this won't get the QOH when it is inserting. Is there a way to get the latest qoh and add while it is inserting.
-- Should work for both cases of first time product entry or for existing product entry
INSERT INTO [dbo].[tbl_Inventory_Trail_test]
([INTR_ProdID], [INTR_QuantityOnHand], [INTR_QuantityAvailable], [INTR_TranType_Lookup_ID], [INTR_TranTypeIDValue], [INTR_TranTypeQty], [INTR_TranTypeDate])
select
prodid,isnull(intr_quantityonhand,0)+qty,0,1,saleid,qty,saledt as Summary
from sale_test as A
left join
(
select
row_number() over (partition by [INTR_ProdID] order by [INTR_TranTypeDate] DESC) as rownum,
*
from [tbl_Inventory_Trail_test]
)B
on B.[INTR_ProdID] = A.prodid and B.rownum=1
Note: As I had mentioned before, these tables are only sample which represents the orginal table that has all the keys and constraints set up already. For convinence purposes
I created them to focus on just the logic.
Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply