Query activities, oportunities and oportunities with specific stage

[former_member550499]

Hello,

I was wondering if it is posible to have the next 3 queries in 1 query.

Activities Created

SELECT T2.[SlpName] 'Sales Agent', T1.[CardName] 'Client', month(T0.[CntctDate]) as Month, count(T0.[ClgCode]) 'activities created'

FROM OCLG T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

GROUP BY T2.[SlpName], T1.[CardName], month(T0.[CntctDate])

Opportunities Created

SELECT T1.[SlpName] as 'Sales Agent', T2.[CardName] as 'Client', month(T0.[OpenDate]) as Month, count(T0.[OpprId]) as 'Opportunities'

FROM OOPR T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

GROUP BY T1.[SlpName], T2.[CardName], month(T0.[OpenDate])

Opportunities with Stage #4 opened

SELECT T2.[SlpName] 'SAles Agent', T3.[CardName] Client, month(T1.[OpenDate]) as Month, count(T1.[Step_Id]) as 'Stage 4'

FROM OOPR T0 INNER JOIN OPR1 T1 ON T0.[OpprId] = T1.[OpprId] INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode WHERE T1.[Step_Id] ='4'

GROUP BY T2.[SlpName], T3.[CardName], month(T1.[OpenDate])

Final result will end up like the image.

Thanks in advance.

[KonradZaleski]

What about joining this subqueries together :

SELECT
	ACT.[Sales Agent], ACT.[Client], ACT.[Month], OPP_CREAT.[Opportunities], OPP_CREAT.[Stage 4], ACT.[activities created]
FROM
(
	(
	--Activities Created
		SELECT T2.[SlpName] 'Sales Agent', T1.[CardName] 'Client', month(T0.[CntctDate]) as Month, count(T0.[ClgCode]) 'activities created'


		FROM OCLG T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode


		GROUP BY T2.[SlpName], T1.[CardName], month(T0.[CntctDate])


	) ACT
	JOIN 
	(
	--Opportunities Created
		SELECT T1.[SlpName] as 'Sales Agent', T2.[CardName] as 'Client', month(T0.[OpenDate]) as Month, count(T0.[OpprId]) as 'Opportunities'


		FROM OOPR T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode


		GROUP BY T1.[SlpName], T2.[CardName], month(T0.[OpenDate])
	) OPP_CREAT ON ACT.[Sales Agent] = OPP_CREAT.[Sales Agent] AND  ACT.[Client] = OPP_CREAT.[Client] AND  ACT.[Month] = OPP_CREAT.[Month]
	JOIN
	(
	--Opportunities with Stage #4 opened
		SELECT T2.[SlpName] 'Sales Agent', T3.[CardName] Client, month(T1.[OpenDate]) as Month, count(T1.[Step_Id]) as 'Stage 4'


		FROM OOPR T0 INNER JOIN OPR1 T1 ON T0.[OpprId] = T1.[OpprId] INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode WHERE T1.[Step_Id] ='4'


		GROUP BY T2.[SlpName], T3.[CardName], month(T1.[OpenDate])
	) OPP_STAG ON OPP_CREAT.[Sales Agent] = OPP_STAG.[Sales Agent] AND  OPP_CREAT.[Client] = OPP_STAG.[Client] AND  OPP_CREAT.[Month] = OPP_STAG.[Month]
)

[former_member550499]

Thank you for your answer. It works with some cases but for example if we don't have an activity created for the sales agent and client in month 1 it won't show the opportunities created and opportunities with stage 4 opened in month 1.

It just shows the opportunities and stage 4 that have a month equal to an activity.