Stumped and need help with a query, pretty please

[planetk2013]

I have two tables of dates & activitites by company. Table A are Major Activiities and Table B are Minor Activities (images attached). I would like to associate/link a minor activity to the Major activity that occured before it (DESIRED_VIEW image attached.
attempt #1:
SELECT MAJOR_ACTIVITY.[Company Name]
, MAJOR_ACTIVITY.[Major Activity]
, MAJOR_ACTIVITY.[Major Activity Date]
, MINOR_ACTIVITY.[Minor Activity]
, MINOR_ACTIVITY.[Minor Activity Date]
FROM
COMPANY_ACTIVITIES.MAJOR_ACTIVITY
INNER JOIN
COMPANY_ACTIVITIES.MINOR_ACTIVITY
ON (MAJOR_ACTIVITY.[Company Name]= MINOR_ACTIVITY.[Company Name])
WHERE (MINOR_ACTIVITY.[Minor Activity Date] >= MAJOR_ACTIVITY.[Major Activity Date])

It made sense at first but the results showed minor activities being associated with multiple Major Activities.

Probably a very simple answer and I will feel like a complete moron when you give the answer but I have to put it out here so I can sleep. Thank you!

Message was edited by: kworsham_343

[Debbie_Peabody]

Company name does not appear to be unique. You need to join on unique columns.

[itlnstln]

This one is going to be complicated since you can have multiple major/minor activities that occurred before/after each other. Therefore, we probably need to use some recursion to narrow down the results to get closest to your answer as possible. Since you don’t have anything to key on to associate the events, the best we can do is guess (intelligently, of course). I’ll start with your code and change/augment from there.

First, we need to the closest minor activity date to the major activity. We can change up the code a bit to accomplish this:

SELECT MAJOR_ACTIVITY.[Company Name]
, MAJOR_ACTIVITY.[Major Activity]
, MAJOR_ACTIVITY.[Major Activity Date]
, MIN(MINOR_ACTIVITY.[Minor Activity Date]) AS MIN_MINOR_ACT_DT
FROM
COMPANY_ACTIVITIES.MAJOR_ACTIVITY
INNER JOIN
COMPANY_ACTIVITIES.MINOR_ACTIVITY
ON (MAJOR_ACTIVITY.[Company Name]= MINOR_ACTIVITY.[Company Name]) AND (MINOR_ACTIVITY.[Minor Activity Date] >= MAJOR_ACTIVITY.[Major Activity Date])
GROUP BY 1,2,3;

Now all we need to do is use the query above as a derived table to constrain the original SQL to show the rest of the data:

SELECT A.[Company Name]
, A.[Major Activity]
, A.[Major Activity Date]
, B.[Minor Activity]
, B.[Minor Activity Date]
FROM
COMPANY_ACTIVITIES.MAJOR_ACTIVITY A,
COMPANY_ACTIVITIES.MINOR_ACTIVITY B,
(SELECT MAJOR_ACTIVITY.[Company Name]
, MAJOR_ACTIVITY.[Major Activity Date]
, MIN(MINOR_ACTIVITY.[Minor Activity Date]) AS MIN_MINOR_ACT_DT
FROM
COMPANY_ACTIVITIES.MAJOR_ACTIVITY
INNER JOIN
COMPANY_ACTIVITIES.MINOR_ACTIVITY
ON (MAJOR_ACTIVITY.[Company Name]= MINOR_ACTIVITY.[Company Name]) AND (MINOR_ACTIVITY.[Minor Activity Date] >= MAJOR_ACTIVITY.[Major Activity Date])
GROUP BY 1,2) C
WHERE A.[COMPANY NAME] = B.[COMPANY NAME]
AND A.[COMPANY NAME] = C.[COMPANY NAME]
AND A.[MAJOR ACTIVITY DATE] = C.[MAJOR ACTIVITY DATE]
AND B.[MINOR ACTIVITY DATE] = C.[MINOR ACTIVITY DATE]

UNION – TO GET THE MAJOR EVENTS WITHOUT MINOR EVENTS OCCURRING AFTERWARDS. THIS AVOIDS ANY “SQUIRRELINESS” IN USING OUTER JOINS IN THE FIRST SQL.

SELECT A.[Company Name]
, A.[Major Activity]
, A.[Major Activity Date]
,NULL AS [MINOR ACTIVITY]
,NULL AS [MINOR ACTIVITY DATE]
FROM
COMPANY_ACTIVITIES.MAJOR_ACTIVITY A
LEFT OUTER JOIN
(SELECT MAJOR_ACTIVITY.[Company Name]
, MAJOR_ACTIVITY.[Major Activity Date]
, MIN(MINOR_ACTIVITY.[Minor Activity Date]) AS MIN_MINOR_ACT_DT
FROM
COMPANY_ACTIVITIES.MAJOR_ACTIVITY
INNER JOIN
COMPANY_ACTIVITIES.MINOR_ACTIVITY
ON (MAJOR_ACTIVITY.[Company Name]= MINOR_ACTIVITY.[Company Name]) AND (MINOR_ACTIVITY.[Minor Activity Date] >= MAJOR_ACTIVITY.[Major Activity Date])
GROUP BY 1,2) C ON A.[COMPANY NAME] = C.[COMPANY NAME] AND A.[MAJOR ACTIVITY DATE] = C.[MAJOR ACTIVITY DATE]
WHERE C.[MAJOR ACTIVITY DATE] IS NULL;