October 30, 2012 at 3:18 am
Hi,
I had posted the query earlier but did not get any response so I have no option but to post it again. 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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply