earnings_calendar.sql

WITH pj_con AS (
SELECT ult.ultimateParentCompanyId AS id, 
  count(DISTINCT billOfLadingNumber) AS shp, 
  sum(volumeTEU) AS teu
FROM XFL_PANJIVA.dbo.panjivaUSImport2020 im
JOIN XFL_PANJIVA.dbo.panjivaCompanyCrossRef ccr 
  ON ccr.identifierValue = im.conPanjivaId
JOIN XFL_CIQ.dbo.ciqCompanyUltimateParent ult 
  ON ult.companyId = ccr.companyid
WHERE im.arrivalDate <= '2019-06-30'
GROUP BY ult.ultimateParentCompanyId
UNION ALL
SELECT ult.ultimateParentCompanyId AS id, 
  count(DISTINCT billOfLadingNumber) AS shp, 
  sum(volumeTEU) AS teu
FROM XFL_PANJIVA.dbo.panjivaUSImport2019 im
JOIN XFL_PANJIVA.dbo.panjivaCompanyCrossRef ccr 
  ON ccr.identifiervalue = im.conPanjivaId
JOIN XFL_CIQ.dbo.ciqCompanyUltimateParent ult 
  ON ult.companyId = ccr.companyid
WHERE im.arrivalDate > '2019-06-30'
GROUP BY ult.ultimateParentCompanyId
), pj_shp AS (
SELECT ult.ultimateParentCompanyId AS id, 
  count(DISTINCT billOfLadingNumber) AS shp, 
  sum(volumeTEU) AS teu
FROM XFL_PANJIVA.dbo.panjivaUSImport2020 im
JOIN XFL_PANJIVA.dbo.panjivaCompanyCrossRef ccr 
  ON ccr.identifierValue = im.shpPanjivaId
JOIN XFL_CIQ.dbo.ciqCompanyUltimateParent ult 
  ON ult.companyId = ccr.companyid
WHERE im.arrivalDate <= '2019-06-30'
GROUP BY ult.ultimateParentCompanyId
UNION ALL
SELECT ult.ultimateParentCompanyId AS id, 
  count(DISTINCT billOfLadingNumber) AS shp, 
  sum(volumeTEU) AS teu
FROM XFL_PANJIVA.dbo.panjivaUSImport2019 im
JOIN XFL_PANJIVA.dbo.panjivaCompanyCrossRef ccr 
  ON ccr.identifiervalue = im.shpPanjivaId
JOIN XFL_CIQ.dbo.ciqCompanyUltimateParent ult 
  ON ult.companyId = ccr.companyid
WHERE im.arrivalDate > '2019-06-30'
GROUP BY ult.ultimateParentCompanyId
)
SELECT fet.keyDevEventTypeName
  , co.companyName
  , convert(varchar, fe.mostImportantDateUTC, 101) 'EarningsRelease'
  , sum(pj_con.shp) AS con_shp
  , sum(pj_con.teu) AS con_teu
  , sum(pj_shp.shp) AS shp_shp
  , sum(pj_shp.teu) AS shp_teu
FROM ciqFutEventToObjToEventType feo
JOIN ciqFutureEvent fe 
  ON fe.keyDevId = feo.keyDevId
JOIN ciqCompany co 
  ON co.companyId = feo.objectId
JOIN ciqFutureEventObjectRoleType fert 
  ON fert.keyDevToObjectRoleTypeId = feo.keyDevToObjectRoleTypeId
JOIN ciqFutureEventEventType fet 
  ON fet.keyDevEventTypeId = feo.keyDevEventTypeId
JOIN ciqCompanyUltimateParent ult ON ult.companyId = co.companyId 
JOIN pj_con ON pj_con.id = ult.companyId
JOIN pj_shp ON pj_shp.id = ult.companyId
WHERE feo.keyDevId 
  IN (SELECT a.keyDevId FROM ciqFutEventToObjToEventType a WHERE a.keyDevEventTypeId = 55)
  AND fe.mostImportantDateUTC BETWEEN getdate() AND dateadd(month, 6, getdate())
GROUP BY co.companyName, 
         convert(varchar, fe.mostImportantDateUTC, 101), 
         fet.keyDevEventTypeName, 
         fe.mostImportantDateUTC
order by fe.mostImportantDateUTC ASC