eric-oaktree

(WITH hs AS (
SELECT panjivarecordid, LEFT(REPLACE(REPLACE(unnest(string_to_array(p.hscode, ';')), '.', ''), ' ', ''), 4) AS "hs"
FROM panjivausimphscode2019 p
UNION ALL
SELECT panjivarecordid, LEFT(REPLACE(REPLACE(unnest(string_to_array(p.hscode, ';')), '.', ''), ' ', ''), 4) AS "hs"
FROM panjivausimphscode2010 p
), comp AS (
select DISTINCT ccr.identifierValue
from panjivaCompanyCrossRef ccr
join ciqCompanyUltimateParent ul on ul.companyId = ccr.companyId

eric-oaktree

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'

eric-oaktree

SELECT 'import' AS direction, 
  left(hsCode, 6) AS hs,
  shpmtOrigin,
  sum(itemValueUSD)
FROM XFL_PANJIVA.dbo.panjivaINImport2019
GROUP BY left(hsCode, 6), shpmtOrigin
UNION ALL
SELECT 'export' AS direction, 
  left(hsCode, 6) AS hs,
  shpmtDestination,

eric-oaktree

select datepart(year, shpmtDate) yr, datepart(month, shpmtDate) mo, conName, conPanjivaId, 
  itemOrigin, transportMethod, sum(volumeTEU) teu, sum(valueOfGoodsItemCIFUSD) usd, count(distinct mexicoShpmtId) shp
from (
select mexicoShpmtId, shpmtDate, conName, conPanjivaId, shpName, shpPanjivaId, 
  itemOrigin, transportMethod, volumeTEU, valueOfGoodsItemCIFUSD 
from XFL_PANJIVA.dbo.panjivaMXImport2019
where left(hsCode, 4) = '8708'
union all
select mexicoShpmtId, shpmtDate, conName, conPanjivaId, shpName, shpPanjivaId, 
  itemOrigin, transportMethod, volumeTEU, valueOfGoodsItemCIFUSD 

eric-oaktree

select sum(volumeteu) teu, datepart(year, imp.arrivalDate) yr, datepart(month,
  imp.arrivalDate) mo, shpmtOrigin, imp.conName, con.companyName, imp.shpName, shp.companyName
from (
  select im.*, left(trim(value), 4) hs
  from XFL_PANJIVA.dbo.panjivaUSImport2019 im
  join XFL_PANJIVA.dbo.panjivaUSImpHSCode2019 hs 
    on hs.panjivaRecordId = im.panjivaRecordId
  cross apply string_split(replace(hs.hscode, '.', ''),';')
  union all
  select im.*, left(trim(value), 4) hs

eric-oaktree

select sum(volumeteu), shpmtorigin, arrivaldate
from (
select *
from panjivausimport2019 im
join panjivausimpgoodsshpd2019 gs 
  on gs.panjivarecordid = im.panjivarecordid
union all
select *
from panjivausimport2018 im
join panjivausimpgoodsshpd2018 gs

eric-oaktree

select sum(volumeteu) teu, datepart(year, imp.arrivalDate) yr,
  datepart(month, imp.arrivalDate) mo, shpmtOrigin
from (
select im.*, left(trim(value), 4) hs
from XFL_PANJIVA.dbo.panjivaUSImport2019 im
join XFL_PANJIVA.dbo.panjivaUSImpHSCode2019 hs
  on hs.panjivaRecordId = im.panjivaRecordId
cross apply string_split(replace(hs.hscode, '.', ''),';')
union all
select im.*, left(trim(value), 4) hs

eric-oaktree

select carrier, shppanjivaid, shpname, conpanjivaid, conname, 
  date_part('year', arrivaldate), date_part('month', arrivaldate), 
  sum(volumeteu) teu, count(distinct billofladingnumber) shp
from (
select * from panjivausimport2019
union all
select * from panjivausimport2018
union all
select * from panjivausimport2017
union all

eric-oaktree

select comp.companyName, comp.companyId, shpcity, shpstateregion, shpmtorigin,
  sum(volumeteu), count(distinct billofladingnumber), datepart(year, arrivaldate) yr,
  datepart(month, arrivaldate) mo -- include shipper location information
from XFL_PANJIVA.dbo.panjivausimport2019 imp
join ( -- resolve ultimate parents
  select ccr.identifierValue, ccr.companyId, ul.ultimateParentCompanyId, co.companyName
  from XFL_PANJIVA.dbo.panjivaCompanyCrossRef ccr
  join XFL_CIQ.dbo.ciqCompanyUltimateParent ul on ul.companyId = ccr.companyId
  join XFL_CIQ.dbo.ciqCompany co on co.companyId = ul.ultimateparentcompanyid
  where ul.ultimateParentCompanyId in (select ultimateParentCompanyId from ciqCompanyUltimateParent)