Create select statement for the query

• pwalter83

  SSChampion

  Points: 14575

  More actions

  October 29, 2012 at 4:00 am

  #1553662

  pwalter83 (10/24/2012)

  ---

  Eugene Elutin (10/18/2012)

  ---

  Which tables all of these PLACE_OF_DELIVERY, PLACE_OF_RECEIPT and saisan_month columns come from?

  They are not in your DDL and you didn't use table aliases in your query...

  Hi Eugene,

  I have amended my query and its more easy to understand now and I have also developed some code (still incomplete and in bold) for the issue I have:

  select

  SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2008' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO08,

  SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2009' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO09,

  SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2010' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO10,

  SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2011' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO11

  FROM NCV_BL NB

  INNER JOIN MiniGapp..MG_BILL_OF_LADING BOL

  ON NB.BL_ID = BOL.BL_ID

  LEFT JOIN

  (

  SELECT distinct

  MAX(NCV.BL_ID) AS BL_ID,

  SUM(case when Left(saisan_month,4) = '2011' and PLACE_OF_DELIVERY like 'GB%' THEN TEU END) AS GB_TS08,

  SUM(case when Left(saisan_month,4) = '2011' and PLACE_OF_DELIVERY like 'DE%' THEN TEU END) AS DE_TS08

  FROM NCV_BL NCV

  INNER JOIN MG_BILL_OF_LADING AS bol ON bol.BL_ID = NCV.BL_ID

  INNER JOIN MG_VSLVOY_HEADER AS vh ON bol.VESSEL_CD = vh.VESSEL_CD

  INNER JOIN MG_VSLVOY_PORT_CONTROL AS vpc ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

  INNER JOIN MG_VSLVOY_SCHEDULE AS vs ON vpc.VSLVOY_SCHEDULE_ID = vs.VSLVOY_SCHEDULE_ID

  WHERE NCV.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE)

  ) NA

  ON NB.BL_ID = NA.BL_ID

  Thanks,

  Paul

  Hi,

  Can someone please help on this ?

  Thanks,

  Paul