SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query


SQL Query

Author
Message
pwalter83
pwalter83
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2134
ChrisM@Work (1/11/2013)
I think this better fits the spec:

SELECT SUM(teu) AS TEU

FROM NCV_BL ncv

JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD

JOIN MG_VSLVOY_PORT_CONTROL vpc
ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

WHERE NOT EXISTS (
SELECT 1
FROM MG_VSLVOY_SCHEDULE vs
WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
)
AND BL_ID = '17231410'



Thanks for your reply.

Your query shows the SUM of TEU as NULL, it should be 4. Am I missing something ?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16360 Visits: 19554
pwalter83 (1/11/2013)
ChrisM@Work (1/11/2013)
I think this better fits the spec:

SELECT SUM(teu) AS TEU

FROM NCV_BL ncv

JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD

JOIN MG_VSLVOY_PORT_CONTROL vpc
ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

WHERE NOT EXISTS (
SELECT 1
FROM MG_VSLVOY_SCHEDULE vs
WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
)
AND BL_ID = '17231410'



Thanks for your reply.

Your query shows the SUM of TEU as NULL, it should be 4. Am I missing something ?


Can't tell without access to your data. This is how I'd do it. Work your way through the tables, using sometablealias.BL_ID = '17231410'.

“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
Exploring Recursive CTEs by Example Dwain Camps
pwalter83
pwalter83
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2134
ChrisM@Work (1/11/2013)
pwalter83 (1/11/2013)
ChrisM@Work (1/11/2013)
I think this better fits the spec:

SELECT SUM(teu) AS TEU

FROM NCV_BL ncv

JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD

JOIN MG_VSLVOY_PORT_CONTROL vpc
ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

WHERE NOT EXISTS (
SELECT 1
FROM MG_VSLVOY_SCHEDULE vs
WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
)
AND BL_ID = '17231410'



Thanks for your reply.

Your query shows the SUM of TEU as NULL, it should be 4. Am I missing something ?


Can't tell without access to your data. This is how I'd do it. Work your way through the tables, using sometablealias.BL_ID = '17231410'.


Thanks Chris,

I tried but could'nt get around to change the result of TEU as per your message. It still shows up as NULL for whatever reason and should come out to 4 as per the sample data in my attached query.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16360 Visits: 19554
Hi Paul

I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!

Cheers

ChrisM

“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
Exploring Recursive CTEs by Example Dwain Camps
pwalter83
pwalter83
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2134
ChrisM@Work (1/11/2013)
Hi Paul

I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!

Cheers

ChrisM


Thanks a lot Chris, whenever you have the time.

take care and Cheers !!!

gotta have some beer too when I get back home.
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2056 Visits: 10370
Hi Paul

Your sample data set doesn't have a matching row between MG_VSLVOY_HEADER and MG_VSLVOY_PORT_CONTROL:

SELECT ncv.*,  vh.VSLVOY_HEADER_ID

FROM NCV_BL ncv

JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD

--JOIN MG_VSLVOY_PORT_CONTROL vpc
-- ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

--WHERE NOT EXISTS (
-- SELECT 1
-- FROM MG_VSLVOY_SCHEDULE vs
-- WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
-- AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
-- )
-- AND BL_ID = '17231410'



Here's your sample data script for those who don't like attachments:
-- sample data script
CREATE TABLE [dbo].[NCV_BL](
[BL_ID] [decimal](10, 0) NOT NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](10) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](12) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[TEU] [decimal] (10,3) null,
[PLACE_OF_RECEIPT] [nvarchar](5) NULL,
[PLACE_OF_DELIVERY] [nvarchar](5) NULL,
[SAISAN_MONTH] [nvarchar](6) NULL
) ON [PRIMARY]

INSERT INTO [NCV_BL]
VALUES ('17231410','DKAAR','IDJKT','HNJAFR','0002E','E', '4','DEYTN','EGPSW','200802')


CREATE TABLE [dbo].[MG_VSLVOY_PORT_CONTROL](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL
) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES ('1629990', '1342190'),('1629991', '1342191'),('1629992', '1342192'),
('1629993', '1342193'),('1629994', '1342194'),('1629995', '1342195'),
('1629996', '1342196'),('1629997', '1342197'),('1630001', '1342201'),
('1629998', '1379563'),('1629999', '1379564'),('1630000', '1379565')


CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,
[PORT_CD] [varchar](5) NOT NULL
) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES ('1342190','DEHAM'),('1342191','NLRTM'),('1342192','FRLEH'),('1342193','ESALG'),('1342194','EGSUZ'),('1342195','SGSIN'),
('1342196','CNYTN'),('1342197','HKHKG'),('1342201','CNSHA'),('1379563','CNTXG'),('1379564','KRKAN'),('1379565','KRPUS')


CREATE TABLE [dbo].[MG_VSLVOY_HEADER](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [nvarchar](10) NOT NULL,
[VOYAGE_NUM] [nvarchar](12) NULL,
[LEG_CD] [nvarchar](1) NULL
) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_HEADER]
VALUES ('762476','HNJAFR','0002E','E'),('696131','COSHEL','0003W','W'),('669246','HJPA','0007R','R'),('761970','ALSAB','0008Q','Q')




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86752 Visits: 41103
ChrisM@Work (1/11/2013)
Hi Paul

I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!

Cheers

ChrisM


Voluntary job change? Or???

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pwalter83
pwalter83
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2134
ChrisM@home (1/13/2013)
Hi Paul

Your sample data set doesn't have a matching row between MG_VSLVOY_HEADER and MG_VSLVOY_PORT_CONTROL:

SELECT ncv.*,  vh.VSLVOY_HEADER_ID

FROM NCV_BL ncv

JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD

--JOIN MG_VSLVOY_PORT_CONTROL vpc
-- ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID

--WHERE NOT EXISTS (
-- SELECT 1
-- FROM MG_VSLVOY_SCHEDULE vs
-- WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
-- AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
-- )
-- AND BL_ID = '17231410'



Here's your sample data script for those who don't like attachments:
-- sample data script
CREATE TABLE [dbo].[NCV_BL](
[BL_ID] [decimal](10, 0) NOT NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](10) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](12) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[TEU] [decimal] (10,3) null,
[PLACE_OF_RECEIPT] [nvarchar](5) NULL,
[PLACE_OF_DELIVERY] [nvarchar](5) NULL,
[SAISAN_MONTH] [nvarchar](6) NULL
) ON [PRIMARY]

INSERT INTO [NCV_BL]
VALUES ('17231410','DKAAR','IDJKT','HNJAFR','0002E','E', '4','DEYTN','EGPSW','200802')


CREATE TABLE [dbo].[MG_VSLVOY_PORT_CONTROL](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL
) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES ('1629990', '1342190'),('1629991', '1342191'),('1629992', '1342192'),
('1629993', '1342193'),('1629994', '1342194'),('1629995', '1342195'),
('1629996', '1342196'),('1629997', '1342197'),('1630001', '1342201'),
('1629998', '1379563'),('1629999', '1379564'),('1630000', '1379565')


CREATE TABLE [dbo].[MG_VSLVOY_SCHEDULE](
[VSLVOY_SCHEDULE_ID] [numeric](10, 0) NOT NULL,
[PORT_CD] [varchar](5) NOT NULL
) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES ('1342190','DEHAM'),('1342191','NLRTM'),('1342192','FRLEH'),('1342193','ESALG'),('1342194','EGSUZ'),('1342195','SGSIN'),
('1342196','CNYTN'),('1342197','HKHKG'),('1342201','CNSHA'),('1379563','CNTXG'),('1379564','KRKAN'),('1379565','KRPUS')


CREATE TABLE [dbo].[MG_VSLVOY_HEADER](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [nvarchar](10) NOT NULL,
[VOYAGE_NUM] [nvarchar](12) NULL,
[LEG_CD] [nvarchar](1) NULL
) ON [PRIMARY]

INSERT INTO [MG_VSLVOY_HEADER]
VALUES ('762476','HNJAFR','0002E','E'),('696131','COSHEL','0003W','W'),('669246','HJPA','0007R','R'),('761970','ALSAB','0008Q','Q')



Hi Chris,

I am really sorry for this blunder and for wasting your precious time.

Please find the updated ddl and sample data attached.
Attachments
ddl.txt (18 views, 2.00 KB)
pwalter83
pwalter83
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2134
ChrisM@Work (1/11/2013)
Hi Paul

I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!

Cheers

ChrisM


Hi Chris,

Best of luck for your next venture !!!
pwalter83
pwalter83
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2134
ChrisM@Work (1/11/2013)
Hi Paul

I'll try to have a look over the weekend. Meantime, it's the last 5 mins of my last day here - and it's BEERTIME!

Cheers

ChrisM


Hi Chris,

I am still struggling with this query. Any possibility you could have a look whenever you get a chance ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search