Recurring values
-
SIMPLE QUESTION: when inventory is received, is the quantity remaining column null, zero or equal to the quantity received?
When inventory recieved, the quantity remaining column may or may not be zero. Assume we buy some chocolates. I may or may not not have the chocolates at that time. If I had then i supposed to use the chocolates i had previously first then the new ones. Here also we have to use the same.
NOT SO SIMPLE QUESTION: what's the data structure? DDL is now critical! Knowing what fields are in the table makes a huge difference in how to propose a solution.
Recieve table
Partno-------char(10)
RDate-----DateTime
Rquantity----int
RRate----float
RemainingQty---int
Issue Table
Partno-------char(10)
IDate-----DateTime
Iquantity----int
IRate----float
Resultant Table
Partno-------char(10)
RDate--DateTime
IDate-----DateTime
Iquantity----int
IRate----float
ShortTermVal---float
LongTermVal---float
QUESTION (may be easy, maybe not): Is there a separate record created for each row of received inventory for each issuing of inventory? You show 3 inventory records for A, with three different dates (and note that the illustration does NOT show inventory in date order! is that a typo?) Or is it sufficient to show one row of issued inventory, reflecting the total short term and long term value of issued inventory?
Yes for each recieved inventory and issue inventory seperate record is created. Date order is typo. I dont think it is possible to show the short term value and long term value in the same row when each time the issue and recieved rate may vary.
-
Heh... I think you're making a mistake using FLOAT on the RATEs (which I assume will be currency amounts of some sort). If you don't think so, try this...
DECLARE @FloatTest FLOAT
SET @FloatTest = .33
SELECT @FloatTest
SET @FloatTest = .35
SELECT @FloatTest
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Haven't figured out the long term/shorterm things yet, but here's some more realistic data to play with... created a single "work" table just to help imagine what's being received and issued and when...
--===== Setup the environment
USE TempDB --"Safe" place available on every instance of SQL Server
SET DATEFORMAT dmy --Had to change this because of data like '13/2/2007' in posted data
--===== If the test tables already exist, drop them so we can rerun
IF OBJECT_ID('TempDB.dbo.Receive','U') IS NOT NULL
DROP TABLE TempDB.dbo.Receive
IF OBJECT_ID('TempDB.dbo.Issue','U') IS NOT NULL
DROP TABLE TempDB.dbo.Issue
--===== Create the test tables
-- (Didn't clearly see what the PK might be, so added tablenameID as the PK
CREATE TABLE dbo.Receive
(
ReceiveID INT IDENTITY(1,1),
PartNo VARCHAR(10),
RDate DATETIME,
RRate INT,
RQuantity INT,
CONSTRAINT PK_Receive_ReceiveID PRIMARY KEY CLUSTERED (ReceiveID)
)
CREATE TABLE dbo.Issue
(
IssueID INT IDENTITY(1,1),
PartNo VARCHAR(10),
IDate DATETIME,
IRate INT,
IQuantity INT,
CONSTRAINT PK_Issue_IssueID PRIMARY KEY CLUSTERED (IssueID)
)
--===== Populate the tables with the posted example data
INSERT INTO dbo.Receive
(PartNo,RDate,RRate,RQuantity)
SELECT 'A','10/10/2006','10','5' UNION ALL
SELECT 'A','10/10/2006','12','5' UNION ALL
SELECT 'A','10/10/2006','14','5' UNION ALL
SELECT 'A','10/10/2006','16','5' UNION ALL
SELECT 'A','10/10/2007','20','50' UNION ALL
SELECT 'A','1/11/2006' ,'18','10' UNION ALL
SELECT 'A','2/12/2007','20','45' UNION ALL
SELECT 'B','10/12/2006','15','30' UNION ALL
SELECT 'B','13/2/2007' ,'25','20'
INSERT INTO dbo.Issue
(PartNo,IDate,IRate,IQuantity)
SELECT 'A','10/5/2007','18','10' UNION ALL
SELECT 'A','1/12/2007','20','45' UNION ALL
SELECT 'A','13/2/2007','20','15' UNION ALL
SELECT 'A','1/12/2008','20','55' UNION ALL
SELECT 'B','13/2/2007','20','20' UNION ALL
SELECT 'B','1/5/2007' ,'20','25'
--===== Set the date format back to something I'm more used to
SET DATEFORMAT mdy
--===== Create a temp table to do the FiFo in
IF OBJECT_ID('TempDB.dbo.FiFo','U') IS NOT NULL
DROP TABLE TempDB.dbo.FiFo
CREATE TABLE FiFo
(
FiFoID INT IDENTITY(1,1),
PartNo VARCHAR(10) NOT NULL,
RowType VARCHAR( 7) NOT NULL,
Date DATETIME NOT NULL,
Rate INT NOT NULL,
Quantity INT NOT NULL,
RunningBalance INT DEFAULT 0 NOT NULL,
ShortTerm INT DEFAULT 0 NOT NULL,
LongTerm INT DEFAULT 0 NOT NULL,
CONSTRAINT PK_FiFo_FiFoID PRIMARY KEY CLUSTERED (FiFoID)
)
--===== Add a "magic" index for the upcoming updates
CREATE INDEX IX_Magic ON FiFo (PartNo,Date,RowType DESC)
GO
---------------------------------------------------------------------------------------------
--===== Truncate the work table to start fresh
TRUNCATE TABLE dbo.FiFo
--===== Combine the data from both tables into the FiFo table
INSERT INTO FiFo
(PartNo, RowType, Date, Rate, Quantity)
SELECT PartNo,
RowType = 'Receive',
Date = RDate,
Rate = RRate,
Quantity = RQuantity
FROM dbo.Receive
UNION ALL ----------------------------------------------
SELECT PartNo,
RowType = 'Issue',
Date = IDate,
Rate = IRate,
Quantity = IQuantity
FROM dbo.Issue
ORDER BY PartNo, Date, RowType DESC
--===== Declare the local variables we'll need
DECLARE @PrevPartNo VARCHAR(10)
DECLARE @PrevDate DATETIME
DECLARE @RunningBalance INT
SET @PrevPartNo = ''
--===== Create a running balance just to see what's going on
UPDATE f
SET @RunningBalance = RunningBalance = CASE WHEN RowType = 'Receive'
THEN Quantity
WHEN RowType = 'Issue'
THEN -Quantity
END
+
CASE WHEN @PrevPartNo = PartNo
THEN @RunningBalance
ELSE 0
END,
@PrevPartNo = PartNo
FROM dbo.FiFo f WITH (INDEX(IX_Magic))
SELECT * FROM FiFo
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you very much Jeff. I will try this. I hope this works.
-
When I run this I got the following result. What does RunningBalance is Actually representing?
FiFoID PartNo RowType Date Rate Quantity RunningBalance ShortTerm LongTerm
----------- ---------- ------- --------------------------- ----------- ----------- -------------- ----------- -----------
1 A Receive 2006-10-10 00:00:00.000 10 5 5 0 0
2 A Receive 2006-10-10 00:00:00.000 12 5 10 0 0
3 A Receive 2006-10-10 00:00:00.000 14 5 15 0 0
4 A Receive 2006-10-10 00:00:00.000 16 5 20 0 0
5 A Receive 2006-11-01 00:00:00.000 18 10 30 0 0
6 A Issue 2007-02-13 00:00:00.000 20 15 15 0 0
7 A Issue 2007-05-10 00:00:00.000 18 10 5 0 0
8 A Receive 2007-10-10 00:00:00.000 20 50 55 0 0
9 A Issue 2007-12-01 00:00:00.000 20 45 10 0 0
10 A Receive 2007-12-02 00:00:00.000 20 45 55 0 0
11 A Issue 2008-12-01 00:00:00.000 20 55 0 0 0
12 B Receive 2006-12-10 00:00:00.000 15 30 30 0 0
13 B Receive 2007-02-13 00:00:00.000 25 20 30 0 0
14 B Issue 2007-02-13 00:00:00.000 20 20 10 0 0
15 B Issue 2007-05-01 00:00:00.000 20 25 5 0 0
-
Sum of the quantities for a given part. Quantity is add if the RowType is 'Receive', Quantity is subtracted if the RowType is 'Issue'. Another name for 'RunningBalance' might be 'Quantity on Hand'.
Like I said, still haven't figure out how to "distribute" the Issue quantities over the Receive quantities to do the necessary ShortTerm/LongTerm calculations. I'm thinking about that but wanted to post some setup code so others could take a whack at it as well (not just you
) --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
jeff, i think the answer to short term / long term will be [in pseudocode] a case statement (case datediff 1 year longterm, case datediff = 1 year ??? [short or long?], else raise error (bad date processing)).
chaitra - thank you for being so cooperative in sharing your details with us so we can help you! Not everyone is as responsive, patient, and polite as you have been.
-
Ummm.... not quite that simple... need to distribute a batch of QTY 15 issues against 4 batches of QTY 5 each and do the calculation and forward the remainder. Lot's of questions like how to know which batches to do that with. Obviously, the Issues should come from previous Receives... and I'm trying desparately not to use a loop.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Got part way there using a limited triangular join... it spawned 2 times as many rows as the original count of both tables and that's with just a couple of dates... I gotta bad feeling that a loop
is going to be more efficient on this one... I'll keep plugging.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Can we use .net interface for this, instead of trying only with sql statements? I know even then we need to run sql statements but just thinking that it may help to work with "remaining items".
-
Sure... can be done in any language... I just got bogged down at work.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply