Display ALL rows even if no data exists

Question

Display ALL rows even if no data exists

Viewing 15 posts - 31 through 45 (of 56 total)

You must be logged in to reply to this topic. Login to reply

pwalter83 SSChampion Points: 14575 More actions · Answer 1

ChrisM@Work (8/15/2014)
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:

Thanks for your code, Chris. However, it now returns less number of rows than expected and its missing the logic to display the rows for the remaining countries also called BUFFER which I have used in my OP as shown below:

MG_OFFICE AS mo ON mo.COUNTRY_CD = mc.COUNTRY_CD 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'

The query should return all the rows from the MG_BOOKING and MG_BOOKING_ITINERARY tables. I think using CTE is the way to go but as I am not comfortable with using it so cant go any further now.

Could you please help me on this ?

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Paul

Can you provide the following please?

CREATE TABLE for MG_COUNTRY with INSERTs to populate a few sample rows

Do you wish to include every row from this table in the report? Is it missing any rows which you want to see?

CREATE TABLE for MG_OFFICE with INSERTs to populate a few sample rows

Do you wish to include every row from this table in the report? Is it missing any rows which you want to see?

Rowcounts for MG_BOOKING, MG_BOOKING_ITINERARY, MG_BOOKING_EQUIPMENT_REQ_DETAIL, MG_CONTAINER_CODE

Cheers.

^{“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 · Answer 3

ChrisM@Work (8/18/2014)
Hi Paul
Can you provide the following please?
CREATE TABLE for MG_COUNTRY with INSERTs to populate a few sample rows
Do you wish to include every row from this table in the report? Is it missing any rows which you want to see?
CREATE TABLE for MG_OFFICE with INSERTs to populate a few sample rows
Do you wish to include every row from this table in the report? Is it missing any rows which you want to see?
Rowcounts for MG_BOOKING, MG_BOOKING_ITINERARY, MG_BOOKING_EQUIPMENT_REQ_DETAIL, MG_CONTAINER_CODE
Cheers.

Thanks Chris but I really dont know what answers to give now as its getting more and more confusing now.

All I wanted was based on the query that I created below (which is giving the correct result), display the results for the list of countries regardless of the data in the database. In other words, my query gives the correct resultset but if no data exists for the combination of vessel and voyage then no results are displayed.

I want to display the names of all the countries ( as attached in the screenshot) in the report even if the TEU for ONE or ALL them is NULL. The report should display the names of all the mentioned countries below with or without data.

SELECT ber.EQUIPMENT_TYPE_CD, mgd.BOOKING_NUM, CASE WHEN MC.COUNTRY_DSC = 'BELGIUM' 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 MC.COUNTRY_DSC = 'FRANCE' THEN 'FRANCE' WHEN mo.OFFICE_CD IN ('KEU RTM', 'KNL RTM')

THEN 'NETHERLANDS' WHEN mo.OFFICE_CD IN ('KDE PRG')

THEN 'CZECH REPUBLIC' WHEN MC.COUNTRY_DSC = 'SWITZERLAND' THEN 'SWITZERLAND' WHEN MC.COUNTRY_DSC = 'PORTUGAL' THEN 'PORTUGAL' WHEN MC.COUNTRY_DSC

= 'SPAIN' THEN 'SPAIN' WHEN MC.COUNTRY_DSC = 'IRELAND' THEN 'IRELAND' WHEN MC.COUNTRY_DSC = 'SWEDEN' THEN 'SWEDEN' WHEN MC.COUNTRY_DSC

= 'FINLAND' THEN 'FINLAND' WHEN MC.COUNTRY_DSC = 'DENMARK' THEN 'DENMARK' WHEN MC.COUNTRY_DSC = 'NORWAY' 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' WHEN MC.COUNTRY_DSC NOT IN ('BELGIUM', 'AUSTRIA', 'GERMANY', 'NETHERLANDS', 'CZECH REPUBLIC', 'SWITZERLAND', 'PORTUGAL', 'SPAIN',

'IRELAND', 'SWEDEN', 'FINLAND', 'DENMARK', 'NORWAY') OR

MC.COUNTRY_CD NOT IN ('LT', 'LV', 'EE', 'BY', 'SK', 'RU') THEN 'BUFFER' END AS 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 MG_BOOKING AS mgd LEFT OUTER JOIN

MG_BOOKING_ITINERARY AS mbi ON mgd.BOOKING_ID = mbi.BOOKING_ID LEFT OUTER JOIN

MG_BOOKING_EQUIPMENT_REQ_DETAIL AS ber ON mgd.BOOKING_ID = ber.BOOKING_ID LEFT OUTER JOIN

MG_OFFICE AS mo ON mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD AND mgd.BOOKING_STATUS_CD IN ('H', 'F', 'I', 'P') RIGHT OUTER JOIN

MG_COUNTRY AS mc ON mo.COUNTRY_CD = mc.COUNTRY_CD 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)) AND (mbi.VESSEL_CD IS NOT NULL) AND (mbi.VOYAGE_CD IS NOT NULL)

ORDER BY mgd.BOOKING_NUM

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

I know what you are attempting to do here and it wouldn't take long with access to your tables, however scripts for all of the tables along with sample data would be time consuming for you so we make do with what we've got unless it becomes impractical. I don't think I can help without the scripts I've asked for. Here's how to obtain a table create script:

Right-click on the table in Object Explorer. Select 'Script Table as', then 'Create to', then 'New Query Editor Window'. Copy the script and paste it into your post. We'll work on the INSERTs afterwards.

Are you using a query designer or writing freehand? The reason I'm asking is that your query now contains a right join - which many folks find tricky to interpret and hence are rarely seen in freehand code.

Which table is 'voyage'?

Which table is 'Vessel'?

What's TEU?

Thanks.

^{“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 · Answer 5

I still don't have any indication as to the results of the GROUP BY query I submitted. If you didn't run it because it uses a CTE, then I'm going to stop helping. CTEs are not some dangerous odd-ball, but a rather well-known quantity that you should become familiar with and make use of, if for no other reason than they at least do a much better job of making your overall query easier to understand than just racking up one sub-query after another, which tends to obscure the objective. I'm not trying to be difficult... I'm just trying to impress upon you that we've all managed to try and help you without the usual things we need, like table creation statements and sample data, and in my case, not only do I not have those things, I don't even know if you tried my GROUP BY query or not, and what I keep hearing is an irrational fear of the CTE. If you want help, you have to at least take responsibility for trying what is offered. Fear is not an acceptable excuse for doing nothing, and besides, you never learn anything that way.

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

pwalter83 SSChampion Points: 14575 More actions · Answer 6

sgmunson (8/18/2014)
I still don't have any indication as to the results of the GROUP BY query I submitted. If you didn't run it because it uses a CTE, then I'm going to stop helping. CTEs are not some dangerous odd-ball, but a rather well-known quantity that you should become familiar with and make use of, if for no other reason than they at least do a much better job of making your overall query easier to understand than just racking up one sub-query after another, which tends to obscure the objective. I'm not trying to be difficult... I'm just trying to impress upon you that we've all managed to try and help you without the usual things we need, like table creation statements and sample data, and in my case, not only do I not have those things, I don't even know if you tried my GROUP BY query or not, and what I keep hearing is an irrational fear of the CTE. If you want help, you have to at least take responsibility for trying what is offered. Fear is not an acceptable excuse for doing nothing, and besides, you never learn anything that way.

Hi Steve,

I have already tried your query and its returning 120 odd rows less than it should. Also, if there are no matching results for vessel and voyage selected by the user, then it returns a blank. My requirement is that it should show the resultset for each country irrespective iof the TEU data as displayed in the attached screenshot.

I have nothing against using CTE, in fact I would be more than happy to use CTE. Why should I complain if I am getting a ready to use code:-)

Thanks.

sgmunson SSC Guru Points: 110639 More actions · Answer 7

Okay, then... It's become obvious that we simply do not have the proper understanding of your data. We need to know the following:

1.) What a record in each of the tables in your query represents. Be explicit...

2.) Which of the tables contains the data to be displayed, and which of the tables are purely for reference, to do things like identify the country, for example

Until I have explicit and detailed descriptions of what the records in each table represent, we're not going to get very far. So far, this data has been asked for, but just not provided. There's no longer any alternative.

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

pwalter83 SSChampion Points: 14575 More actions · Answer 8

ChrisM@Work (8/18/2014)
I know what you are attempting to do here and it wouldn't take long with access to your tables, however scripts for all of the tables along with sample data would be time consuming for you so we make do with what we've got unless it becomes impractical. I don't think I can help without the scripts I've asked for. Here's how to obtain a table create script:
Right-click on the table in Object Explorer. Select 'Script Table as', then 'Create to', then 'New Query Editor Window'. Copy the script and paste it into your post. We'll work on the INSERTs afterwards.
Are you using a query designer or writing freehand? The reason I'm asking is that your query now contains a right join - which many folks find tricky to interpret and hence are rarely seen in freehand code.
Which table is 'voyage'?
Which table is 'Vessel'?
What's TEU?
Thanks.

I know how to create a DDL script, its just that not all of the columns in a table are being used in the report.

The following are the scripts for MG_OFFICE and MG_COUNTRY tables-

CREATE TABLE [dbo].[MG_COUNTRY](

[COUNTRY_CD] [varchar](2) NOT NULL,

[COUNTRY_DSC] [varchar](35) NOT NULL,

[PHONE_COUNTRY_CODE] [varchar](10) NULL,

[FAX_SEND_PROC_NAME] [varchar](50) NULL,

[FAX_RESULT_PROC_NAME] [varchar](50) NULL,

[DELETED_FLG] [char](1) NOT NULL,

[LAST_UPDATE_USER_ID] [numeric](10, 0) NOT NULL,

[LAST_UPDATE_DT] [datetime] NOT NULL,

[CREATE_USER_ID] [numeric](10, 0) NOT NULL,

[CREATE_DT] [datetime] NOT NULL,

[CONTINENT_CD] [varchar](3) NULL,

[FAX_FILE_TYPE] [varchar](5) NULL,

[FAX_SERVICE_TYPE] [varchar](10) NULL,

[FAX_SERVICE_SMTP] [varchar](255) NULL,

[WEB_PDF_MK_TIMING] [char](1) NULL,

[PRINT_ON_BOARD_DEFAULT_CD] [char](1) NULL,

CONSTRAINT [PK_MG_COUNTRY] PRIMARY KEY CLUSTERED

(

[COUNTRY_CD] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[MG_OFFICE](

[OFFICE_CD] [varchar](10) NOT NULL,

[OFFICE_LOCATION_CD] [varchar](5) NOT NULL,

[ORGANIZATION_CD] [varchar](6) NOT NULL,

[ORGANIZATION_COMPANY_CD] [varchar](6) NOT NULL,

[TOKYO_ACCOUNTING_CD] [varchar](6) NULL,

[DUNS] [varchar](10) NULL,

[ADDRESS_1] [varchar](35) NULL,

[ADDRESS_2] [varchar](35) NULL,

[ADDRESS_3] [varchar](35) NULL,

[ADDRESS_4] [varchar](35) NULL,

[CITY] [varchar](35) NULL,

[POSTAL_CODE] [varchar](10) NULL,

[COUNTRY_CD] [varchar](2) NULL,

[STATE_CD] [varchar](3) NULL,

[AUTO_BL_ASSIGN_FLG] [char](1) NOT NULL,

[AUTO_BOOK_ASSIGN_FLG] [char](1) NOT NULL,

[DELETED_FLG] [char](1) NOT NULL,

[LAST_UPDATE_USER_ID] [numeric](10, 0) NOT NULL,

[LAST_UPDATE_DT] [datetime] NOT NULL,

[CREATE_USER_ID] [numeric](10, 0) NOT NULL,

[CREATE_DT] [datetime] NOT NULL,

[BL_PARTY_SEARCH_OPTION] [varchar](14) NULL,

CONSTRAINT [PK_MG_OFFICE] PRIMARY KEY CLUSTERED

(

[OFFICE_CD] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

The vessel and voyage columns are populated from the MG_BOOKING_ITINERARY table.

TEU is calculated using the EQUIPMENT_TYPE_CD column from the MG_BOOKING_EQUIPMENT_REQ_DETAIL table as follows:

CASE WHEN EQUIPMENT_TYPE_CD LIKE '20%' THEN 1 WHEN EQUIPMENT_TYPE_CD = '0' THEN 0 ELSE 2 END AS TEU.

I used freehand to create this query, the reason for left join to MG_BOOKING_ITINERARY and MG_BOOKING tables is due to the fact that all the rows from these tables need to be displayed.

Hope I have answered your questions.

Thanks.

sgmunson SSC Guru Points: 110639 More actions · Answer 9

Okay, so now the excuse for not explaining EVERYTHING is that some of the columns aren't in use??? Really? Sorry, but I'm done with this. Every explanation I've seen so far has done little or nothing to make this the slightest bit more clear. This person simply can't be bothered, and seems more interested in leading a chase than actually getting useful answers. I'm simply not willing to help someone that insists on playing this game.

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

pwalter83 SSChampion Points: 14575 More actions · Answer 10

sgmunson (8/18/2014)
Okay, so now the excuse for not explaining EVERYTHING is that some of the columns aren't in use??? Really? Sorry, but I'm done with this. Every explanation I've seen so far has done little or nothing to make this the slightest bit more clear. This person simply can't be bothered, and seems more interested in leading a chase than actually getting useful answers. I'm simply not willing to help someone that insists on playing this game.

Can you just not reply instead of writing provocatively to discourage others from providing solutions ?

sgmunson SSC Guru Points: 110639 More actions · Answer 11

pwalter83 (8/18/2014)
sgmunson (8/18/2014)
Okay, so now the excuse for not explaining EVERYTHING is that some of the columns aren't in use??? Really? Sorry, but I'm done with this. Every explanation I've seen so far has done little or nothing to make this the slightest bit more clear. This person simply can't be bothered, and seems more interested in leading a chase than actually getting useful answers. I'm simply not willing to help someone that insists on playing this game.
Can you just not reply instead of writing provocatively to discourage others from providing solutions ?

Can you just post what you were asked for repeatedly instead of avoiding it like the plague? I'm absolutely certain it would get better results than what you've provided so far.

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

pwalter83 (8/18/2014)
sgmunson (8/18/2014)
Okay, so now the excuse for not explaining EVERYTHING is that some of the columns aren't in use??? Really? Sorry, but I'm done with this. Every explanation I've seen so far has done little or nothing to make this the slightest bit more clear. This person simply can't be bothered, and seems more interested in leading a chase than actually getting useful answers. I'm simply not willing to help someone that insists on playing this game.
Can you just not reply instead of writing provocatively to discourage others from providing solutions ?

I, for one, would be happy to consider your problem...but from reading thro this thread...I have to agree that DDL and sample data (and the expected results based on your sample data) would help considerably.

so far all I see is DDL for two tables (out of six?) and no sample data or expected results.

I would expect that if you provide what has been requested that your problem can be resolved quickly and with tried and tested code.

kind regards

edit typos

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

J Livingston SQL (8/18/2014)
pwalter83 (8/18/2014)
sgmunson (8/18/2014)
Okay, so now the excuse for not explaining EVERYTHING is that some of the columns aren't in use??? Really? Sorry, but I'm done with this. Every explanation I've seen so far has done little or nothing to make this the slightest bit more clear. This person simply can't be bothered, and seems more interested in leading a chase than actually getting useful answers. I'm simply not willing to help someone that insists on playing this game.
Can you just not reply instead of writing provocatively to discourage others from providing solutions ?
I, for one, would be happy to consider your problem...but from reading thro this thread...I have to agree that DDL and sample data (and the expected results based on your sample data) would help considerably.
so far all I see is DDL for two tables (out of six?) and no sample data or expected results.
I would expect that if you provide what has been requested that your problem can be resolved quickly and with tried and tested code.
kind regards
edit typos

I'm also still in. Paul, can you please provide INSERTs to populate the two tables with say 30 or 40 rows of data? I think it could go a long way to sorting this out. Cheers.

^{“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 · Answer 14

I'm also still in. Paul, can you please provide INSERTs to populate the two tables with say 30 or 40 rows of data? I think it could go a long way to sorting this out. Cheers.

Thank you all for your eagerness to help.

On popular demand :-), please find the DDL and sample data for all the tables used in the query below. Just a humble request, could you please use the query below as the basis for your code as it returns correct resultset in terms of row counts. I mean I wish to use the functionality of my query but at the same time want to show all the listed countries as shown in the attached screenshot (2 examples) if no data exists.

Thanks a lot.

CREATE TABLE [dbo].[MG_OFFICE](

[OFFICE_CD] [varchar](10) NOT NULL,

[COUNTRY_CD] [varchar](2) NULL)

insert into [MG_OFFICE]

values('KBE ANR', 'BE')

insert into [MG_OFFICE]

values('KBE ZEE', 'BE')

insert into [MG_OFFICE]

values('KEU ANR', 'BE')

insert into [MG_OFFICE]

values('KDE DUS', 'DE')

insert into [MG_OFFICE]

values('KDE BRE', 'DE')

insert into [MG_OFFICE]

values('KDE FRA', 'DE')

insert into [MG_OFFICE]

values('KDE HAM', 'DE')

insert into [MG_OFFICE]

values('ESBLR BCN', 'ES')

insert into [MG_OFFICE]

values('ESBLR MAD', 'ES')

insert into [MG_OFFICE]

values('ESBLR SDR', 'ES')

insert into [MG_OFFICE]

values('ESBLR TAR', 'ES')

insert into [MG_OFFICE]

values('EESCR TLL', 'EE')

insert into [MG_OFFICE]

values('KDE GDY', 'DE')

insert into [MG_OFFICE]

values('KRU LED', 'RU')

insert into [MG_OFFICE]

values('LTLIM KLJ', 'LT')

insert into [MG_OFFICE]

values('KEU RTM', 'NL')

insert into [MG_OFFICE]

values('KNL RTM', 'NL')

insert into [MG_OFFICE]

values('KFR BOD', 'FR')

insert into [MG_OFFICE]

values('KFR DKK', 'FR')

insert into [MG_OFFICE]

values('KFR FOS', 'FR')

insert into [MG_OFFICE]

values('KFR LEH', 'FR')

insert into [MG_OFFICE]

values('KFR LIO', 'FR')

insert into [MG_OFFICE]

values('KSE HES', 'SE')

insert into [MG_OFFICE]

values('KSE-JV GOT', 'SE')

insert into [MG_OFFICE]

values('KSE-JV HES', 'SE')

CREATE TABLE [dbo].[MG_COUNTRY](

[COUNTRY_CD] [varchar](2) NOT NULL,

[COUNTRY_DSC] [varchar](35) NOT NULL

)

insert into [MG_COUNTRY]

values('BE', 'BELGIUM')

insert into [MG_COUNTRY]

values('FR', 'FRANCE')

insert into [MG_COUNTRY]

values('SE', 'SWEDEN')

insert into [MG_COUNTRY]

values('ES', 'SPAIN')

insert into [MG_COUNTRY]

values('NL', 'NETHERLANDS')

insert into [MG_COUNTRY]

values('RU', 'RUSSIA')

insert into [MG_COUNTRY]

values('EE', 'ESTONIA')

insert into [MG_COUNTRY]

values('LT', 'LITHUANIA')

CREATE TABLE [dbo].[MG_BOOKING](

[BOOKING_ID] [numeric](10, 0) NOT NULL,

[BOOKING_NUM] [varchar](15) NULL,

[BOOKING_OFFICE_CD] [varchar](10) NULL,

[BOOKING_STATUS_CD] [char](1) NULL,

[POL_LOCATION_CD] [varchar](5) NULL

)

INSERT INTO [MG_BOOKING]

values('1','A1','KBE ANR','F','BEANR')

INSERT INTO [MG_BOOKING]

values('2','A2','KBE ANR','P','BEANR')

INSERT INTO [MG_BOOKING]

values('3','A3','KBE ZEE','I','BEANR')

INSERT INTO [MG_BOOKING]

values('4','A4','KBE ZEE', 'H','BEANR')

INSERT INTO [MG_BOOKING]

values('5','B1','KDE DUS','F','NLRTM')

INSERT INTO [MG_BOOKING]

values('6','B2','KDE DUS', 'I','NLRTM')

INSERT INTO [MG_BOOKING]

values('7','B3','KBE ZEE','P','NLRTM')

INSERT INTO [MG_BOOKING]

values('8','B4','KDE BRE','F','DEBRV')

INSERT INTO [MG_BOOKING]

values('9','C1','KDE BRE','F','DEBRV')

INSERT INTO [MG_BOOKING]

values('10','C2','KSE HES','H','DEBRV')

INSERT INTO [MG_BOOKING]

values('11','C3','KSE HES','I','DEBRV')

INSERT INTO [MG_BOOKING]

values('12','D4','KFR DKK','P','DEBRV')

INSERT INTO [MG_BOOKING]

values('13','D1','KFR DKK','P','CNTAO')

INSERT INTO [MG_BOOKING]

values('14','D2','LTLIM KLJ','I','SGSIN')

INSERT INTO [MG_BOOKING]

values('15','D3','LTLIM KLJ','I','CNSHA')

INSERT INTO [MG_BOOKING]

values('16','E1','ESBLR BCN','P','ESBCN')

INSERT INTO [MG_BOOKING]

values('17','E2','ESBLR BCN','F','ESBCN')

INSERT INTO [MG_BOOKING]

values('18','E3','EESCR TLL','H','ESBCN')

INSERT INTO [MG_BOOKING]

values('19','E4','ESBLR BCN','P','ESVLC')

INSERT INTO [MG_BOOKING]

values('20','E5','EESCR TLL','I','ESVLC')

CREATE TABLE [dbo].[MG_BOOKING_ITINERARY](

[BOOKING_ID] [numeric](10, 0) NULL,

[VESSEL_CD] [varchar](10) NULL,

[VOYAGE_CD] [varchar](12) NULL

)

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('1','HSMB','22E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('2','JKB','B071S')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('3','CSCEX','005E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('4','GDGB','052E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('5','HJSP','0009E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('6','CSCEX','005E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('7','HNJEUR','0005E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('8','STPAO','016W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('9','HPHO','121W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('10','HPHO','122W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('11','HPHO','121W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('12','CSNGY','034W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('13','COSPRD','009W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('14','COSPRD','009W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('15','HMBB','038W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('16','HMBB','038W')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('17','HKGB','021E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('18','HKGB','021E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('19','YMUPSU','001E')

INSERT INTO [MG_BOOKING_ITINERARY]

VALUES('20','YMUPSU','001E')

CREATE TABLE [dbo].[MG_BOOKING_EQUIPMENT_REQ_DETAIL](

[EQUIPMENT_TYPE_CD] [varchar](5) NULL,

[BOOKING_ID] [numeric](10, 0) NOT NULL

)