April 2, 2012 at 6:05 pm
Hello SQL Experts,
I have a Inventory data like below
Id|Trantype|TranID|TranDate|Qty|QOH|
1|S|S1|2012-01-01|-100|-100
2|OS|O0|2012-01-02|100|0
3|S|S1|2012-01-03|-100|-100
4|OS|O1|2012-01-04|100|0
5|S|S2|2012-01-06|-50|-50
6|OR|O1|2012-01-09|0|-150
7|OS|O2|2012-02-01|100|-50
8|OR|O2|2012-02-02|0|-150
S -Sale
OS - Order Submitted
OR - Order Received
I like to exclude all the orders that did not receive any qty back and get the last valid transaction date.
For e.g
From the above example I need ID-5 that has 2012-01-06
My approach was to get the qty =0 and its corresponding TranID and delete all the records with that tranid
then get the last row
Like in above case all tranid with O1 and O2 will be deleted.
I like someone to help with the query.
Thanks
Kris
April 2, 2012 at 6:33 pm
If you can take a look at the first link in my signature (the left side ones) you'll see a way to present data that makes it easy for us to consume and to test against. As a bonus, you'll receive tested code in your response.
Can you clarify what you're looking for? I see no way to associate a Sale with an OrderSubmittal and an OrderReceived. It looks like you have to do it by row-positioning, which is a painful way to try to deal with this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 3, 2012 at 11:47 am
Sure Craig.
At this point I do not need to relate Sale and Order. I just need to exclude the orders(both submitted and received that has same order id) that did not receive any qty (Qty column=0 for received order). After I exclude them I need to get the last transaction date of the active record (it can be a sale or a valid order record)
Note: This is a Inventory trail table where a record goes in there whenever an order or sale happens.
CREATE TABLE [dbo].[Invtrail_Test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Trantype] [char](11) NOT NULL,
[TranID] [char](25) NOT NULL,
[TranDate] [datetime] NULL,
[Qty] [int] NOT NULL,
[QOH] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('C',
,'C1',
,'03/01/12',
,-100,
,-100
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OS',
,'O1',
,'03/04/12',
,100,
,0
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('C',
,'C4',
,'03/06/12',
,-50,
,-50
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OR',
,'O1',
,'03/09/12',
0,
,-150
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OS',
,'O2',
,'04/01/12',
,100,
,-50
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OR',
,'O2',
,'04/02/12',
0,
,-150
)
April 3, 2012 at 11:48 am
Based on your sample data above, what is the expected results you are looking for from the query?
April 3, 2012 at 12:14 pm
I like to get this record basically I care only about the transaction date which is 2012-01-06
5|S|S2|2012-01-06|-50|-50
Thanks
April 3, 2012 at 3:21 pm
misstryguy (4/3/2012)
Sure Craig.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 49
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 63
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 77
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 91
Incorrect syntax near ','.
I just need to exclude the orders(both submitted and received that has same order id) that did not receive any qty (Qty column=0 for received order). After I exclude them I need to get the last transaction date of the active record (it can be a sale or a valid order record)
In your original data, TranID O2 is non-0, both in summation and in individual values. How do you backtrack to prior to ID 7/8 to 5 when neither of these are excluded? If you're doing a directly to date ordering, 01/09 is later than 01/06 on ID 6 and is also non-0. Does the OS being 0 and being related to the OR being -50 exclude it so that ANY component of a TranID being 0 means it's excluded?
And why is O2 deleted in the original data? Neither are 0 quantity.
The business rules and description vs. what you describe as the correct answer don't seem to coincide.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 3, 2012 at 3:47 pm
Hi Craig,
I have corrected the script below.
This is related to process called true-up, where we need to identify last valid transaction date that affected the quantity on hand of a product. That date should be greater than 60 days from now.
Valid transaction excludes any orders that were not fulfilled like the example above were both order O1 and O2 did not fulfill has the qty received were zero.
I am in the first step of excluding these transactions and then need to check the 60 day validation.
I put a query like below which is giving me right result but I am not sure about the efficiency considering the size of this table going to be.
select id,TranID,TranDate,qty,qoh,rownum -- into #temp2
from
(SELECT id,TranID,TranDate,qty,qoh,row_number() over(order by id desc) as rownum
FROM Invtrail_Test where tranid not in
(SELECT TranID FROM Invtrail_Test where qty =0)) as t
where rownum=1
CREATE TABLE [dbo].[Invtrail_Test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Trantype] [char](11) NOT NULL,
[TranID] [char](25) NOT NULL,
[TranDate] [datetime] NULL,
[Qty] [int] NOT NULL,
[QOH] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('C',
'C1',
'03/01/12',
-100,
-100
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OS',
'O1',
'03/04/12',
100,
0
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('C',
'C4',
'03/06/12',
-50,
-50
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OR',
'O1',
'03/09/12',
0,
-150
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OS',
'O2',
'04/01/12',
100,
-50
)
INSERT INTO [dbo].[Invtrail_Test]
([Trantype]
,[TranID]
,[TranDate]
,[Qty]
,[QOH]
)
VALUES
('OR',
'O2',
'04/02/12',
0,
-150
)
select * from Invtrail_Test
April 3, 2012 at 4:38 pm
misstryguy (4/3/2012)
Hi Craig,Valid transaction excludes any orders that were not fulfilled like the example above were both order O1 and O2 did not fulfill has the qty received were zero.
Ah, so it's not both, it's either. And I was reading from the wrong column, which wasn't helping me understand.
I put a query like below which is giving me right result but I am not sure about the efficiency considering the size of this table going to be.
No, that won't necessarily be pretty, but I'm not sure you're going to be able to avoid it. However, we might be able to thin down the plan a bit.
What's the indexing look like on the actual source table? That's going to vary how we approach this for optimal access.
That said, there's really no 'clean' way to do this. Depending on your indexing, my approach would probably be the following, mostly for readability and not needing to apply a windows function to the entire result set:
SELECT TOP 1
src.id, src.TranID, src.TranDate, src.qty, src.qoh
FROM
InvTrail_Test AS src
LEFT JOIN
(SELECT
TranID
FROM
InvTrail_Test
WHERE
Qty = 0
AND TranType IN ('OR', 'OS')
) AS drv
ONsrc.TranID = drv.TranID
WHERE
drv.TranID IS NULL
ORDER BY
src.ID DESC
But, again, the optimal query will depend heavily on the existing indexing. In particular, that query would desire TranID to be the clustered index while Qty/TranType were the leading edge in a non-clustered. My guess is the PK, (ID), is also defaulted as your clustered, which means that what's available in the existing NC indexes would help to define the right approach.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 5, 2012 at 10:27 am
Thanks Craig. I will try to work on the indexes and see.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply