Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Query Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 8:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:09 AM
Points: 580, Visits: 1,839
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 ?
Post #1406082
Posted Friday, January 11, 2013 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 6,863, Visits: 14,161
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
Post #1406102
Posted Friday, January 11, 2013 9:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:09 AM
Points: 580, Visits: 1,839
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.

Post #1406141
Posted Friday, January 11, 2013 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 6,863, Visits: 14,161
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
Post #1406142
Posted Friday, January 11, 2013 9:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:09 AM
Points: 580, Visits: 1,839
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.
Post #1406146
Posted Sunday, January 13, 2013 2:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 1,037, Visits: 6,953
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
Post #1406458
Posted Sunday, January 13, 2013 9:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1406566
Posted Monday, January 14, 2013 2:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:09 AM
Points: 580, Visits: 1,839
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.


  Post Attachments 
ddl.txt (2 views, 3.00 KB)
Post #1406600
Posted Monday, January 14, 2013 2:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:09 AM
Points: 580, Visits: 1,839
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 !!!
Post #1406603
Posted Tuesday, January 15, 2013 2:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:09 AM
Points: 580, Visits: 1,839
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 ?
Post #1407102
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse