August 7, 2014 at 3:47 am
Hi,
I have a requirement to display all the list of countries in an SSRS report as below even if there is no data/values available in the database. As an example below there is no data for Austria and Sweden but they should still show up:
ANRBRVRTMLEH
Germany3510274
Austria
Czech Rep38
Netherlands88122
Belgium5143
France10243
Ireland2
Sweden
Finland16
Denmark222
Norway
Switzerland1337
Portugal14
Spain4
Baltics1
Buffer30
The SQL code that I created doesnt work as it only shows rows for which there is data available for the countries listed above.
Please find the SQL code attached.
Could someone please advise any changes to above code so that the requirement can be accomplished ?
Thanks.
August 7, 2014 at 5:15 am
I would use the following approach:
WITH cte_with_values AS
(
SELECT xxx
FROM abc INNER JOIN cde ON abc.col1=cde.col1
)
SELECT your_columns
FROM tbl_with_list_of_countries t
LEFT OUTER JOIN cte ON t.country=cte.country
The verbal description:
Within the cte calculate the values you need for the countries that do have values.
Then use a table (or a list) to left join the cte against to get the result you're looking for.
You might need to change the data type for BRV, RTM, and LEH to VARCHAR(x) if you need blank vlaus for the countries without corresponding values. Alternateively, you could display NULL or 0.
August 7, 2014 at 5:35 am
To add to what Lutz posted, you have things like the following in your code...
WHEN mo.OFFICE_CD IN ('KBE ANR','KBE ZEE', 'KEU ANR') THEN 'BELGIUM'
It would be MUCH better if you had a 2 column table of values that would hold the Office Codes similar to the following...
OfficeCode Country
KBE ANR Belgium
KBE ZEE Belgium
KEU ANR Belgium
That way, you could use simple joins instead of massive case statements. The side benefit would be that if you opened a new office, you could simply add it to the table instead of having to find and change all of the code that is affected.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2014 at 6:21 am
This should get you started with Jeff's suggestion:
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 (VALUES
--('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),
--('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')
--) d (COUNTRY_DSC)
--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 ('VCRB')
AND mbi.VOYAGE_CD IN ('5')
left JOIN MG_CONTAINER_CODE mcc
ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
ORDER BY d.COUNTRY_DSC
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
August 7, 2014 at 7:52 am
LutzM (8/7/2014)
I would use the following approach:
WITH cte_with_values AS(
SELECT xxx
FROM abc INNER JOIN cde ON abc.col1=cde.col1
)
SELECT your_columns
FROM tbl_with_list_of_countries t
LEFT OUTER JOIN cte ON t.country=cte.country
The verbal description:
Within the cte calculate the values you need for the countries that do have values.
Then use a table (or a list) to left join the cte against to get the result you're looking for.
You might need to change the data type for BRV, RTM, and LEH to VARCHAR(x) if you need blank vlaus for the countries without corresponding values. Alternateively, you could display NULL or 0.
Hi Lutz, thanks for your suggestion but I am unable to implement it as I am not comfortable with using CTE.
I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.
Is there any other way you can suggest ?
Thanks.
August 7, 2014 at 8:02 am
ChrisM@Work (8/7/2014)
This should get you started with Jeff's suggestion:
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 (VALUES
--('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),
--('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')
--) d (COUNTRY_DSC)
--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 ('VCRB')
AND mbi.VOYAGE_CD IN ('5')
left JOIN MG_CONTAINER_CODE mcc
ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
ORDER BY d.COUNTRY_DSC
Thanks.
What Jeff mentioned is secondary to what the real issue I am facing. I am stuck with the basic issue of displaying all the rows (list of countries) even if there is no data in the table.
My primary concern is to work on Lutz suggestion which is to use CTE which I am not comfortable with. Could you please suggest something with regards to my original post ?
I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.
Thanks.
August 7, 2014 at 8:09 am
Step 1:
Write the query to produce the result set you're looking for (except the countries without values).
Step 2: post that query.
Step 3:
Tell us the source tabel where we can find the column ANR.
August 7, 2014 at 8:15 am
pwalter83 (8/7/2014)
ChrisM@Work (8/7/2014)
This should get you started with Jeff's suggestion:
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 (VALUES
--('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),
--('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')
--) d (COUNTRY_DSC)
--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 ('VCRB')
AND mbi.VOYAGE_CD IN ('5')
left JOIN MG_CONTAINER_CODE mcc
ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
ORDER BY d.COUNTRY_DSC
Thanks.
What Jeff mentioned is secondary to what the real issue I am facing. I am stuck with the basic issue of displaying all the rows (list of countries) even if there is no data in the table.
My primary concern is to work on Lutz suggestion which is to use CTE which I am not comfortable with. Could you please suggest something with regards to my original post ?
I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.
Thanks.
Did you look at the code, Paul? Check the FROM-list (FROM clause).
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
August 7, 2014 at 8:50 am
ChrisM@Work (8/7/2014)
pwalter83 (8/7/2014)
ChrisM@Work (8/7/2014)
This should get you started with Jeff's suggestion:
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 (VALUES
--('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),
--('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')
--) d (COUNTRY_DSC)
--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 ('VCRB')
AND mbi.VOYAGE_CD IN ('5')
left JOIN MG_CONTAINER_CODE mcc
ON ber.EQUIPMENT_TYPE_CD = mcc.CONTAINER_CD
ORDER BY d.COUNTRY_DSC
Thanks.
What Jeff mentioned is secondary to what the real issue I am facing. I am stuck with the basic issue of displaying all the rows (list of countries) even if there is no data in the table.
My primary concern is to work on Lutz suggestion which is to use CTE which I am not comfortable with. Could you please suggest something with regards to my original post ?
I am currently working on the report where the user selects values from the 2 dropdownboxes and then based on the values, the rows are populated with counts. My requirement is to display all the list of countries even there are no counts for a specific country.
Thanks.
Did you look at the code, Paul? Check the FROM-list (FROM clause).
Thanks Chris. I tried your code but it displays all the data irrespective of the values chosen from the dropdown list. For e.g. if I select 'VCRB' and '076W' as the values it would show me all the values not taking the parameters into consideration.
The report should show all the countries irrespective of the data exists or not as shown in the attached - Resultset1.
But what I am currently getting is as shown in Resultset2. Its only showing countries for which data exists.
Thanks.
August 7, 2014 at 8:58 am
I don't see any parameters in your code, Paul.
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
August 7, 2014 at 9:08 am
ChrisM@Work (8/7/2014)
I don't see any parameters in your code, Paul.
Sorry Chris, the code should be as attached. I am making blunders after blunders. This is what happens when you have little knowledge and this then leads to frustration and thats what I am experiencing at the moment. I am in the wrong profession is all I can say and its too late to change now.
You have helped immensely before and saved my job many times and I was hoping I could get your help this time too but it seems I am not even able to explain my problem clearly.
Please let me know of any missing information I can provide.
Thanks a lot.
August 7, 2014 at 9:15 am
Like I said before: Step 1 is to get the query you're looking for without the additional countries that aren't included in the result set.
Please post that query.
August 7, 2014 at 9:18 am
LutzM (8/7/2014)
Like I said before: Step 1 is to get the query you're looking for without the additional countries that aren't included in the result set.Please post that query.
Sorry Lutz, I dont know what you mean by 'without the additional countries that aren't included in the result set'. Please find my query I created attached.
Thanks.
August 8, 2014 at 3:03 am
ChrisM@Work (8/7/2014)
I don't see any parameters in your code, Paul.
Any ideas, Chris ??
August 8, 2014 at 9:36 am
Basically your problem is that you are using the country derived from MG_OFFICE instead of the country derived from your fixed list. You could try something like
select booking no, d.country, .....
from (VALUES
('GERMANY'),('AUSTRIA'),('NETHERLANDS'),('BELGIUM'),('FRANCE'),
('IRELAND'),('SWEDEN'),('FINLAND'),('DENMARK'),('NORWAY'),('CZECH REPUBLIC'),('SWITZERLAND'),('PORTUGAL'),('SPAIN'),('BALTICS'),('BUFFER')
) d (COUNTRY_DSC)
left join MG_COUNTRY AS mc ON d.COUNTRY_DSC = mc.COUNTRY_DSC
left join MG_OFFICE AS mo ON mo.COUNTRY_DSC = mc.COUNTRY_DSC AND d.COUNTRY_DSC = CASE
WHEN mo.OFFICE_CD IN ('KBE ANR','KBE ZEE', 'KEU ANR') THEN 'BELGIUM'
WHEN mo.OFFICE_CD IN ('KDE VIE') THEN 'AUSTRIA'
WHEN mo.OFFICE_CD IN ('KDE BRE', 'KDE BRV', 'KDE DUS', 'KDE FRA', 'KDE HAM', 'KDE MUC', 'KDE STR') THEN 'GERMANY'
WHEN mo.OFFICE_CD IN ('KFR BOD','KFR DKK','KFR FOS','KFR LEH','KFR LIO','KFR MRS','KFR PAR','EXAF PARIS','FRBAL BOD','KEU LEH')
THEN 'FRANCE'
WHEN mo.OFFICE_CD IN ('KEU RTM', 'KNL RTM') THEN 'NETHERLANDS'
WHEN mo.OFFICE_CD IN ('KDE PRG') THEN 'CZECH REPUBLIC'
WHEN mo.OFFICE_CD IN ('CHGLS BSL') THEN 'SWITZERLAND'
WHEN mo.OFFICE_CD IN ('KPT LIS','KPT OPO') THEN 'PORTUGAL'
WHEN mo.OFFICE_CD IN ('ESBLR BCN','ESBLR MAD','ESBLR SDR','ESBLR TAR','ESBLR VCL','ESBLR VGO','ESBLR VLC','KES BCN','KES BIO'
,'KES MAD','KES VGO','KES VLC') THEN 'SPAIN'
WHEN mo.OFFICE_CD IN ('IEIRL DUB','IEIRL ORK') THEN 'IRELAND'
WHEN mo.OFFICE_CD IN ('KSE HES','KSE-JV GOT','KSE-JV HES') THEN 'SWEDEN'
WHEN mo.OFFICE_CD IN ('KFI HEL') THEN 'FINLAND'
WHEN mo.OFFICE_CD IN ('KDK AAL','KDK AAR','KDK CPH','KDK EBJ','KDK ODE','KDK-JV AAL','KDK-JV AAR','KDK-JV CPH','KDK-JV EBJ','KDK-JV ODE')
THEN 'DENMARK'
WHEN mo.OFFICE_CD IN ('KNO OSL','KNO-JV OSL') THEN 'NORWAY'
WHEN mo.OFFICE_CD IN ('KDE GDY', 'EESCR TLL', 'KRU LED', 'LTLIM KLJ', 'LVKLS RIX', 'RUMET LED', 'RUMET MOW', 'RUMSA LED')
THEN 'BALTIC' ELSE 'BUFFER'
END
left join MG_BOOKING mgd on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
......
(you need to add the rest of the select list and the remaining joins to this).
Tom
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply