February 10, 2015 at 9:10 am
Hi,
I had to use the MAX function in my query for many columns but by doing that I get incorrect resultset.
Is there any way I can replace the below query with another one and get rid of the MAX function ?
SELECT VESSEL_NAME ,
max(MG_VSLVOY_HEADER.DELETED_FLG) as header_deletedflag, max(MG_VSLVOY_PORT_CONTROL.DELETED_FLG) as portcontrol_deletedflag,
max(MG_VSLVOY_SCHEDULE.DELETED_FLG) as SCHEDULE_deletedflag,
MAX(NATIONALITY_CD) AS NATIONALITY_CD , MAX(PORT_CD) AS PORT_CD,
MAX(case when CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT , 120) = CONVERT(VARCHAR(10), DATEADD(MM, 3, GETDATE()), 120) and PORT_CD IN ('BEZEE', 'GBSOU') or CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.CREATE_DT , 120) = CONVERT(VARCHAR(10), GETDATE() - 1, 120) and PORT_CD IN ('BEZEE', 'GBSOU') then 'N' else '' end +
case when CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT, 120) = CONVERT(VARCHAR(10), GETDATE() - 1, 120) and PORT_CD in ('ITLIV', 'DEBRV', 'BEZEE','GBSOU','MXVER','USGLS','USJAX','USBAL','USCHS','USSSI') then 'C' else '' END) as DateStatus,
MAX(MG_VSLVOY_HEADER.LEG_CD) AS LEG_CD, MG_VSLVOY_HEADER.VOYAGE_NUM ,MAX(PORT_SEQUENCE_NBR) AS PORT_SEQUENCE_NBR, MAX(ARRIVAL_SCHEDULE_DT) AS ARRIVAL_SCHEDULE_DT, MAX(ARRIVAL_ESTIMATE_DT) AS ARRIVAL_ESTIMATE_DT,
MAX(ARRIVAL_STATUS_CD) AS ARRIVAL_STATUS_CD, MAX(ARRIVAL_ACTUAL_DT) AS ARRIVAL_ACTUAL_DT, MAX(DEPART_ESTIMATE_DT) AS DEPART_ESTIMATE_DT,
MAX(DEPART_ACTUAL_DT) AS DEPART_ACTUAL_DT, MAX(DEPART_STATUS_CD) AS DEPART_STATUS_CD,
MAX(Case When PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end) as ITLIV_E,
MAX(Case When PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end) as BEZEE_E,
MAX(Case When PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end) as GBSOU_E
FROM MG_VESSEL_PARTICULAR
INNER JOIN MG_VSLVOY_HEADER
ON MG_VESSEL_PARTICULAR.VESSEL_CD = MG_VSLVOY_HEADER.VESSEL_CD
AND MG_VSLVOY_HEADER.LEG_CD = 'A'
INNER JOIN MG_VSLVOY_PORT_CONTROL
ON MG_VSLVOY_HEADER.VSLVOY_HEADER_ID = MG_VSLVOY_PORT_CONTROL.VSLVOY_HEADER_ID
AND PORT_SEQUENCE_NBR = 1
INNER JOIN MG_VSLVOY_SCHEDULE
ON MG_VSLVOY_PORT_CONTROL.VSLVOY_SCHEDULE_ID = MG_VSLVOY_SCHEDULE.VSLVOY_SCHEDULE_ID
GROUP BY VESSEL_NAME, VOYAGE_NUM
ORDER BY GBSOU_E , BEZEE_E
Thanks.
February 10, 2015 at 9:23 am
We need much more information to be able to answer.
Pleast post:
* table definitions as CREATE TABLE scripts
* some sample data as INSERT INTO scripts
* the desired output based on sample data
* why your current output is wrong
-- Gianluca Sartori
February 10, 2015 at 10:47 am
educated guess, since the all those values dates of arrivals are tied to the ship itself, it's not appropriate to arbitrarily grab the max.
you need to use row number featuring partition by, and filter to jus the first match per partition.
SELECT * FROM (
SELECT row_number()
OVER(
partition BY VESSEL_NAME, VOYAGE_NUM
ORDER BY ARRIVAL_ACTUAL_DT) AS RW,
VESSEL_NAME,
MG_VSLVOY_HEADER.DELETED_FLG AS header_deletedflag,
MG_VSLVOY_PORT_CONTROL.DELETED_FLG AS portcontrol_deletedflag,
MG_VSLVOY_SCHEDULE.DELETED_FLG AS SCHEDULE_deletedflag,
NATIONALITY_CD AS NATIONALITY_CD,
PORT_CD AS PORT_CD,
CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT, 120) = CONVERT(VARCHAR(10), Dateadd(MM, 3, Getdate()), 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
OR CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.CREATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
THEN 'N'
ELSE ''
END
+ CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('ITLIV', 'DEBRV', 'BEZEE', 'GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
THEN 'C'
ELSE ''
END AS DateStatus,
MG_VSLVOY_HEADER.LEG_CD AS LEG_CD,
MG_VSLVOY_HEADER.VOYAGE_NUM,
PORT_SEQUENCE_NBR AS PORT_SEQUENCE_NBR,
ARRIVAL_SCHEDULE_DT AS ARRIVAL_SCHEDULE_DT,
ARRIVAL_ESTIMATE_DT AS ARRIVAL_ESTIMATE_DT,
ARRIVAL_STATUS_CD AS ARRIVAL_STATUS_CD,
ARRIVAL_ACTUAL_DT AS ARRIVAL_ACTUAL_DT,
DEPART_ESTIMATE_DT AS DEPART_ESTIMATE_DT,
DEPART_ACTUAL_DT AS DEPART_ACTUAL_DT,
DEPART_STATUS_CD AS DEPART_STATUS_CD,
CASE
WHEN PORT_CD = 'ITLIV'
AND ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
WHEN PORT_CD = 'ITLIV'
AND ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
WHEN PORT_CD = 'ITLIV'
AND ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT
END AS ITLIV_E,
CASE
WHEN PORT_CD = 'BEZEE'
AND ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
WHEN PORT_CD = 'BEZEE'
AND ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
WHEN PORT_CD = 'BEZEE'
AND ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT
END AS BEZEE_E,
CASE
WHEN PORT_CD = 'GBSOU'
AND ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
WHEN PORT_CD = 'GBSOU'
AND ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
WHEN PORT_CD = 'GBSOU'
AND ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT
END AS GBSOU_E
FROM MG_VESSEL_PARTICULAR
INNER JOIN MG_VSLVOY_HEADER
ON MG_VESSEL_PARTICULAR.VESSEL_CD = MG_VSLVOY_HEADER.VESSEL_CD
AND MG_VSLVOY_HEADER.LEG_CD = 'A'
INNER JOIN MG_VSLVOY_PORT_CONTROL
ON MG_VSLVOY_HEADER.VSLVOY_HEADER_ID = MG_VSLVOY_PORT_CONTROL.VSLVOY_HEADER_ID
AND PORT_SEQUENCE_NBR = 1
INNER JOIN MG_VSLVOY_SCHEDULE
ON MG_VSLVOY_PORT_CONTROL.VSLVOY_SCHEDULE_ID = MG_VSLVOY_SCHEDULE.VSLVOY_SCHEDULE_ID
) MyAlias
WHERE RW = 1
Lowell
February 13, 2015 at 8:10 am
Lowell (2/10/2015)
educated guess, since the all those values dates of arrivals are tied to the ship itself, it's not appropriate to arbitrarily grab the max.you need to use row number featuring partition by, and filter to jus the first match per partition.
Thanks a lot for the code. However, I am still not getting the correct data. I just found that if I can add the GROUP BY statement as follows then it brings in the correct resultset. I need to add the group by with the query below without using MAX function for other columns or grouping the rest of the columns.
GROUP BY (VESSEL_NAME + VOYAGE_NUM)
SELECT VESSEL_NAME,
MG_VSLVOY_HEADER.DELETED_FLG AS header_deletedflag,
MG_VSLVOY_PORT_CONTROL.DELETED_FLG AS portcontrol_deletedflag,
MG_VSLVOY_SCHEDULE.DELETED_FLG AS SCHEDULE_deletedflag,
NATIONALITY_CD AS NATIONALITY_CD,
PORT_CD AS PORT_CD,
CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT, 120) = CONVERT(VARCHAR(10), Dateadd(MM, 3, Getdate()), 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
OR CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.CREATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
THEN 'N'
ELSE ''
END
+ CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('ITLIV', 'DEBRV', 'BEZEE', 'GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
THEN 'C'
ELSE ''
END AS DateStatus,
MG_VSLVOY_HEADER.LEG_CD AS LEG_CD,
MG_VSLVOY_HEADER.VOYAGE_NUM,
PORT_SEQUENCE_NBR AS PORT_SEQUENCE_NBR,
ARRIVAL_SCHEDULE_DT AS ARRIVAL_SCHEDULE_DT,
ARRIVAL_ESTIMATE_DT AS ARRIVAL_ESTIMATE_DT,
ARRIVAL_STATUS_CD AS ARRIVAL_STATUS_CD,
ARRIVAL_ACTUAL_DT AS ARRIVAL_ACTUAL_DT,
DEPART_ESTIMATE_DT AS DEPART_ESTIMATE_DT,
DEPART_ACTUAL_DT AS DEPART_ACTUAL_DT,
DEPART_STATUS_CD AS DEPART_STATUS_CD,
Case When PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as ITLIV_E,
Case When PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as DEBRV_E,
Case When PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as BEZEE_E,
Case When PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as GBSOU_E,
Case When PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as MXVER_E,
Case When PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USGLS_E,
Case When PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USJAX_E,
Case When PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USBAL_E,
Case When PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USCHS_E,
Case When PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USSSI_E,
Case When PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as ITLIV_G,
Case When PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as DEBRV_G,
Case When PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as BEZEE_G,
Case When PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as GBSOU_G,
Case When PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as MXVER_G,
Case When PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USGLS_G,
Case When PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USJAX_G,
Case When PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USBAL_G,
Case When PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USCHS_G,
Case When PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USSSI_G
FROM MG_VESSEL_PARTICULAR
INNER JOIN MG_VSLVOY_HEADER
ON MG_VESSEL_PARTICULAR.VESSEL_CD = MG_VSLVOY_HEADER.VESSEL_CD
AND MG_VSLVOY_HEADER.LEG_CD = 'A'
INNER JOIN MG_VSLVOY_PORT_CONTROL
ON MG_VSLVOY_HEADER.VSLVOY_HEADER_ID = MG_VSLVOY_PORT_CONTROL.VSLVOY_HEADER_ID
AND PORT_SEQUENCE_NBR = 1
INNER JOIN MG_VSLVOY_SCHEDULE
ON MG_VSLVOY_PORT_CONTROL.VSLVOY_SCHEDULE_ID = MG_VSLVOY_SCHEDULE.VSLVOY_SCHEDULE_ID
where MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VSLVOY_HEADER.SERVICE_CD IN ('EBTAS', 'NCTAS')
AND (PORT_CD IN ('ITLIV', 'DEBRV','MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS') AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())
OR (PORT_CD = 'USSSI' AND ARRIVAL_STATUS_CD <> 'A' AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE()) OR (PORT_CD IN ('BEZEE', 'GBSOU')
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE()))
AND ARRIVAL_STATUS_CD IN ('E','A','S') AND (ARRIVAL_ESTIMATE_DT IS NOT NULL OR ARRIVAL_ACTUAL_DT IS NOT NULL OR ARRIVAL_SCHEDULE_DT IS NOT NULL)))
Thanks.
February 16, 2015 at 5:43 am
pwalter83 (2/13/2015)
Lowell (2/10/2015)
educated guess, since the all those values dates of arrivals are tied to the ship itself, it's not appropriate to arbitrarily grab the max.you need to use row number featuring partition by, and filter to jus the first match per partition.
Thanks a lot for the code. However, I am still not getting the correct data. I just found that if I can add the GROUP BY statement as follows then it brings in the correct resultset. I need to add the group by with the query below without using MAX function for other columns or grouping the rest of the columns.
GROUP BY (VESSEL_NAME + VOYAGE_NUM)
SELECT VESSEL_NAME,
MG_VSLVOY_HEADER.DELETED_FLG AS header_deletedflag,
MG_VSLVOY_PORT_CONTROL.DELETED_FLG AS portcontrol_deletedflag,
MG_VSLVOY_SCHEDULE.DELETED_FLG AS SCHEDULE_deletedflag,
NATIONALITY_CD AS NATIONALITY_CD,
PORT_CD AS PORT_CD,
CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT, 120) = CONVERT(VARCHAR(10), Dateadd(MM, 3, Getdate()), 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
OR CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.CREATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
THEN 'N'
ELSE ''
END
+ CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('ITLIV', 'DEBRV', 'BEZEE', 'GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
THEN 'C'
ELSE ''
END AS DateStatus,
MG_VSLVOY_HEADER.LEG_CD AS LEG_CD,
MG_VSLVOY_HEADER.VOYAGE_NUM,
PORT_SEQUENCE_NBR AS PORT_SEQUENCE_NBR,
ARRIVAL_SCHEDULE_DT AS ARRIVAL_SCHEDULE_DT,
ARRIVAL_ESTIMATE_DT AS ARRIVAL_ESTIMATE_DT,
ARRIVAL_STATUS_CD AS ARRIVAL_STATUS_CD,
ARRIVAL_ACTUAL_DT AS ARRIVAL_ACTUAL_DT,
DEPART_ESTIMATE_DT AS DEPART_ESTIMATE_DT,
DEPART_ACTUAL_DT AS DEPART_ACTUAL_DT,
DEPART_STATUS_CD AS DEPART_STATUS_CD,
Case When PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as ITLIV_E,
Case When PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as DEBRV_E,
Case When PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as BEZEE_E,
Case When PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as GBSOU_E,
Case When PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as MXVER_E,
Case When PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USGLS_E,
Case When PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USJAX_E,
Case When PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USBAL_E,
Case When PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USCHS_E,
Case When PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USSSI_E,
Case When PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as ITLIV_G,
Case When PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as DEBRV_G,
Case When PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as BEZEE_G,
Case When PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as GBSOU_G,
Case When PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as MXVER_G,
Case When PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USGLS_G,
Case When PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USJAX_G,
Case When PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USBAL_G,
Case When PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USCHS_G,
Case When PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USSSI_G
FROM MG_VESSEL_PARTICULAR
INNER JOIN MG_VSLVOY_HEADER
ON MG_VESSEL_PARTICULAR.VESSEL_CD = MG_VSLVOY_HEADER.VESSEL_CD
AND MG_VSLVOY_HEADER.LEG_CD = 'A'
INNER JOIN MG_VSLVOY_PORT_CONTROL
ON MG_VSLVOY_HEADER.VSLVOY_HEADER_ID = MG_VSLVOY_PORT_CONTROL.VSLVOY_HEADER_ID
AND PORT_SEQUENCE_NBR = 1
INNER JOIN MG_VSLVOY_SCHEDULE
ON MG_VSLVOY_PORT_CONTROL.VSLVOY_SCHEDULE_ID = MG_VSLVOY_SCHEDULE.VSLVOY_SCHEDULE_ID
where MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VSLVOY_HEADER.SERVICE_CD IN ('EBTAS', 'NCTAS')
AND (PORT_CD IN ('ITLIV', 'DEBRV','MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS') AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())
OR (PORT_CD = 'USSSI' AND ARRIVAL_STATUS_CD <> 'A' AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE()) OR (PORT_CD IN ('BEZEE', 'GBSOU')
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE()))
AND ARRIVAL_STATUS_CD IN ('E','A','S') AND (ARRIVAL_ESTIMATE_DT IS NOT NULL OR ARRIVAL_ACTUAL_DT IS NOT NULL OR ARRIVAL_SCHEDULE_DT IS NOT NULL)))
Thanks.
Can anyone help on this please ? Thanks.
February 16, 2015 at 5:45 am
Table definitions (as CREATE TABLE statements), sample data (as INSERT statements) and expected results please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2015 at 6:06 am
GilaMonster (2/16/2015)
Table definitions (as CREATE TABLE statements), sample data (as INSERT statements) and expected results please.
I just want to find a way to group using 2 columns (without the need to group other columns) and without using MAX function.
This just requires a change of the code I have mentioned above and I don't think any table def or data is required. I am already clear on my requirements. Thanks.
February 16, 2015 at 6:16 am
If you don't want a tested solution, cool, your choice.
Look at Row_Number and the partition by clause, you can then filter on that value to get just the highest. Should do what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2015 at 6:20 am
GilaMonster (2/16/2015)
If you don't want a tested solution, cool, your choice.Look at Row_Number and the partition by clause, you can then filter on that value to get just the highest. Should do what you want.
Thanks, I understand what you are saying but I know through testing that using group by on 2 columns will work.
Could you please tell how to use the Row_number and partition by clause in the query ? Is there no other way of achieving this ?
Thanks.
February 16, 2015 at 6:24 am
You have the advantage of me then, since I can't test.
Lowell's query is much what I would write if I was writing a query blind (no tables, no data) with the requirement you gave. Play with that, it should be close to what you want. If it isn't doing what you need, we're going to need more information to go any further.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2015 at 4:57 am
I agree with your Joe, but I'm under the impression that the OP cannot change the schema definition and is working on a very limited requirement.
While I find your suggestions sensible, I suppose they're out of scope here.
-- Gianluca Sartori
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply