April 6, 2017 at 1:43 pm
Hello,
We have two tables where F2F and online promotional activities are stored. What I'm trying to achieve here is to track down the activities with different timestamps. F2F and online promotional activities are expected to be logged within reasonable timeframe. Let's suppose that a F2F activity is logged at 10:00 AM, then the online promotional activity is expected to be performed between 10:00 - 10:30 AM.
Please find the sample data below.
CREATE TABLE OnlinePromotions (
ActivityID nvarchar(255),
MaterialID nvarchar(255),
MaterialName nvarchar(255),
MaterialPageID nvarchar(255),
MaterialPageName nvarchar(255),
SequenceNumber int,
StartTime datetime,
EndTime datetime
);
INSERT INTO OnlinePromotions (ActivityID, MaterialID, MaterialName, MaterialPageID, MaterialPageName, SequenceNumber, StartTime, EndTime)
VALUES ('AKXA-G3E9S9', 'AKXA-DKHXPU', 'CAR_24_Saat', 'AKXA-DKHXQ1', '24Saat_GRA', 2, '2016-01-05 10:56:18.000', '2016-01-05 10:56:55.000'),
('AKXA-G3E9S9', 'AKXA-DKHXPU', 'CAR_24_Saat', 'AKXA-EJTYQ4', '24Saat_Kapak', 1, '2016-01-05 10:56:08.000', '2016-01-05 10:56:18.000'),
('AKXA-G3E9SJ', 'AKXA-DKHXPU', 'CAR_24_Saat', 'AKXA-DKHXQ1', '24Saat_GRA', 2, '2016-01-05 10:54:36.000', '2016-01-05 10:55:07.000'),
('AKXA-G3E9SJ', 'AKXA-DKHXPU', 'CAR_24_Saat', 'AKXA-EJTYQ4', '24Saat_Kapak', 1, '2016-01-05 10:54:05.000', '2016-01-05 10:54:36.000'),
('AKXA-G67ZZE', 'AKXA-DYPSC8', 'CAR_Brosur', 'AKXA-DYPSCC', 'CAR_Index', 1, '2016-01-05 11:02:56.000', '2016-01-05 11:03:08.000'),
('AKXA-G67ZZE', 'AKXA-DYPSC8', 'CAR_Brosur', 'AKXA-DYPSDE', 'CAR_KUB_SSI', 2, '2016-01-05 11:03:08.000', '2016-01-05 11:03:29.000'),
('AKXA-G923U3', 'AKXA-EJGDTO', 'SIN_DIV', 'AKXA-EJGDTS', 'DIV_KAPAK', 1, '2016-01-04 09:03:36.000', '2016-01-04 09:04:20.000'),
('AKXA-G923U3', 'AKXA-EJGDTO', 'SIN_DIV', 'AKXA-EJGDU5', 'DIV_DOZ', 2, '2016-01-04 09:04:20.000', '2016-01-04 09:04:26.000');
CREATE TABLE Activities2016 (
ActivityID nvarchar(255),
SalesPerson nvarchar(255),
SalesPersonID nvarchar(255),
CustomerID nvarchar(255),
CustomerType nvarchar(255),
StartTime datetime,
EndTime datetime
);
INSERT INTO Activities2016 (ActivityID, SalesPerson, SalesPersonID, CustomerID, CustomerType, StartTime, EndTime)
VALUES ('AKXA-G3E9S9', 'SP1', 'AHKA-11B4SQ', 'AHKA-13EWSK', 'Type1', '2016-01-05 09:15:00.000', '2016-01-05 09:30:00.000'),
('AKXA-G3E9SJ', 'SP2', 'AHKA-11B4SQ', 'AHKA-14FFLF', 'Type1', '2016-01-05 09:30:00.000', '2016-01-05 09:45:00.000'),
('AKXA-G67ZZE', 'SP3', 'AHKA-11B4SQ', 'AHKA-14GSIT', 'Type2', '2016-01-05 09:00:00.000', '2016-01-05 09:15:00.000'),
('AKXA-G923U3', 'SP4', 'AKXA-FT7RJ7', 'AHKA-134MD6', 'Type3', '2016-01-04 09:00:00.000', '2016-01-04 09:15:00.000');
The desired output is attached. As you may notice that Activity ID's are unique and there are no duplicate data in Activities2016 table, but in the OnlinePromotion table there are duplicate Activity ID's, basically because Sales Persons have presented two or more promotion materials in their activities, so what I need to do is to get the MIN date for "StartTime" and get the MAX date for "EndTime" to compute the elapsed time.
I would appreciate if you can help me out in this?
I tried to return the data as follows but I suspect that there is something wrong with it.
SELECT
T1.ActivityID,
T1.StartTime,
T1.EndTime,
T2.StartTime,
T2.EndTime
FROM
Activities2016 T1
INNER JOIN OnlinePromotions OP ON AC.[ActivityID] = OP.[ActivityID]
INNER JOIN (
SELECT ActivityID, StartTime = MIN([StartTime]), EndTime = MAX([EndTime])
FROM OnlinePromotions
GROUP BY [ActivityID]
) T2 ON T2.[ActivityID] = T1.[ActivityID] AND T2.StartTime = T1.StartTime AND T2.EndTime = T1.EndTime
Thanks,
seismicbeat
April 6, 2017 at 11:42 pm
Any ideas/comments? I tried to provide the sample data in a correct way to facilitate things, but if not please tell me, so that I can fix it. 🙂
April 7, 2017 at 12:32 am
Your requirements for filtering the data are quite vague.
The code below will join the data, and return it all. You can tweak the WHERE as needed.
WITH cteOnlinePromotions AS (
SELECT ActivityID, StartTime = MIN(StartTime), EndTime = MAX(EndTime)
FROM #OnlinePromotions
GROUP BY ActivityID
)
SELECT
AC.ActivityID
, AC.SalesPersonID
, AC.CustomerID
, AC.StartTime
, AC.EndTime
, OnlinePromStartTime = OP.StartTime
, OnlinePromEndTime = OP.EndTime
FROM #Activities2016 AS AC
INNER JOIN cteOnlinePromotions AS OP
ON AC.ActivityID = OP.ActivityID
WHERE OP.StartTime > AC.EndTime /* OnlinePromotions starts after Activity EndTime */
--WHERE OP.StartTime > DATEADD(mi, 30, AC.EndTime) /* OnlinePromotions starts after Activity StartTime+30mins */
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply