Hi
I am interested in breaking the join that is currently arises for "VesselName" .
I have included attachment showing the table viewer and also included the respective script below. Your assictance would be greatelu appreciated
Thanks
Rohan
ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
ODBC CONNECT TO SSL36D;
/*
* Head Fixtures
*
*/
SELECT DISTINCT VH.ID HeadFixtureID, vh.VesselName,
Oown.SLCode 'Owner', Oown.Country 'Owner Country'
From VMHead AS VH WITH (NOLOCK)
JOIN OpFix AS own WITH (NOLOCK) ON own.ID = VH.TCFixture
JOIN Organisation AS Oown WITH (NOLOCK) ON Oown.ID = isnull(own.seller, own.SelPrincipal);
/*
* Voyages and Ports
* Link to Head Fixtures using HeadFixtureID
* This could be split in to two tables.
*/
SELECT DISTINCT v.id VoyageID, vp.ID PortID, v.HeadFixture HeadFixtureID, V.VesselName, V.VoyageNo, V.Status,
VP.Sequence, VP.Port, vp2.Country 'Port Country',
VP.Arrived 'Arrived', VP.Sailed 'Departed', VP.ActivityDisplay 'Port Activity',
V.FixedBy, V.OperatedBy,
ISNULL(V.StartBallast, V.StartVoyageDate) StartVoyage,
Year(V.StartVoyageDate)AS Start_Voyage_Year,
Month(V.StartVoyageDate)AS Start_Voyage_Month,
ISNULL(V.RedeliveryDate, V.EndVoyageDate) EndVoyage,
V.Type VoyageType
FROM VMVoyage AS V WITH (NOLOCK)
JOIN VMVoyagePort AS VP WITH (NOLOCK) ON VP.Voyage = V.ID
JOIN VesselPort AS VP2 WITH (NOLOCK) ON vp2.name = vp.Port
WHERE vp.DeleteFlag IS NULL
AND V.Category = 'FIXED';
/*
* Port Agens (DA's)
* Link to Voyage-Ports using Port ID
*/
SELECT DISTINCT dis.VMVoyagePort PortID,
dis.InvCurrency 'DA Currency',
dis.InvAmount 'DA Amount',
CASE DIS.InvType
WHEN 3 THEN 'Proforma'
WHEN 4 THEN 'Final'
ELSE 'Other'
END 'DAType', o.SLCode 'DA Agent',
dis.InvDueDate 'DA DueDate'
FROM DAInvSummary AS DIS WITH (NOLOCK)
JOIN Organisation AS O WITH (NOLOCK) ON o.id = dis.Supplier
WHERE isnull(dis.Active, 1) = 1
AND dis.ReplacedByInvSummary IS NULL ;
/*
* Cargo and charges
* Link to Voyage-Ports via Voyage ID
* This could be split in two, which would avoid the ISNULL where clause and LOJ.
*/
SELECT DISTINCT v.id VoyageID,
OVC.Type 'Cargo', OVC.ActNomMax 'MT',
Ochtr.SLCode 'Charterer', Ochtr.Country 'Charterer Country',
oc.Type 'Freight Charge Type',
oc.Curr 'Freight Charge Currency',
oc.Charge 'Freight Charge Amount',
oc.ExpectedDue 'Freight Charge Due Date',
oc.Status 'Freight Charge Status'
FROM VMVoyage AS V WITH (NOLOCK)
JOIN VMVoyageFixture AS VVF WITH (NOLOCK) ON VVF.VMVoyageNo = V.ID
JOIN OpFix AS chtr WITH (NOLOCK) ON chtr.ID = vvf.OpsFixture
LEFT OUTER JOIN OpCharge AS OC WITH (NOLOCK) ON chtr.id = oc.Fixture
JOIN Organisation AS Ochtr WITH (NOLOCK) ON Ochtr.ID = chtr.Buyer
JOIN OpVOYCargo AS OVC WITH (NOLOCK) ON OVC.Fixture = chtr.ID
WHERE ISNULL(oc.charge,1) <> 0;
/*
* Invoices
* Link to Voyage via VoaygeID
*/
SELECT DISTINCT v.id VoyageID,
oih.InvoiceNo,
oih.Header,
oig.Curr,
oih.Type,
oih.DueDate,
oih.ExpectedDueDate,
oih.InvoiceTotal,
oih.ReceivedTotal,
oih.Settled,
oih.Paid,
oih.Reference,
oih.PaidDate,
o.SLCode
FROM VMVoyage AS V WITH (NOLOCK)
JOIN VMVoyageFixture AS VVF WITH (NOLOCK) ON VVF.VMVoyageNo = V.ID
JOIN OpInvoiceHeader AS OIH WITH (NOLOCK) ON OIH.Fixture = vvf.OpsFixture
JOIN OpInvoiceGroup AS OIG WITH (NOLOCK) ON OIG.Fixture = OIH.Fixture
JOIN Organisation AS O WITH (NOLOCK) ON o.id = oih.Invoicee;
/*
* Bunkers
* Link to voyages via VoyageID
* Link to ports via PortID
*/
SELECT v.ID VoyageID, vp.ID PortID,
V.VesselName, V.VoyageNo, VP.Port, VVPBL.StemmedDate, VVPBL.Quality, VVPBL.DeliveryDate, VP.Arrived, VVPBL.Lifted, vvpbl.LiftingType,
VVPBS.SupplierFullName, VVPBL.Gross, VVPBL.ApprovedFlag, VVPBL.Gross * VVPBL.Lifted TotalUSD
FROM VMVoyage AS V WITH (NOLOCK)
JOIN VMVoyagePort AS VP WITH (NOLOCK) ON VP.Voyage = V.ID
JOIN VMVoyagePortBunkerSupplier AS VVPBS WITH (NOLOCK) ON VVPBS.VMVoyagePort = vp.id
JOIN VMVoyagePortBunkerLifting AS VVPBL WITH (NOLOCK) ON VVPBS.id = VVPBL.VMVoyagePortBunkerSupplier
WHERE VVPBL.Gross > 0
AND VVPBL.LiftingType NOT LIKE '* Survey';
