February 2, 2017 at 1:40 pm
Hello,
I have a query that I would like to just group by the (LastInteractiveDate - by day) but not the other columns.
Below is my query
SELECT DISTINCT
CK.PRIMARY_ID AS 'TID',
P.NAME AS 'Name 1',
A.NAME AS 'Name 2',
T.CHANNEL AS 'Channel',
T.NAME AS 'Touchpoint Name',
CONVERT(DATE,MAX(E.DATE),112) AS 'LastActivityDate'
FROM TABLE_1 TI
INNER JOIN [TABLE_2] E ON E.ID = TI.ID
AND E.WORK_ID = TI.WORK_ID
AND E.T_ID = TI.T_ID
INNER JOIN [TABLE_3] CK ON CK.PRIMARY_ID = TI.PRIMARY_ID
AND CK.T_ID = TI.T_ID
AND E.WORK_ID = CK.WORK_ID
AND CK.T_ID = E.T_ID
INNER JOIN [TABLE_4] T ON T.T_ID = E.T_ID
AND T.TOUCH_ID = E.TOUCH_ID
INNER JOIN [TABLE_5] P ON P.T_ID = CK.T_ID
AND P.WORK_ID = CK.WORK_ID
AND P.PROP_ID = E.PROP_ID
INNER JOIN [TABLE_6] A ON A.ACTIVITY_ID = E.ACTIVITY_ID
WHERE CK.[API] <> 'one-tid'
AND P.T_ID = 7240471331262603468
AND P.WORK_ID = 2168
AND DATEDIFF(DAY, CONVERT(DATE,CONVERT(VARCHAR(10),E.DATE,101)), CONVERT(VARCHAR(10),GETDATE(),101)) <= 90
GROUP BY CK.PRIMARY_ID, P.NAME, A.NAME, T.CHANNEL, T.NAME
Is there a way I can just group by the E.DATE column through maybe a CTE or other type of function.
Thank you for your help.
February 2, 2017 at 2:12 pm
PFlorenzano-641896 - Thursday, February 2, 2017 1:40 PMHello,I have a query that I would like to just group by the (LastInteractiveDate - by day) but not the other columns.
Below is my query
SELECT DISTINCT
CK.PRIMARY_ID AS 'TID',
P.NAME AS 'Name 1',
A.NAME AS 'Name 2',
T.CHANNEL AS 'Channel',
T.NAME AS 'Touchpoint Name',
CONVERT(DATE,MAX(E.DATE),112) AS 'LastActivityDate'
FROM TABLE_1 TI
INNER JOIN [TABLE_2] E ON E.ID = TI.ID
AND E.WORK_ID = TI.WORK_ID
AND E.T_ID = TI.T_ID
INNER JOIN [TABLE_3] CK ON CK.PRIMARY_ID = TI.PRIMARY_ID
AND CK.T_ID = TI.T_ID
AND E.WORK_ID = CK.WORK_ID
AND CK.T_ID = E.T_ID
INNER JOIN [TABLE_4] T ON T.T_ID = E.T_ID
AND T.TOUCH_ID = E.TOUCH_ID
INNER JOIN [TABLE_5] P ON P.T_ID = CK.T_ID
AND P.WORK_ID = CK.WORK_ID
AND P.PROP_ID = E.PROP_ID
INNER JOIN [TABLE_6] A ON A.ACTIVITY_ID = E.ACTIVITY_ID
WHERE CK.[API] <> 'one-tid'
AND P.T_ID = 7240471331262603468
AND P.WORK_ID = 2168
AND DATEDIFF(DAY, CONVERT(DATE,CONVERT(VARCHAR(10),E.DATE,101)), CONVERT(VARCHAR(10),GETDATE(),101)) <= 90
GROUP BY CK.PRIMARY_ID, P.NAME, A.NAME, T.CHANNEL, T.NAMEIs there a way I can just group by the E.DATE column through maybe a CTE or other type of function.
Thank you for your help.
What do you expect in your output? A rowcount?
There's scope for performance gains - the date filter isn't SARGable.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 2, 2017 at 2:16 pm
I'm guessing (because you didn't provide sample data and expected results).
SELECT DISTINCT
CK.PRIMARY_ID AS 'TID',
P.NAME AS 'Name 1',
A.NAME AS 'Name 2',
T.CHANNEL AS 'Channel',
T.NAME AS 'Touchpoint Name',
e.LastActivityDate
FROM TABLE_1 TI
CROSS APPLY
(
SELECT TOP (1) CONVERT(DATE,E.DATE,112) AS LastActivityDate
FROM [TABLE_2] E
WHERE E.ID = TI.ID
AND E.WORK_ID = TI.WORK_ID
AND E.T_ID = TI.T_ID
ORDER BY LastActivityDate DESC
) E
INNER JOIN [TABLE_3] CK ON CK.PRIMARY_ID = TI.PRIMARY_ID
AND CK.T_ID = TI.T_ID
AND E.WORK_ID = CK.WORK_ID
AND CK.T_ID = E.T_ID
INNER JOIN [TABLE_4] T ON T.T_ID = E.T_ID
AND T.TOUCH_ID = E.TOUCH_ID
INNER JOIN [TABLE_5] P ON P.T_ID = CK.T_ID
AND P.WORK_ID = CK.WORK_ID
AND P.PROP_ID = E.PROP_ID
INNER JOIN [TABLE_6] A ON A.ACTIVITY_ID = E.ACTIVITY_ID
WHERE CK.[API] <> 'one-tid'
AND P.T_ID = 7240471331262603468
AND P.WORK_ID = 2168
AND DATEDIFF(DAY, LastActivityDate, GETDATE()) <= 90
Also, WHY?!? are you converting your dates to char and back to date to get the DATEDIFF in your WHERE clause?
Drew
Edit: Forgot to remove the MAX function in the subquery.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply