Skip to Content
Oct 28, 2019 at 11:46 PM

Query activities, oportunities and oportunities with specific stage



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.