September 26, 2011 at 3:45 pm
Hello All,
I need to create transactions to either add or remove items from a list. The transactions are sent offsite to one of my vendors. The vendor then processes the transactions, updates the list on their side and sends me back not the gold copy of the items on the list but a gold copy of the transaction history.
On my side I generate an internal list. The task at hand is then to generate transactions that will make the external list consistent with the internal list.
The rules of the transactions are this:
-A transaction with a startdate and no end date places the item on the list
-A transaction that removes an item from the list will have an end date populated with the current date plus five days
-A transaction that removes an item from the list must have a startdate that matches the start date of the transaction that placed the item on the list
So given the below transactions:
-Item 1-Exists on the current list
-Item 2-Was on the list but it was removed so not on the current list
-Item 3-On the list, removed and then put back
-Item 4-On the list twice and removed twice
-Item 5-On the list twice, removed twice and the put back for the third time
Given these transactions the members of the current list are 1,3, 5
So given:
-the current transaction history (which represents the remote list at my vendors site); and
-an internally generated determination of items that should be on the list(the internal list)
I need to generate the transactions necessary to properly update the list, that is, make the remote list match the internal list.
CREATE TABLE #Transactions
(
ItemID int,
StartDate datetime,
EndDate datetime
)
--1
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (1,'1/1/2000',NULL)
--2
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (2,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (2,'1/1/2000','10/1/2002')
--3
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2000','10/1/2000')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2001',NULL)
--4
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2000','10/1/2000')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2003','9/1/2003')
--5
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001','12/1/2002')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003','9/1/2003')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2004','9/1/2003')
SELECT * FROM #Transactions
-A simple scenario
--The item is on the internal list and is not in the transaction history; generate an add transaction
--Generate and populate my internal list
CREATE TABLE #MyInternalList(ItemID int)
INSERT INTO #MyInternalList (ItemID) VALUES (6)
--generate the add transaction
SELECT
ItemID,
dateadd(dd,0, datediff(dd,0,getdate())) AS StartDate,
NULL AS EndDate
FROM #MyInternalList WHERE ItemID NOT IN (SELECT ItemID FROM #Transactions)
--The Item is not on the internal list
--The transactions history indicates that it is on the remote list
SELECT
ItemID,
StartDate AS StartDate,
dateadd(dd,0, datediff(dd,0,getdate()+5)) AS EndDate
FROM #Transactions
WHERE ItemID =1
--I am hard coding here for the demonstration purposes of generating an end transaction
-I'm obviously not looking for a hardcoded solution
DROP TABLE #Transactions
DROP TABLE #MyInternalList
I am not sure how to handle the scenarios presented by items 2-5, particulary where the items can be put on the list multiple times.
More sample data and desired results where each set of INSERTS represents the internal list at a point in time and the transaction history provided:
INSERT INTO #MyInternalList (ItemID) VALUES (6)
INSERT INTO #MyInternalList (ItemID) VALUES (1)
INSERT INTO #MyInternalList (ItemID) VALUES (2)
INSERT INTO #MyInternalList (ItemID) VALUES (3)
--Adds: 6 and 2
--Deletes: 5
INSERT INTO #MyInternalList (ItemID) VALUES (2)
INSERT INTO #MyInternalList (ItemID) VALUES (3)
--Adds: 2
--Deletes: 1 and 5
INSERT INTO #MyInternalList (ItemID) VALUES (1)
INSERT INTO #MyInternalList (ItemID) VALUES (3)
INSERT INTO #MyInternalList (ItemID) VALUES (5)
--No transactions generated
I know this sounds like a homework assignment from a bad teacher but its an actually business scenario.
I think the method is horrible but that is another story and out of my control...
I hope I have been clear and thanks to anyone who can help.
September 27, 2011 at 9:17 am
I guess I could go on the assumption that if the item appears on the list a number of times that is even then it has been added and removed.
--Identify the items that are on the list
SELECT ItemID
FROM #Transactions
GROUP BY ItemID
HAVING COUNT(ItemID) NOT IN (2,4,6,8,10)
September 27, 2011 at 9:36 am
Sorry Chrissy, it seems your thread was overlooked yesterday.
Chrissy321 (9/27/2011)
I guess I could go on the assumption that if the item appears on the list a number of times that is even then it has been added and removed.--Identify the items that are on the list
SELECT ItemID
FROM #Transactions
GROUP BY ItemID
HAVING COUNT(ItemID) NOT IN (2,4,6,8,10)
A better way to check for even numbers would be this
SELECT ItemID
FROM #Transactions
GROUP BY ItemID
HAVING COUNT(ItemID) % 2 = 0
For the rest of your problem, I don't really understand the logic. It's great that you've supplied sample data btw 😀
To help me understand, given this: -
IF OBJECT_ID('tempdb..#Transactions') IS NOT NULL
DROP TABLE #Transactions
CREATE TABLE #Transactions
(
ItemID int,
StartDate datetime,
EndDate datetime
)
--1
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (1,'1/1/2000',NULL)
--2
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (2,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (2,'1/1/2000','10/1/2002')
--3
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2000','10/1/2000')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2001',NULL)
--4
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2000','10/1/2000')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2003','9/1/2003')
--5
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001','12/1/2002')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003','9/1/2003')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2004','9/1/2003')
What exactly goes into #MyInternalList ?
September 27, 2011 at 9:47 am
I think that this will give you want you need without depending on having a complete history of the transactions.
;
WITH Items AS (
SELECT ItemID
, EndDate
, Row_Number() OVER( PARTITION BY ItemID ORDER BY IsNull(EndDate, StartDate) DESC ) AS rn
FROM #Transactions
)
SELECT ItemID
FROM Items
WHERE rn = 1
AND EndDate IS NULL
Drew
PS: It appears that the last entry for Item 5 in your sample should actually have a NULL end date.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 27, 2011 at 9:49 am
Thanks!
>>What exactly goes into #MyInternalList ?
MyInternalList is determined by internal business logic, it will change day to day. The external list is maintained by my vendor.
The transactions table is the only way I can infer what me vendor says is on their list.
I need to generate transactions to update the vendor maintained list based on the contents of my internal list.
If my vendor provided the actual list this would be easy. The logic would be
-if the item is on my internal list and not on my vendors list then generate an add transaction
-if the item is on my vendors list and not on my internal list then generate an delete transaction.
The complication is I don't have a distinct list of items on my vendors list. All I have is the transaction history from which hopefully the actual contents of the list can be inferred.
September 27, 2011 at 9:56 am
Correct, sorry about that. Revised
--5
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001','12/1/2002')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003','9/1/2003')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2004',NULL)
September 27, 2011 at 9:56 am
Chrissy321 (9/27/2011)
Correct, sorry about that. Revised--5
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001','12/1/2002')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003','9/1/2003')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2004',NULL)
In that case, Drew's clever little CTE is your solution 🙂
IF OBJECT_ID('tempdb..#Transactions') IS NOT NULL
DROP TABLE #Transactions
CREATE TABLE #Transactions
(
ItemID int,
StartDate datetime,
EndDate datetime
)
--1
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (1,'1/1/2000',NULL)
--2
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (2,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (2,'1/1/2000','10/1/2002')
--3
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2000','10/1/2000')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (3,'1/1/2001',NULL)
--4
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2000',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2000','10/1/2000')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (4,'1/1/2003','9/1/2003')
--5
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2001','12/1/2002')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003',NULL)
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2003','9/1/2003')
INSERT INTO #Transactions (ItemID,StartDate,EndDate) VALUES (5,'1/1/2004',NULL)
IF OBJECT_ID('tempdb..#MyInternalList') IS NOT NULL
DROP TABLE #MyInternalList
CREATE TABLE #MyInternalList(ItemID int);
--==Drew's CTE==--
--Ensure you terminate any preceding statements with a ;
WITH Items
AS (
SELECT ItemID
,EndDate
,Row_Number() OVER (
PARTITION BY ItemID ORDER BY IsNull(EndDate, StartDate) DESC
) AS rn
FROM #Transactions
)
--Then you can Insert into your internal list table from the CTE
INSERT INTO #MyInternalList
SELECT ItemID
FROM Items
WHERE rn = 1
AND EndDate IS NULL
SELECT *
FROM #MyInternalList
September 27, 2011 at 1:17 pm
Brilliant. Thank you all.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply