Display ALL rows even if no data exists

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  August 8, 2014 at 11:38 am

  #1736514

  pwalter83 (8/8/2014)

  ---

  ChrisM@Work (8/7/2014)

  ---

  I don't see any parameters in your code, Paul.

  Any ideas, Chris ??[/quote

  Hi Paul, I'm on break in Normandy with no access to SQL Server until tuesday. A question for you: can you please provide as much information as you can about the use of parameters and how you intend to use them to influence the result set from your query? Cheers.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• pwalter83

  SSChampion

  Points: 14575

  More actions

  August 13, 2014 at 9:46 am

  #1737668

  ChrisM@home (8/8/2014)

  ---

  pwalter83 (8/8/2014)

  ---

  ChrisM@Work (8/7/2014)

  ---

  I don't see any parameters in your code, Paul.

  Any ideas, Chris ??[/quote

  Hi Paul, I'm on break in Normandy with no access to SQL Server until tuesday. A question for you: can you please provide as much information as you can about the use of parameters and how you intend to use them to influence the result set from your query? Cheers.

  The parameters in the SSRS report would allow the users to select a value each from the dropdown boxes namely vessel and voyage and based on the selection the report should display data for the mentioned countries.

  If data for a particular country does not exist, it should still display the name of the country in the row as shown in the attachment. In the attachment, you would notice that Belgium, France and Portugal do not have data for vessel - VCRB and VOYAGE - 076W but are still shown.

  Thanks.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 13, 2014 at 12:47 pm

  #1737776

  I fail to understand what the discomfort level is with CTEs. It's kind of like having a subquery, but just that it appears BEFORE the rest of the query and gets a table name. It's not something you have to worry about whether or not it will work. I took an educated guess approach and put the following together, based on the last query text I saw:

  WITH OFFICES AS (

  SELECT 'KBE ANR' AS OFFICE_CD, 'BELGIUM' AS COUNTRY_DSC UNION ALL

  SELECT 'KBE ZEE', 'BELGIUM' UNION ALL

  SELECT 'KEU ANR','BELGIUM' UNION ALL

  SELECT 'KDE VIE','AUSTRIA' UNION ALL

  SELECT 'KDE BRE','GERMANY' UNION ALL

  SELECT 'KDE BRV','GERMANY' UNION ALL

  SELECT 'KDE DUS','GERMANY' UNION ALL

  SELECT 'KDE FRA','GERMANY' UNION ALL

  SELECT 'KDE HAM','GERMANY' UNION ALL

  SELECT 'KDE MUC','GERMANY' UNION ALL

  SELECT 'KDE STR','GERMANY' UNION ALL

  SELECT 'KFR BOD','FRANCE' UNION ALL

  SELECT 'KFR DKK','FRANCE' UNION ALL

  SELECT 'KFR FOS','FRANCE' UNION ALL

  SELECT 'KFR LEH','FRANCE' UNION ALL

  SELECT 'KFR LIO','FRANCE' UNION ALL

  SELECT 'KFR MRS','FRANCE' UNION ALL

  SELECT 'KFR PAR','FRANCE' UNION ALL

  SELECT 'EXAF PARIS','FRANCE' UNION ALL

  SELECT 'FRBAL BOD','FRANCE' UNION ALL

  SELECT 'KEU LEH','FRANCE' UNION ALL

  SELECT 'KEU RTM','NETHERLANDS' UNION ALL

  SELECT 'KNL RTM','NETHERLANDS' UNION ALL

  SELECT 'KDE PRG','CZECH REPUBLIC' UNION ALL

  SELECT 'CHGLS BSL','SWITZERLAND' UNION ALL

  SELECT 'KPT LIS','PORTUGAL' UNION ALL

  SELECT 'KPT OPO','PORTUGAL' UNION ALL

  SELECT 'ESBLR BCN','SPAIN' UNION ALL

  SELECT 'ESBLR MAD','SPAIN' UNION ALL

  SELECT 'ESBLR SDR','SPAIN' UNION ALL

  SELECT 'ESBLR TAR','SPAIN' UNION ALL

  SELECT 'ESBLR VCL','SPAIN' UNION ALL

  SELECT 'ESBLR VGO','SPAIN' UNION ALL

  SELECT 'ESBLR VLC','SPAIN' UNION ALL

  SELECT 'KES BCN','SPAIN' UNION ALL

  SELECT 'KES BIO','SPAIN' UNION ALL

  SELECT 'KES MAD','SPAIN' UNION ALL

  SELECT 'KES VGO','SPAIN' UNION ALL

  SELECT 'KES VLC','SPAIN' UNION ALL

  SELECT 'IEIRL DUB','IRELAND' UNION ALL

  SELECT 'IEIRL ORK','IRELAND' UNION ALL

  SELECT 'KSE HES','SWEDEN' UNION ALL

  SELECT 'KSE-JV GOT','SWEDEN' UNION ALL

  SELECT 'KSE-JV HES','SWEDEN' UNION ALL

  SELECT 'KFI HEL','FINLAND' UNION ALL

  SELECT 'KDK AAL','DENMARK' UNION ALL

  SELECT 'KDK AAR','DENMARK' UNION ALL

  SELECT 'KDK CPH','DENMARK' UNION ALL

  SELECT 'KDK EBJ','DENMARK' UNION ALL

  SELECT 'KDK ODE','DENMARK' UNION ALL

  SELECT 'KDK-JV AAL','DENMARK' UNION ALL

  SELECT 'KDK-JV AAR','DENMARK' UNION ALL

  SELECT 'KDK-JV CPH','DENMARK' UNION ALL

  SELECT 'KDK-JV EBJ','DENMARK' UNION ALL

  SELECT 'KDK-JV ODE','DENMARK' UNION ALL

  SELECT 'KNO OSL','NORWAY' UNION ALL

  SELECT 'KNO-JV OSL','NORWAY' UNION ALL

  SELECT 'KDE GDY', 'BALTIC' UNION ALL

  SELECT 'EESCR TLL', 'BALTIC' UNION ALL

  SELECT 'KRU LED', 'BALTIC' UNION ALL

  SELECT 'LTLIM KLJ', 'BALTIC' UNION ALL

  SELECT 'LVKLS RIX', 'BALTIC' UNION ALL

  SELECT 'RUMET LED', 'BALTIC' UNION ALL

  SELECT 'RUMET MOW', 'BALTIC' UNION ALL

  SELECT 'RUMSA LED','BALTIC'

  ),

  COUNTRIES AS (

  SELECT 'GERMANY' AS COUNTRY_DSC UNION ALL

  SELECT 'AUSTRIA' UNION ALL

  SELECT 'NETHERLANDS' UNION ALL

  SELECT 'BELGIUM' UNION ALL

  SELECT 'FRANCE' UNION ALL

  SELECT 'IRELAND' UNION ALL

  SELECT 'SWEDEN' UNION ALL

  SELECT 'FINLAND' UNION ALL

  SELECT 'DENMARK' UNION ALL

  SELECT 'NORWAY' UNION ALL

  SELECT 'CZECH REPUBLIC' UNION ALL

  SELECT 'SWITZERLAND' UNION ALL

  SELECT 'PORTUGAL' UNION ALL

  SELECT 'SPAIN' UNION ALL

  SELECT 'BALTICS' UNION ALL

  SELECT 'BUFFER'

  )

  SELECT booking_num,

  O.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR','DKCPH','FIHEL','FIKTK','IEORK','IEDUB','NLRTM','PTLEI','PTOPO','PTLIS',

  'ESBIO','ESVGO','SEGOT','SEHEL','GBBEL') THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU','FIOUL','DEBRV','NOAES','NOBGO','NOFRK','NOHAL','NOHAU','NOKRS',

  'NOKSU','NOLAR','NOMSS','NOOSL','NOTAE','PLGDY','SEGVX','SEMMA','SENRK','SESTO','DEHAM') THEN 'DEBRV'

  -- THE FOLLOWING TWO LINES SERVE NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD

  END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  -- THE FOLLOWING LINE SERVES NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2

  END AS TEU,

  mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD

  FROM MG_COUNTRY AS mc

  INNER JOIN COUNTRIES AS d

  ON mc.COUNTRY_DSC = d.COUNTRY_DSC

  INNER JOIN MG_OFFICE AS mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  INNER JOIN OFFICES AS O

  ON mo.OFFICE_CD = O.OFFICE_CD

  LEFT OUTER JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL AS ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  LEFT OUTER JOIN MG_BOOKING_ITINERARY AS mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  AND mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  LEFT OUTER JOIN MG_CONTAINER_CODE AS mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  LEFT OUTER JOIN MG_BOOKING AS mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  ORDER BY mc.COUNTRY_DSC

  Let me know if that does it for you.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 14, 2014 at 2:16 am

  #1737938

  pwalter83 (8/13/2014)

  ---

  ChrisM@home (8/8/2014)

  ---

  pwalter83 (8/8/2014)

  ---

  ChrisM@Work (8/7/2014)

  ---

  I don't see any parameters in your code, Paul.

  Any ideas, Chris ??[/quote

  Hi Paul, I'm on break in Normandy with no access to SQL Server until tuesday. A question for you: can you please provide as much information as you can about the use of parameters and how you intend to use them to influence the result set from your query? Cheers.

  The parameters in the SSRS report would allow the users to select a value each from the dropdown boxes namely vessel and voyage and based on the selection the report should display data for the mentioned countries.

  If data for a particular country does not exist, it should still display the name of the country in the row as shown in the attachment. In the attachment, you would notice that Belgium, France and Portugal do not have data for vessel - VCRB and VOYAGE - 076W but are still shown.

  Thanks.

  I'm confused, Paul - is it the parameters you're having issues with i.e. parameterising the query, or the query itself? If it's both, then try to tackle the two issues separately. I'd fix the query first.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• pwalter83

  SSChampion

  Points: 14575

  More actions

  August 14, 2014 at 2:29 am

  #1737942

  blank

• pwalter83

  SSChampion

  Points: 14575

  More actions

  August 14, 2014 at 2:38 am

  #1737946

  sgmunson (8/13/2014)

  ---

  I fail to understand what the discomfort level is with CTEs. It's kind of like having a subquery, but just that it appears BEFORE the rest of the query and gets a table name. It's not something you have to worry about whether or not it will work. I took an educated guess approach and put the following together, based on the last query text I saw:

  Let me know if that does it for you.

  Thanks Steve.

  I tried to run your query but it returns no data at all if there is no matching data for any countries.

  As I mentioned above and also see in the attached example, the report should still display all the countries regardless of data in the database for the selected parameter values for a single country, multiple countries or all of the countries. I tried to modify your query by doing a right join to COUNTRIES buit it still doesnt work. Please find your modified query below.

  The user needs to see all the countries mentioned in the attachment even if the report returns null. Apologies if I am unable to explain the requirement clearly.

  Thanks.

  WITH OFFICES AS (

  SELECT 'KBE ANR' AS OFFICE_CD, 'BELGIUM' AS COUNTRY_DSC UNION ALL

  SELECT 'KBE ZEE', 'BELGIUM' UNION ALL

  SELECT 'KEU ANR','BELGIUM' UNION ALL

  SELECT 'KDE VIE','AUSTRIA' UNION ALL

  SELECT 'KDE BRE','GERMANY' UNION ALL

  SELECT 'KDE BRV','GERMANY' UNION ALL

  SELECT 'KDE DUS','GERMANY' UNION ALL

  SELECT 'KDE FRA','GERMANY' UNION ALL

  SELECT 'KDE HAM','GERMANY' UNION ALL

  SELECT 'KDE MUC','GERMANY' UNION ALL

  SELECT 'KDE STR','GERMANY' UNION ALL

  SELECT 'KFR BOD','FRANCE' UNION ALL

  SELECT 'KFR DKK','FRANCE' UNION ALL

  SELECT 'KFR FOS','FRANCE' UNION ALL

  SELECT 'KFR LEH','FRANCE' UNION ALL

  SELECT 'KFR LIO','FRANCE' UNION ALL

  SELECT 'KFR MRS','FRANCE' UNION ALL

  SELECT 'KFR PAR','FRANCE' UNION ALL

  SELECT 'EXAF PARIS','FRANCE' UNION ALL

  SELECT 'FRBAL BOD','FRANCE' UNION ALL

  SELECT 'KEU LEH','FRANCE' UNION ALL

  SELECT 'KEU RTM','NETHERLANDS' UNION ALL

  SELECT 'KNL RTM','NETHERLANDS' UNION ALL

  SELECT 'KDE PRG','CZECH REPUBLIC' UNION ALL

  SELECT 'CHGLS BSL','SWITZERLAND' UNION ALL

  SELECT 'KPT LIS','PORTUGAL' UNION ALL

  SELECT 'KPT OPO','PORTUGAL' UNION ALL

  SELECT 'ESBLR BCN','SPAIN' UNION ALL

  SELECT 'ESBLR MAD','SPAIN' UNION ALL

  SELECT 'ESBLR SDR','SPAIN' UNION ALL

  SELECT 'ESBLR TAR','SPAIN' UNION ALL

  SELECT 'ESBLR VCL','SPAIN' UNION ALL

  SELECT 'ESBLR VGO','SPAIN' UNION ALL

  SELECT 'ESBLR VLC','SPAIN' UNION ALL

  SELECT 'KES BCN','SPAIN' UNION ALL

  SELECT 'KES BIO','SPAIN' UNION ALL

  SELECT 'KES MAD','SPAIN' UNION ALL

  SELECT 'KES VGO','SPAIN' UNION ALL

  SELECT 'KES VLC','SPAIN' UNION ALL

  SELECT 'IEIRL DUB','IRELAND' UNION ALL

  SELECT 'IEIRL ORK','IRELAND' UNION ALL

  SELECT 'KSE HES','SWEDEN' UNION ALL

  SELECT 'KSE-JV GOT','SWEDEN' UNION ALL

  SELECT 'KSE-JV HES','SWEDEN' UNION ALL

  SELECT 'KFI HEL','FINLAND' UNION ALL

  SELECT 'KDK AAL','DENMARK' UNION ALL

  SELECT 'KDK AAR','DENMARK' UNION ALL

  SELECT 'KDK CPH','DENMARK' UNION ALL

  SELECT 'KDK EBJ','DENMARK' UNION ALL

  SELECT 'KDK ODE','DENMARK' UNION ALL

  SELECT 'KDK-JV AAL','DENMARK' UNION ALL

  SELECT 'KDK-JV AAR','DENMARK' UNION ALL

  SELECT 'KDK-JV CPH','DENMARK' UNION ALL

  SELECT 'KDK-JV EBJ','DENMARK' UNION ALL

  SELECT 'KDK-JV ODE','DENMARK' UNION ALL

  SELECT 'KNO OSL','NORWAY' UNION ALL

  SELECT 'KNO-JV OSL','NORWAY' UNION ALL

  SELECT 'KDE GDY', 'BALTIC' UNION ALL

  SELECT 'EESCR TLL', 'BALTIC' UNION ALL

  SELECT 'KRU LED', 'BALTIC' UNION ALL

  SELECT 'LTLIM KLJ', 'BALTIC' UNION ALL

  SELECT 'LVKLS RIX', 'BALTIC' UNION ALL

  SELECT 'RUMET LED', 'BALTIC' UNION ALL

  SELECT 'RUMET MOW', 'BALTIC' UNION ALL

  SELECT 'RUMSA LED','BALTIC'

  ),

  COUNTRIES AS (

  SELECT 'GERMANY' AS COUNTRY_DSC UNION ALL

  SELECT 'AUSTRIA' UNION ALL

  SELECT 'NETHERLANDS' UNION ALL

  SELECT 'BELGIUM' UNION ALL

  SELECT 'FRANCE' UNION ALL

  SELECT 'IRELAND' UNION ALL

  SELECT 'SWEDEN' UNION ALL

  SELECT 'FINLAND' UNION ALL

  SELECT 'DENMARK' UNION ALL

  SELECT 'NORWAY' UNION ALL

  SELECT 'CZECH REPUBLIC' UNION ALL

  SELECT 'SWITZERLAND' UNION ALL

  SELECT 'PORTUGAL' UNION ALL

  SELECT 'SPAIN' UNION ALL

  SELECT 'BALTICS' UNION ALL

  SELECT 'BUFFER'

  )

  SELECT booking_num,

  O.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR','DKCPH','FIHEL','FIKTK','IEORK','IEDUB','NLRTM','PTLEI','PTOPO','PTLIS',

  'ESBIO','ESVGO','SEGOT','SEHEL','GBBEL') THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU','FIOUL','DEBRV','NOAES','NOBGO','NOFRK','NOHAL','NOHAU','NOKRS',

  'NOKSU','NOLAR','NOMSS','NOOSL','NOTAE','PLGDY','SEGVX','SEMMA','SENRK','SESTO','DEHAM') THEN 'DEBRV'

  -- THE FOLLOWING TWO LINES SERVE NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD

  END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  -- THE FOLLOWING LINE SERVES NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2

  END AS TEU,

  mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD

  FROM MG_COUNTRY AS mc

  right JOIN COUNTRIES AS d

  ON mc.COUNTRY_DSC = d.COUNTRY_DSC

  INNER JOIN MG_OFFICE AS mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  LEFT OUTER JOIN MG_BOOKING AS mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  inner JOIN OFFICES AS O

  ON mo.OFFICE_CD = O.OFFICE_CD

  LEFT OUTER JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL AS ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  LEFT OUTER JOIN MG_BOOKING_ITINERARY AS mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  LEFT OUTER JOIN MG_CONTAINER_CODE AS mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  where mbi.VESSEL_CD IN ('VCRB')

  AND mbi.VOYAGE_CD IN ('076w')

  ORDER BY mc.COUNTRY_DSC

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 14, 2014 at 6:50 am

  #1738009

  Okay.... I'll need to refactor the query by moving the table order around and see what I can come up with. Give me a couple hours as I have some other stuff that needs doing.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 14, 2014 at 7:15 am

  #1738022

  Okay, I got to it sooner. Try this:

  WITH OFFICES AS (

  SELECT 'KBE ANR' AS OFFICE_CD, 'BELGIUM' AS COUNTRY_DSC UNION ALL

  SELECT 'KBE ZEE', 'BELGIUM' UNION ALL

  SELECT 'KEU ANR','BELGIUM' UNION ALL

  SELECT 'KDE VIE','AUSTRIA' UNION ALL

  SELECT 'KDE BRE','GERMANY' UNION ALL

  SELECT 'KDE BRV','GERMANY' UNION ALL

  SELECT 'KDE DUS','GERMANY' UNION ALL

  SELECT 'KDE FRA','GERMANY' UNION ALL

  SELECT 'KDE HAM','GERMANY' UNION ALL

  SELECT 'KDE MUC','GERMANY' UNION ALL

  SELECT 'KDE STR','GERMANY' UNION ALL

  SELECT 'KFR BOD','FRANCE' UNION ALL

  SELECT 'KFR DKK','FRANCE' UNION ALL

  SELECT 'KFR FOS','FRANCE' UNION ALL

  SELECT 'KFR LEH','FRANCE' UNION ALL

  SELECT 'KFR LIO','FRANCE' UNION ALL

  SELECT 'KFR MRS','FRANCE' UNION ALL

  SELECT 'KFR PAR','FRANCE' UNION ALL

  SELECT 'EXAF PARIS','FRANCE' UNION ALL

  SELECT 'FRBAL BOD','FRANCE' UNION ALL

  SELECT 'KEU LEH','FRANCE' UNION ALL

  SELECT 'KEU RTM','NETHERLANDS' UNION ALL

  SELECT 'KNL RTM','NETHERLANDS' UNION ALL

  SELECT 'KDE PRG','CZECH REPUBLIC' UNION ALL

  SELECT 'CHGLS BSL','SWITZERLAND' UNION ALL

  SELECT 'KPT LIS','PORTUGAL' UNION ALL

  SELECT 'KPT OPO','PORTUGAL' UNION ALL

  SELECT 'ESBLR BCN','SPAIN' UNION ALL

  SELECT 'ESBLR MAD','SPAIN' UNION ALL

  SELECT 'ESBLR SDR','SPAIN' UNION ALL

  SELECT 'ESBLR TAR','SPAIN' UNION ALL

  SELECT 'ESBLR VCL','SPAIN' UNION ALL

  SELECT 'ESBLR VGO','SPAIN' UNION ALL

  SELECT 'ESBLR VLC','SPAIN' UNION ALL

  SELECT 'KES BCN','SPAIN' UNION ALL

  SELECT 'KES BIO','SPAIN' UNION ALL

  SELECT 'KES MAD','SPAIN' UNION ALL

  SELECT 'KES VGO','SPAIN' UNION ALL

  SELECT 'KES VLC','SPAIN' UNION ALL

  SELECT 'IEIRL DUB','IRELAND' UNION ALL

  SELECT 'IEIRL ORK','IRELAND' UNION ALL

  SELECT 'KSE HES','SWEDEN' UNION ALL

  SELECT 'KSE-JV GOT','SWEDEN' UNION ALL

  SELECT 'KSE-JV HES','SWEDEN' UNION ALL

  SELECT 'KFI HEL','FINLAND' UNION ALL

  SELECT 'KDK AAL','DENMARK' UNION ALL

  SELECT 'KDK AAR','DENMARK' UNION ALL

  SELECT 'KDK CPH','DENMARK' UNION ALL

  SELECT 'KDK EBJ','DENMARK' UNION ALL

  SELECT 'KDK ODE','DENMARK' UNION ALL

  SELECT 'KDK-JV AAL','DENMARK' UNION ALL

  SELECT 'KDK-JV AAR','DENMARK' UNION ALL

  SELECT 'KDK-JV CPH','DENMARK' UNION ALL

  SELECT 'KDK-JV EBJ','DENMARK' UNION ALL

  SELECT 'KDK-JV ODE','DENMARK' UNION ALL

  SELECT 'KNO OSL','NORWAY' UNION ALL

  SELECT 'KNO-JV OSL','NORWAY' UNION ALL

  SELECT 'KDE GDY', 'BALTIC' UNION ALL

  SELECT 'EESCR TLL', 'BALTIC' UNION ALL

  SELECT 'KRU LED', 'BALTIC' UNION ALL

  SELECT 'LTLIM KLJ', 'BALTIC' UNION ALL

  SELECT 'LVKLS RIX', 'BALTIC' UNION ALL

  SELECT 'RUMET LED', 'BALTIC' UNION ALL

  SELECT 'RUMET MOW', 'BALTIC' UNION ALL

  SELECT 'RUMSA LED','BALTIC'

  ),

  COUNTRIES AS (

  SELECT 'GERMANY' AS COUNTRY_DSC UNION ALL

  SELECT 'AUSTRIA' UNION ALL

  SELECT 'NETHERLANDS' UNION ALL

  SELECT 'BELGIUM' UNION ALL

  SELECT 'FRANCE' UNION ALL

  SELECT 'IRELAND' UNION ALL

  SELECT 'SWEDEN' UNION ALL

  SELECT 'FINLAND' UNION ALL

  SELECT 'DENMARK' UNION ALL

  SELECT 'NORWAY' UNION ALL

  SELECT 'CZECH REPUBLIC' UNION ALL

  SELECT 'SWITZERLAND' UNION ALL

  SELECT 'PORTUGAL' UNION ALL

  SELECT 'SPAIN' UNION ALL

  SELECT 'BALTICS' UNION ALL

  SELECT 'BUFFER'

  )

  SELECT DISTINCT booking_num,

  d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR','DKCPH','FIHEL','FIKTK','IEORK','IEDUB','NLRTM','PTLEI','PTOPO','PTLIS',

  'ESBIO','ESVGO','SEGOT','SEHEL','GBBEL') THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU','FIOUL','DEBRV','NOAES','NOBGO','NOFRK','NOHAL','NOHAU','NOKRS',

  'NOKSU','NOLAR','NOMSS','NOOSL','NOTAE','PLGDY','SEGVX','SEMMA','SENRK','SESTO','DEHAM') THEN 'DEBRV'

  -- THE FOLLOWING TWO LINES SERVE NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD

  END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  -- THE FOLLOWING LINE SERVES NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2

  END AS TEU,

  mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD

  FROM COUNTRIES AS d

  LEFT OUTER JOIN MG_COUNTRY AS mc

  ON d.COUNTRY_DSC = mc.COUNTRY_DSC

  LEFT OUTER JOIN MG_OFFICE AS mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  LEFT OUTER JOIN OFFICES AS O

  ON mo.OFFICE_CD = O.OFFICE_CD

  LEFT OUTER JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL AS ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  LEFT OUTER JOIN MG_BOOKING_ITINERARY AS mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  AND mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  LEFT OUTER JOIN MG_CONTAINER_CODE AS mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  LEFT OUTER JOIN MG_BOOKING AS mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  ORDER BY d.COUNTRY_DSC

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• pwalter83

  SSChampion

  Points: 14575

  More actions

  August 14, 2014 at 8:24 am

  #1738062

  sgmunson (8/14/2014)

  ---

  Okay, I got to it sooner. Try this:

  Thanks again Steve for taking time out of your work.

  I am not getting the desired result with your modified query. Now its showing all of the data in the database irrespective of the parameter values entered in the report.

  I have gone ahead and slighly modified your query so that the parameters are included in the where clause. Could you please give some suggestion now if you have some time ?

  WITH OFFICES AS (

  SELECT 'KBE ANR' AS OFFICE_CD, 'BELGIUM' AS COUNTRY_DSC UNION ALL

  SELECT 'KBE ZEE', 'BELGIUM' UNION ALL

  SELECT 'KEU ANR','BELGIUM' UNION ALL

  SELECT 'KDE VIE','AUSTRIA' UNION ALL

  SELECT 'KDE BRE','GERMANY' UNION ALL

  SELECT 'KDE BRV','GERMANY' UNION ALL

  SELECT 'KDE DUS','GERMANY' UNION ALL

  SELECT 'KDE FRA','GERMANY' UNION ALL

  SELECT 'KDE HAM','GERMANY' UNION ALL

  SELECT 'KDE MUC','GERMANY' UNION ALL

  SELECT 'KDE STR','GERMANY' UNION ALL

  SELECT 'KFR BOD','FRANCE' UNION ALL

  SELECT 'KFR DKK','FRANCE' UNION ALL

  SELECT 'KFR FOS','FRANCE' UNION ALL

  SELECT 'KFR LEH','FRANCE' UNION ALL

  SELECT 'KFR LIO','FRANCE' UNION ALL

  SELECT 'KFR MRS','FRANCE' UNION ALL

  SELECT 'KFR PAR','FRANCE' UNION ALL

  SELECT 'EXAF PARIS','FRANCE' UNION ALL

  SELECT 'FRBAL BOD','FRANCE' UNION ALL

  SELECT 'KEU LEH','FRANCE' UNION ALL

  SELECT 'KEU RTM','NETHERLANDS' UNION ALL

  SELECT 'KNL RTM','NETHERLANDS' UNION ALL

  SELECT 'KDE PRG','CZECH REPUBLIC' UNION ALL

  SELECT 'CHGLS BSL','SWITZERLAND' UNION ALL

  SELECT 'KPT LIS','PORTUGAL' UNION ALL

  SELECT 'KPT OPO','PORTUGAL' UNION ALL

  SELECT 'ESBLR BCN','SPAIN' UNION ALL

  SELECT 'ESBLR MAD','SPAIN' UNION ALL

  SELECT 'ESBLR SDR','SPAIN' UNION ALL

  SELECT 'ESBLR TAR','SPAIN' UNION ALL

  SELECT 'ESBLR VCL','SPAIN' UNION ALL

  SELECT 'ESBLR VGO','SPAIN' UNION ALL

  SELECT 'ESBLR VLC','SPAIN' UNION ALL

  SELECT 'KES BCN','SPAIN' UNION ALL

  SELECT 'KES BIO','SPAIN' UNION ALL

  SELECT 'KES MAD','SPAIN' UNION ALL

  SELECT 'KES VGO','SPAIN' UNION ALL

  SELECT 'KES VLC','SPAIN' UNION ALL

  SELECT 'IEIRL DUB','IRELAND' UNION ALL

  SELECT 'IEIRL ORK','IRELAND' UNION ALL

  SELECT 'KSE HES','SWEDEN' UNION ALL

  SELECT 'KSE-JV GOT','SWEDEN' UNION ALL

  SELECT 'KSE-JV HES','SWEDEN' UNION ALL

  SELECT 'KFI HEL','FINLAND' UNION ALL

  SELECT 'KDK AAL','DENMARK' UNION ALL

  SELECT 'KDK AAR','DENMARK' UNION ALL

  SELECT 'KDK CPH','DENMARK' UNION ALL

  SELECT 'KDK EBJ','DENMARK' UNION ALL

  SELECT 'KDK ODE','DENMARK' UNION ALL

  SELECT 'KDK-JV AAL','DENMARK' UNION ALL

  SELECT 'KDK-JV AAR','DENMARK' UNION ALL

  SELECT 'KDK-JV CPH','DENMARK' UNION ALL

  SELECT 'KDK-JV EBJ','DENMARK' UNION ALL

  SELECT 'KDK-JV ODE','DENMARK' UNION ALL

  SELECT 'KNO OSL','NORWAY' UNION ALL

  SELECT 'KNO-JV OSL','NORWAY' UNION ALL

  SELECT 'KDE GDY', 'BALTIC' UNION ALL

  SELECT 'EESCR TLL', 'BALTIC' UNION ALL

  SELECT 'KRU LED', 'BALTIC' UNION ALL

  SELECT 'LTLIM KLJ', 'BALTIC' UNION ALL

  SELECT 'LVKLS RIX', 'BALTIC' UNION ALL

  SELECT 'RUMET LED', 'BALTIC' UNION ALL

  SELECT 'RUMET MOW', 'BALTIC' UNION ALL

  SELECT 'RUMSA LED','BALTIC'

  ),

  COUNTRIES AS (

  SELECT 'GERMANY' AS COUNTRY_DSC UNION ALL

  SELECT 'AUSTRIA' UNION ALL

  SELECT 'NETHERLANDS' UNION ALL

  SELECT 'BELGIUM' UNION ALL

  SELECT 'FRANCE' UNION ALL

  SELECT 'IRELAND' UNION ALL

  SELECT 'SWEDEN' UNION ALL

  SELECT 'FINLAND' UNION ALL

  SELECT 'DENMARK' UNION ALL

  SELECT 'NORWAY' UNION ALL

  SELECT 'CZECH REPUBLIC' UNION ALL

  SELECT 'SWITZERLAND' UNION ALL

  SELECT 'PORTUGAL' UNION ALL

  SELECT 'SPAIN' UNION ALL

  SELECT 'BALTICS' UNION ALL

  SELECT 'BUFFER'

  )

  SELECT DISTINCT booking_num,

  d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR','DKCPH','FIHEL','FIKTK','IEORK','IEDUB','NLRTM','PTLEI','PTOPO','PTLIS',

  'ESBIO','ESVGO','SEGOT','SEHEL','GBBEL') THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU','FIOUL','DEBRV','NOAES','NOBGO','NOFRK','NOHAL','NOHAU','NOKRS',

  'NOKSU','NOLAR','NOMSS','NOOSL','NOTAE','PLGDY','SEGVX','SEMMA','SENRK','SESTO','DEHAM') THEN 'DEBRV'

  -- THE FOLLOWING TWO LINES SERVE NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD

  END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  -- THE FOLLOWING LINE SERVES NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2

  END AS TEU,

  mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD

  FROM COUNTRIES AS d

  LEFT OUTER JOIN MG_COUNTRY AS mc

  ON d.COUNTRY_DSC = mc.COUNTRY_DSC

  LEFT OUTER JOIN MG_OFFICE AS mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  LEFT OUTER JOIN OFFICES AS O

  ON mo.OFFICE_CD = O.OFFICE_CD

  LEFT OUTER JOIN MG_BOOKING AS mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  LEFT OUTER JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL AS ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  LEFT OUTER JOIN MG_BOOKING_ITINERARY AS mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  LEFT OUTER JOIN MG_CONTAINER_CODE AS mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  where mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  ORDER BY d.COUNTRY_DSC

  Thanks.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 14, 2014 at 8:48 am

  #1738087

  Change each LEFT OUTER JOIN to an INNER JOIN, from my final query (not your modified version), one at a time, in order from topmost to bottommost, testing the result after each change. If you never get anything other than the entire database or 0 records, then I'm going to have to rethink the design. Let me know...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 14, 2014 at 8:58 am

  #1738094

  sgmunson (8/14/2014)

  ---

  Change each LEFT OUTER JOIN to an INNER JOIN, from my final query (not your modified version), one at a time, in order from topmost to bottommost, testing the result after each change. If you never get anything other than the entire database or 0 records, then I'm going to have to rethink the design. Let me know...

  Come to think of it, maybe we just need a group by and an extra field that might not need to be used.

  WITH OFFICES AS (

  SELECT 'KBE ANR' AS OFFICE_CD, 'BELGIUM' AS COUNTRY_DSC UNION ALL

  SELECT 'KBE ZEE', 'BELGIUM' UNION ALL

  SELECT 'KEU ANR','BELGIUM' UNION ALL

  SELECT 'KDE VIE','AUSTRIA' UNION ALL

  SELECT 'KDE BRE','GERMANY' UNION ALL

  SELECT 'KDE BRV','GERMANY' UNION ALL

  SELECT 'KDE DUS','GERMANY' UNION ALL

  SELECT 'KDE FRA','GERMANY' UNION ALL

  SELECT 'KDE HAM','GERMANY' UNION ALL

  SELECT 'KDE MUC','GERMANY' UNION ALL

  SELECT 'KDE STR','GERMANY' UNION ALL

  SELECT 'KFR BOD','FRANCE' UNION ALL

  SELECT 'KFR DKK','FRANCE' UNION ALL

  SELECT 'KFR FOS','FRANCE' UNION ALL

  SELECT 'KFR LEH','FRANCE' UNION ALL

  SELECT 'KFR LIO','FRANCE' UNION ALL

  SELECT 'KFR MRS','FRANCE' UNION ALL

  SELECT 'KFR PAR','FRANCE' UNION ALL

  SELECT 'EXAF PARIS','FRANCE' UNION ALL

  SELECT 'FRBAL BOD','FRANCE' UNION ALL

  SELECT 'KEU LEH','FRANCE' UNION ALL

  SELECT 'KEU RTM','NETHERLANDS' UNION ALL

  SELECT 'KNL RTM','NETHERLANDS' UNION ALL

  SELECT 'KDE PRG','CZECH REPUBLIC' UNION ALL

  SELECT 'CHGLS BSL','SWITZERLAND' UNION ALL

  SELECT 'KPT LIS','PORTUGAL' UNION ALL

  SELECT 'KPT OPO','PORTUGAL' UNION ALL

  SELECT 'ESBLR BCN','SPAIN' UNION ALL

  SELECT 'ESBLR MAD','SPAIN' UNION ALL

  SELECT 'ESBLR SDR','SPAIN' UNION ALL

  SELECT 'ESBLR TAR','SPAIN' UNION ALL

  SELECT 'ESBLR VCL','SPAIN' UNION ALL

  SELECT 'ESBLR VGO','SPAIN' UNION ALL

  SELECT 'ESBLR VLC','SPAIN' UNION ALL

  SELECT 'KES BCN','SPAIN' UNION ALL

  SELECT 'KES BIO','SPAIN' UNION ALL

  SELECT 'KES MAD','SPAIN' UNION ALL

  SELECT 'KES VGO','SPAIN' UNION ALL

  SELECT 'KES VLC','SPAIN' UNION ALL

  SELECT 'IEIRL DUB','IRELAND' UNION ALL

  SELECT 'IEIRL ORK','IRELAND' UNION ALL

  SELECT 'KSE HES','SWEDEN' UNION ALL

  SELECT 'KSE-JV GOT','SWEDEN' UNION ALL

  SELECT 'KSE-JV HES','SWEDEN' UNION ALL

  SELECT 'KFI HEL','FINLAND' UNION ALL

  SELECT 'KDK AAL','DENMARK' UNION ALL

  SELECT 'KDK AAR','DENMARK' UNION ALL

  SELECT 'KDK CPH','DENMARK' UNION ALL

  SELECT 'KDK EBJ','DENMARK' UNION ALL

  SELECT 'KDK ODE','DENMARK' UNION ALL

  SELECT 'KDK-JV AAL','DENMARK' UNION ALL

  SELECT 'KDK-JV AAR','DENMARK' UNION ALL

  SELECT 'KDK-JV CPH','DENMARK' UNION ALL

  SELECT 'KDK-JV EBJ','DENMARK' UNION ALL

  SELECT 'KDK-JV ODE','DENMARK' UNION ALL

  SELECT 'KNO OSL','NORWAY' UNION ALL

  SELECT 'KNO-JV OSL','NORWAY' UNION ALL

  SELECT 'KDE GDY', 'BALTIC' UNION ALL

  SELECT 'EESCR TLL', 'BALTIC' UNION ALL

  SELECT 'KRU LED', 'BALTIC' UNION ALL

  SELECT 'LTLIM KLJ', 'BALTIC' UNION ALL

  SELECT 'LVKLS RIX', 'BALTIC' UNION ALL

  SELECT 'RUMET LED', 'BALTIC' UNION ALL

  SELECT 'RUMET MOW', 'BALTIC' UNION ALL

  SELECT 'RUMSA LED','BALTIC'

  ),

  COUNTRIES AS (

  SELECT 'GERMANY' AS COUNTRY_DSC UNION ALL

  SELECT 'AUSTRIA' UNION ALL

  SELECT 'NETHERLANDS' UNION ALL

  SELECT 'BELGIUM' UNION ALL

  SELECT 'FRANCE' UNION ALL

  SELECT 'IRELAND' UNION ALL

  SELECT 'SWEDEN' UNION ALL

  SELECT 'FINLAND' UNION ALL

  SELECT 'DENMARK' UNION ALL

  SELECT 'NORWAY' UNION ALL

  SELECT 'CZECH REPUBLIC' UNION ALL

  SELECT 'SWITZERLAND' UNION ALL

  SELECT 'PORTUGAL' UNION ALL

  SELECT 'SPAIN' UNION ALL

  SELECT 'BALTICS' UNION ALL

  SELECT 'BUFFER'

  )

  SELECT booking_num,

  d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR','DKCPH','FIHEL','FIKTK','IEORK','IEDUB','NLRTM','PTLEI','PTOPO','PTLIS',

  'ESBIO','ESVGO','SEGOT','SEHEL','GBBEL') THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU','FIOUL','DEBRV','NOAES','NOBGO','NOFRK','NOHAL','NOHAU','NOKRS',

  'NOKSU','NOLAR','NOMSS','NOOSL','NOTAE','PLGDY','SEGVX','SEMMA','SENRK','SESTO','DEHAM') THEN 'DEBRV'

  -- THE FOLLOWING TWO LINES SERVE NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD

  END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  -- THE FOLLOWING LINE SERVES NO USEFUL PURPOSE BECAUSE OF THE ELSE CONDITION

  --WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2

  END AS TEU,

  mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD,

  COUNT(*) AS REC_COUNT

  FROM COUNTRIES AS d

  INNER JOIN MG_COUNTRY AS mc

  ON d.COUNTRY_DSC = mc.COUNTRY_DSC

  INNER JOIN MG_OFFICE AS mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  INNER JOIN OFFICES AS O

  ON mo.OFFICE_CD = O.OFFICE_CD

  LEFT OUTER JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL AS ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  LEFT OUTER JOIN MG_BOOKING_ITINERARY AS mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  AND mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  LEFT OUTER JOIN MG_CONTAINER_CODE AS mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  LEFT OUTER JOIN MG_BOOKING AS mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  GROUP BY booking_num, d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR','DKCPH','FIHEL','FIKTK','IEORK','IEDUB','NLRTM','PTLEI','PTOPO','PTLIS',

  'ESBIO','ESVGO','SEGOT','SEHEL','GBBEL') THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU','FIOUL','DEBRV','NOAES','NOBGO','NOFRK','NOHAL','NOHAU','NOKRS',

  'NOKSU','NOLAR','NOMSS','NOOSL','NOTAE','PLGDY','SEGVX','SEMMA','SENRK','SESTO','DEHAM') THEN 'DEBRV'

  ELSE POL_LOCATION_CD

  END,

  CASE WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1 ELSE 2 END,

  mcc.TYPE_CD, mbi.VESSEL_CD, mbi.VOYAGE_CD

  ORDER BY d.COUNTRY_DSC

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 14, 2014 at 9:51 am

  #1738117

  This is worth a try:

  SELECT *

  INTO #Offices

  FROM (VALUES

  ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

  ('KDE VIE','AUSTRIA'),

  ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

  ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

  ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

  ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

  ('KDE PRG','CZECH REPUBLIC'),

  ('CHGLS BSL','SWITZERLAND'),

  ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

  ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

  ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

  ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

  ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

  ('KFI HEL','FINLAND'),

  ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

  ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

  ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

  ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

  ) d (OFFICE_CD, COUNTRY_DSC)

  SELECT

  booking_num,

  d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

  THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

  THEN 'DEBRV'

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2 END AS TEU,

  mcc.TYPE_CD,

  mbi.VESSEL_CD,

  mbi.VOYAGE_CD

  FROM #Offices d

  LEFT JOIN MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

  LEFT JOIN MG_OFFICE mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  LEFT join MG_BOOKING mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  left JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  left JOIN MG_BOOKING_ITINERARY mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  AND mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  left JOIN MG_CONTAINER_CODE mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  ORDER BY d.COUNTRY_DSC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 14, 2014 at 10:01 am

  #1738126

  Actuallly Chris, that's already been tried, and it returns the entire database worth of stuff - at least according to the OP, anyway.

  ChrisM@Work (8/14/2014)

  ---

  This is worth a try:

  SELECT *

  INTO #Offices

  FROM (VALUES

  ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

  ('KDE VIE','AUSTRIA'),

  ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

  ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

  ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

  ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

  ('KDE PRG','CZECH REPUBLIC'),

  ('CHGLS BSL','SWITZERLAND'),

  ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

  ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

  ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

  ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

  ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

  ('KFI HEL','FINLAND'),

  ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

  ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

  ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

  ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

  ) d (OFFICE_CD, COUNTRY_DSC)

  SELECT

  booking_num,

  d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

  THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL', 'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

  THEN 'DEBRV'

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

  CASE

  WHEN ber.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  WHEN ber.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2 END AS TEU,

  mcc.TYPE_CD,

  mbi.VESSEL_CD,

  mbi.VOYAGE_CD

  FROM #Offices d

  LEFT JOIN MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC

  LEFT JOIN MG_OFFICE mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  LEFT join MG_BOOKING mgd

  ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD

  AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  left JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  left JOIN MG_BOOKING_ITINERARY mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  AND mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  left JOIN MG_CONTAINER_CODE mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  ORDER BY d.COUNTRY_DSC

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 15, 2014 at 2:20 am

  #1738303

  Thanks Steve. Something along these lines then. Not sure where two of the tables (MG_COUNTRY,MG_OFFICE) fit into this but it's worth a try:

  SELECT *

  INTO #Offices

  FROM (VALUES

  ('KBE ANR','BELGIUM'),('KBE ZEE','BELGIUM'),('KEU ANR','BELGIUM'),

  ('KDE VIE','AUSTRIA'),

  ('KDE BRE','GERMANY'),('KDE BRV','GERMANY'),('KDE DUS','GERMANY'),('KDE FRA','GERMANY'),('KDE HAM','GERMANY'),('KDE MUC','GERMANY'),('KDE STR','GERMANY'),

  ('KFR BOD','FRANCE'),('KFR DKK','FRANCE'),('KFR FOS','FRANCE'),('KFR LEH','FRANCE'),('KFR LIO','FRANCE'),('KFR MRS','FRANCE'),

  ('KFR PAR','FRANCE'),('EXAF PARIS','FRANCE'),('FRBAL BOD','FRANCE'),('KEU LEH','FRANCE'),

  ('KEU RTM','NETHERLANDS'),('KNL RTM','NETHERLANDS'),

  ('KDE PRG','CZECH REPUBLIC'),

  ('CHGLS BSL','SWITZERLAND'),

  ('KPT LIS','PORTUGAL'),('KPT OPO','PORTUGAL'),

  ('ESBLR BCN','SPAIN'),('ESBLR MAD','SPAIN'),('ESBLR SDR','SPAIN'),('ESBLR TAR','SPAIN'),('ESBLR VCL','SPAIN'),('ESBLR VGO','SPAIN'),

  ('ESBLR VLC','SPAIN'),('KES BCN','SPAIN'),('KES BIO','SPAIN'),('KES MAD','SPAIN'),('KES VGO','SPAIN'),('KES VLC','SPAIN'),

  ('IEIRL DUB','IRELAND'),('IEIRL ORK','IRELAND'),

  ('KSE HES','SWEDEN'),('KSE-JV GOT','SWEDEN'),('KSE-JV HES','SWEDEN'),

  ('KFI HEL','FINLAND'),

  ('KDK AAL','DENMARK'),('KDK AAR','DENMARK'),('KDK CPH','DENMARK'),('KDK EBJ','DENMARK'),('KDK ODE','DENMARK'),

  ('KDK-JV AAL','DENMARK'),('KDK-JV AAR','DENMARK'),('KDK-JV CPH','DENMARK'),('KDK-JV EBJ','DENMARK'),('KDK-JV ODE','DENMARK'),

  ('KNO OSL','NORWAY'),('KNO-JV OSL','NORWAY'),

  ('KDE GDY', 'BALTIC'),('EESCR TLL', 'BALTIC'),('KRU LED', 'BALTIC'),('LTLIM KLJ', 'BALTIC'),('LVKLS RIX', 'BALTIC'),('RUMET LED', 'BALTIC'),('RUMET MOW', 'BALTIC'),('RUMSA LED','BALTIC')

  ) d (OFFICE_CD, COUNTRY_DSC)

  SELECT

  booking_num,

  d.COUNTRY_DSC,

  CASE

  WHEN POL_LOCATION_CD IN ('DKAAR', 'DKCPH', 'FIHEL','FIKTK', 'IEORK', 'IEDUB', 'NLRTM', 'PTLEI', 'PTOPO', 'PTLIS', 'ESBIO', 'ESVGO', 'SEGOT', 'SEHEL', 'GBBEL')

  THEN 'NLRTM'

  WHEN POL_LOCATION_CD IN ('DKFRC','FIRAU', 'FIOUL', 'DEBRV', 'NOAES', 'NOBGO', 'NOFRK', 'NOHAL', 'NOHAU', 'NOKRS', 'NOKSU', 'NOLAR', 'NOMSS', 'NOOSL',

  'NOTAE', 'PLGDY', 'SEGVX', 'SEMMA','SENRK', 'SESTO', 'DEHAM')

  THEN 'DEBRV'

  --WHEN POL_LOCATION_CD = 'BEANR' THEN 'BEANR'

  --WHEN POL_LOCATION_CD = 'FRLEH' THEN 'FRLEH'

  ELSE POL_LOCATION_CD END AS POL_LOCATION_CD,

  CASE

  WHEN mq.EQUIPMENT_TYPE_CD LIKE '20%' THEN 1

  WHEN mq.EQUIPMENT_TYPE_CD IN ('40D96', '40R96') THEN 2

  ELSE 2 END AS TEU,

  mq.TYPE_CD,

  mq.VESSEL_CD,

  mq.VOYAGE_CD

  FROM #Offices d

  LEFT JOIN MG_COUNTRY AS mc

  ON d.COUNTRY_DSC = mc.COUNTRY_DSC

  LEFT JOIN MG_OFFICE mo

  ON mc.COUNTRY_CD = mo.COUNTRY_CD

  LEFT JOIN (

  SELECT

  mgd.BOOKING_OFFICE_CD,

  booking_num, -- table alias

  POL_LOCATION_CD, -- table alias

  ber.EQUIPMENT_TYPE_CD,

  mcc.TYPE_CD,

  mbi.VESSEL_CD,

  mbi.VOYAGE_CD

  FROM MG_BOOKING mgd

  INNER JOIN MG_BOOKING_EQUIPMENT_REQ_DETAIL ber

  ON mgd.BOOKING_ID = ber.BOOKING_ID

  INNER JOIN MG_BOOKING_ITINERARY mbi

  ON mgd.BOOKING_ID = mbi.BOOKING_ID

  AND mbi.VESSEL_CD IN (@vessel)

  AND mbi.VOYAGE_CD IN (@voyage)

  INNER JOIN MG_CONTAINER_CODE mcc

  ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD

  WHERE mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P')

  ) mq ON mq.BOOKING_OFFICE_CD = mo.OFFICE_CD

  ORDER BY d.COUNTRY_DSC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 15, 2014 at 6:12 am

  #1738354

  Chris,

  That's the idea, but because I have to expect the OP to identify where those fields come from that don't have a table alias, I didn't want to presume that they came from the set of tables you've placed in the sub-query. We're not operating with quite everything we usually need, and have never been given any sample table data to work with. However, the query doesn't really appear to need sample data, but instead, a better explanation of what each table represents and why you need or don't need a particular join type for it. Once there was a full explanation of what happened with each proffered solution, it was easier to work with, and I'm pretty sure my GROUP BY query will provide what is sought after. If the fields without table aliases can come from your sub-query, then your solution ought to work as well. I try not to use tempdb unless it's necessary. Relatively small lookup tables are usually better as CTEs.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)