Getting related records setbased results
-
I have a table that has multiple transactions for stock items.
this table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? ive attached scrips below, but if ive missed anything please let me know
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
--Off those 'OnTranport' items get the next 'Dropped' item of the same stockitemid, CreatedDate
SELECT TOP 1
s3.StockItem_Id, s3.CreatedDate, s3.StockTransactionType
FROM
[dbo].[StockTransaction] s3
INNER JOIN
(
--------------------------------------------------------------------------------------
SELECT -- of those inducted items get the 'OnTranport' items of the same stockitemid, CreatedDate
s2.StockItem_Id, s2.CreatedDate, s2.StockTransactionType
FROM
[dbo].[StockTransaction] s2
INNER JOIN (
--------------------------------------------------------------------------------------
--Inner level only looking at items that have been inducted to begin with
Select
s1.CreatedDate, s1.StockItem_Id
FROM
[dbo].[StockTransaction] s1
WHERE
s1.[StockItem_Id]=@StockItemID AND
s1.CreatedDate=@CreateDate AND
s1.StockTransactionType='Inducted'
--------------------------------------------------------------------------------------
)
sub1 ON sub1.StockItem_Id=s2.StockItem_Id
WHERE
s2.CreatedDate=sub1.CreatedDate AND
s2.StockTransactionType='OnTranport'
--------------------------------------------------------------------------------------
)
sub2 ON sub2.StockItem_Id=s3.StockItem_Id
WHERE
s3.CreatedDate>sub2.CreatedDate AND
s3.StockTransactionType='Dropped'
ORDER BY
s3.CreatedDate ASC
-
Does this return the same results as your original query?
DECLARE @StockItemID nVarchar(128)DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
--Off those 'OnTranport' items get the next 'Dropped' item of the same stockitemid, CreatedDate
SELECT TOP 1
s3.StockItem_Id, s3.CreatedDate, s3.StockTransactionType
FROM [dbo].[StockTransaction] sub1
INNER JOIN [dbo].[StockTransaction] s2
ON s2.StockItem_Id = sub1.StockItem_Id
AND s2.CreatedDate = sub1.CreatedDate
INNER JOIN [dbo].[StockTransaction] s3
ON s3.StockItem_Id = sub1.StockItem_Id
AND s3.CreatedDate > sub1.CreatedDate
WHERE
sub1.StockTransactionType = 'Inducted'
AND sub1.[StockItem_Id] = @StockItemID
AND sub1.CreatedDate = @CreateDate
AND s2.StockTransactionType = 'OnTranport'
AND s3.StockTransactionType = 'Dropped'
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Not quite. if you look at a specific stock item using the query below you can see there is one valid sequence..
SELECT
*
FROM
[dbo].[StockTransaction] sub1
WHERE sub1.StockItem_Id='45a8025c-6977-470e-95a5-78a91473913c' order by CreatedDate,
(CASE WHEN StockTransactionType='Inducted' THEN 1 WHEN StockTransactionType='OnTranport' THEN 2 WHEN StockTransactionType='Dropped' THEN 3 END)
Running your original sql brings back 1 record (correct one) but because im wanting to get back a full result set i would have to remove the TOP 1, which then brings back the second drop which isnt valid.
SELECT
s3.StockItem_Id, s3.CreatedDate, s3.StockTransactionType
FROM
[dbo].[StockTransaction] sub1
INNER JOIN [dbo].[StockTransaction] s2 ON s2.StockItem_Id = sub1.StockItem_Id AND s2.CreatedDate = sub1.CreatedDate
INNER JOIN [dbo].[StockTransaction] s3 ON s3.StockItem_Id = sub1.StockItem_Id AND s3.CreatedDate > sub1.CreatedDate
WHERE
sub1.StockTransactionType = 'Inducted'
--AND sub1.[StockItem_Id] = @StockItemID
--AND sub1.CreatedDate = @CreateDate
AND s2.StockTransactionType = 'OnTranport'
AND s3.StockTransactionType = 'Dropped'
and sub1.[StockItem_Id]='45a8025c-6977-470e-95a5-78a91473913c'
I think this is the same issue im running into because there can be random, unrelated/orphaned 'Induct', 'OnTransport' and 'Dropped' that arent valid as they are not sequentially one after the other.
-
Can you set up some sample data to demonstrate? You will want a temp table for this e.g. #StockTransaction, and only the columns which appear somewhere in the query. I can see what you're getting at and there are several ways of going about it: sample data gives folks something to code against (and a tested code sample for you too).
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thanks Chris There is a CreateScript.txt attached to the post, cant you see it?
-
ps_vbdev (9/29/2015)
Thanks Chris There is a CreateScript.txt attached to the post, cant you see it?No. I get a 403 - Forbidden.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
hmmm, Ive removed them and re-added them.
-
ps_vbdev (9/29/2015)
hmmm, Ive removed them and re-added them.Your website still doesn't trust me 🙁
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ok try this link to my skydrive.
-
ps_vbdev (9/29/2015)
ok try this link to my skydrive.Local policy prevents me from accessing this at work. I'll try at home.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
[/URL]
[/URL]
[/URL]
[/URL]Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply