SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL query

Author

Topic

lahsiv2004
Starting Member

13 Posts

Posted - 2012-10-31 : 06:32:10

Hi,

I have a requirement to create a CASE statement for the following condition:

SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is not present in MG_VSLVOY_SCHEDULE.PORT_CD for the SAISAN_VESSEL_CD, SAISAN_VOYAGE_CD and SAISAN_LEG_CD in NCV_BL.

The DDL and sample data is as follows:

--------------------------------
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]

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]

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

) ON [PRIMARY]

CREATE TABLE [dbo].[MG_VSLVOY_HEADER](
[VSLVOY_HEADER_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [varchar](10) NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[MG_BILL_OF_LADING](
[BL_ID] [numeric](10, 0) NOT NULL,
[VESSEL_CD] [varchar](10) NULL

) ON [PRIMARY]

INSERT INTO [NCV_BL]
VALUES ('16789928','CNYTN','EGPSD','HJPA','0112W','W', '1.000','CNYTN','EGPSW','200802')
INSERT INTO [NCV_BL]
VALUES ('16823117','USORF','INNSA','ALSAB','0076E','E','1.000',,'USCHI', 'INTKD','200908')
INSERT INTO [NCV_BL]
VALUES ('16467030','CNSHA','NLRTM','COSHEL','037W','W', '2.000','CNSHA','NLRTM','201009')
INSERT INTO [NCV_BL]
VALUES ('12381896','DEHAM','CNSHA','YMUNT','0030E','E','2.000','DEHAM','CNSHA','201101')

INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('669246', '1230118')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('761970', '1255321')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('696131', '1261443')
INSERT INTO [MG_VSLVOY_PORT_CONTROL]
VALUES('696183', '1261585')

INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1261585','GBFXT')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1261443','CNNSA')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1230118','CNNSA')
INSERT INTO [MG_VSLVOY_SCHEDULE]
VALUES('1255321','INNSA')

INSERT INTO [MG_VSLVOY_HEADER]
VALUES('696183','YMUNT')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('696131','COSHEL')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('669246','HJPA')
INSERT INTO [MG_VSLVOY_HEADER]
VALUES('761970','ALSAB')

INSERT INTO [MG_BILL_OF_LADING]
VALUES('12381896','YMUNT')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16467030','COSHEL')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16789928','HJPA')
INSERT INTO [MG_BILL_OF_LADING]
VALUES('16823117','ALSAB')
--------------------------------

I have also developed some code (still incomplete and in bold) for the issue I have:

select

SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2008' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO08,
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2009' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO09,
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2010' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO10,
SUM(case when PLACE_OF_RECEIPT like 'UA%' and Left(saisan_month,4) = '2011' and bol.POD_LOCATION_CD NOT IN ('GBBEL','IEDUB') then TEU end) as UATO11

FROM NCV_BL NB
INNER JOIN MiniGapp..MG_BILL_OF_LADING BOL
ON NB.BL_ID = BOL.BL_ID

LEFT JOIN
(
SELECT distinct
MAX(NCV.BL_ID) AS BL_ID,
SUM(case when Left(saisan_month,4) = '2011' and PLACE_OF_DELIVERY like 'GB%' THEN TEU END) AS GB_TS08,
SUM(case when Left(saisan_month,4) = '2011' and PLACE_OF_DELIVERY like 'DE%' THEN TEU END) AS DE_TS08

FROM NCV_BL NCV

INNER JOIN MG_BILL_OF_LADING AS bol ON bol.BL_ID = NCV.BL_ID
INNER JOIN MG_VSLVOY_HEADER AS vh ON bol.VESSEL_CD = vh.VESSEL_CD

INNER JOIN MG_VSLVOY_PORT_CONTROL AS vpc ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
INNER JOIN MG_VSLVOY_SCHEDULE AS vs ON vpc.VSLVOY_SCHEDULE_ID = vs.VSLVOY_SCHEDULE_ID

WHERE NCV.POL_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE) or NCV.POD_LOCATION_CD NOT IN (Select PORT_CD FROM MG_VSLVOY_SCHEDULE)

) NA

ON NB.BL_ID = NA.BL_ID

can someone please give any inputs on this.

Thanks,

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources