August 21, 2018 at 11:10 pm
My fix is to find find each ActivityId 5,6 combination and insert a new row with ActivityId 66 that gets the CardDetails from ActivityId 5 and TransactionDateTime from ActivityId 6 under each CreditCardID.
Below is a snippet of the CreditCard table:
CreditCardID | ActivityId | CardDetails | TransactionDateTime | TradingId
____________________________________________________________
1 5 CardDetails1 2015-02-02 00:00:00.000 1
1 6 CardsDetails1 2016-02-02 00:00:00.000 1
1 5 CardDetails2 1999-02-02 00:00:00.000 2
1 6 CardsDetails2 2000-02-02 00:00:00.000 2
1 6 CardsDetails22 2000-02-03 00:00:00.000 2
SELECT DISTINCT T.CreditCardID
INTO #temp
FROM CreditCard T WITH(NOLOCK)
WHERE ActivityId IN (5, 6)
GROUP BY T.CreditCardID
HAVING SUM(CASE WHEN T.ActivityId = 5 THEN 1 ELSE 0 END) > 0 AND
SUM(CASE WHEN T.ActivityId = 6 THEN 1 ELSE 0 END) > 0
SELECT B.*
INTO #CreditCardTemp
FROM #temp A
JOIN CreditCard B
ON A.CreditCardID = B.CreditCardID
INSERT INTO CreditCard (CreditCardID, ActivityId, CardDetails, TransactionDateTime, TradingId)
SELECT A.CreditCardID, 66, B.CardDetails, A.TransactionDateTime, B.TradingId
FROM #CreditCardTemp A
JOIN #CreditCardTemp B
ON A.CreditCardID = B.CreditCardID
AND A.TradingId = B.TradingId
WHERE A.ActivityId = 5
AND B.ActivityId = 6
However when I run SELECT * FROM CreditCard I get a 66 row for each 6 row, when I should only get 1 66 row per 5 and 6 combination (irrespective of how many ActivityId 6 it has).
Eg. If I have one ActivityId 5 and two ActivityId 6 under the same CreditCardID and TradingId, I should only get one new 66 row, whereas here I am getting an extra 66 row.
Below is the output from the current query:
CreditCardID | ActivityId | CardDetails | TransactionDateTime | TradingId
____________________________________________________________
1 5 CardDetails1 2015-02-02 00:00:00.000 1
1 6 CardsDetails1 2016-02-02 00:00:00.000 1
1 5 CardDetails2 1999-02-02 00:00:00.000 2
1 6 CardsDetails2 2000-02-02 00:00:00.000 2
1 6 CardsDetails22 2000-02-03 00:00:00.000 2
1 66 CardsDetails1 2015-02-02 00:00:00.000 1
1 66 CardsDetails2 1999-02-02 00:00:00.000 2
1 66 CardsDetails22 1999-02-02 00:00:00.000 2
I believe I need to modify my ActivityId 6 join to only retrieve one ActivityId 6 record and the one record retrieved is the earliest of the Activity Id 6's.
If possible I would like to keep the same JOIN format rather than doing a sub query on each field that gets its data from the earliest 6 row, as my full table has a lot more fields and would therefore require a sub query for each field. However I need writing this JOIN.
Any help would be appreciated.
These are the commands that can be used to re-create the data for the problem:
CREATE TABLE CreditCard
(
CreditCardID INT,
ActivityId INT,
CardDetails VARCHAR(150),
TransactionDateTime DATETIME,
TradingId INT
)
INSERT INTO CreditCard (CreditCardID, ActivityId, CardDetails, TransactionDateTime, TradingId)
VALUES (1, 5, 'CardDetails1', '2015-02-02 00:00:00.000', 1)
INSERT INTO CreditCard (CreditCardID, ActivityId, CardDetails, TransactionDateTime, TradingId)
VALUES (1, 6, 'CardsDetails1', '2016-02-02 00:00:00.000', 1)
INSERT INTO CreditCard (CreditCardID, ActivityId, CardDetails, TransactionDateTime, TradingId)
VALUES (1, 5, 'CardDetails2', '1999-02-02 00:00:00.000', 2)
INSERT INTO CreditCard (CreditCardID, ActivityId, CardDetails, TransactionDateTime, TradingId)
VALUES (1, 6, 'CardsDetails2', '2000-02-02 00:00:00.000', 2)
INSERT INTO CreditCard (CreditCardID, ActivityId, CardDetails, TransactionDateTime, TradingId)
VALUES (1, 6, 'CardsDetails22', '2000-02-03 00:00:00.000', 2)
August 22, 2018 at 12:25 am
You don't need all the temp tables and,unless you don't mind the data being wrong, you really don't need the nolock hint:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
A.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
A.CardDetails,
A.ActivityId,
A.TradingId
August 22, 2018 at 8:36 am
You actually can do this with a single scan of the table. With the appropriate index, you can even prevent a costly sort operation.
WITH CreditCardCTE AS
(
SELECT
cc.CreditCardID,
cc.ActivityId,
cc.CardDetails, -- got rid of the MIN here
MIN(CASE WHEN cc.ActivityID = 6 THEN cc.TransactionDateTime END) OVER(PARTITION BY CreditCardID, TradingID) AS TransactionDateTime,
cc.TradingId
FROM #CreditCard cc
)
SELECT ccc.CreditCardID, 66 AS ActivityID, ccc.CardDetails, ccc.TransactionDateTime, ccc.TradingId
FROM CreditCardCTE ccc
WHERE ccc.ActivityId = 5;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2018 at 5:02 pm
andycadley - Wednesday, August 22, 2018 12:25 AMYou don't need all the temp tables and,unless you don't mind the data being wrong, you really don't need the nolock hint:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
A.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
A.CardDetails,
A.ActivityId,
A.TradingId
This works well, but lets say my requirements change and I need to get DisplayDetails from Activity 6 instead, I unfortunately run into the same problem of getting three 66 records instead of just one. I changed A.CardDetails to B.CardDetails as per the following:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
B.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
B.CardDetails,
A.ActivityId,
A.TradingId
Would I need to add a MIN equivalent for each field that draws from the earliest ActivityId 6 record (based on the earliest VANTransactionDateTime)?
August 22, 2018 at 5:03 pm
Markk1244 - Wednesday, August 22, 2018 5:02 PMandycadley - Wednesday, August 22, 2018 12:25 AMYou don't need all the temp tables and,unless you don't mind the data being wrong, you really don't need the nolock hint:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
A.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
A.CardDetails,
A.ActivityId,
A.TradingIdThis works well, but lets say my requirements change and I need to get DisplayDetails from Activity 6 instead, I unfortunately run into the same problem of getting three 66 records instead of just one. I changed A.CardDetails to B.CardDetails as per the following:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
B.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
B.CardDetails,
A.ActivityId,
A.TradingId
Would I need to add a MIN equivalent for each field that draws from the earliest ActivityId 6 record (based on the earliest VANTransactionDateTime)?
Also ignore the nolock hint, that was put in there by accident 🙂
August 23, 2018 at 8:09 am
Markk1244 - Wednesday, August 22, 2018 5:02 PMandycadley - Wednesday, August 22, 2018 12:25 AMYou don't need all the temp tables and,unless you don't mind the data being wrong, you really don't need the nolock hint:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
A.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
A.CardDetails,
A.ActivityId,
A.TradingIdThis works well, but lets say my requirements change and I need to get DisplayDetails from Activity 6 instead, I unfortunately run into the same problem of getting three 66 records instead of just one. I changed A.CardDetails to B.CardDetails as per the following:
Insert Into CreditCard(CreditCardID,ActivityId,CardDetails,TransactionDateTime,TradingId)
Select
A.CreditCardID,
66,
B.CardDetails,
Min(B.TransactionDateTime),
A.TradingId
From CreditCard A
Join CreditCard B
On A.CreditCardID = B.CreditCardID
And A.TradingId = B.TradingId
Where A.ActivityId =5
And B.ActivityId =6
Group By
A.CreditCardID,
B.CardDetails,
A.ActivityId,
A.TradingId
Would I need to add a MIN equivalent for each field that draws from the earliest ActivityId 6 record (based on the earliest VANTransactionDateTime)?
No, there is no guarantee that the MIN values for different fields will all come from the same record. If you want to pull them from the same record, you will need to use a ROW_NUMBER().
Also, your original criteria said you only had one 5 record, but two 6 records. Changing your criteria to pull details from the 6 record complicates things, because you have to decide WHICH 6 record to pull from, whereas previously there was only one 5record to pull from.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply