SELECT       VESSEL_NAME,
 MG_VSLVOY_HEADER.VOYAGE_NUM,

       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,
             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)))

quote:

---

Originally posted by gbritton

Think about it for a minute. If you GROUP BY (VESSEL_NAME + VOYAGE_NUM) that means that SQL has to aggregate the other columns somehow. That is, there is likely to be more than one row in the result set (before grouping) for each VESSEL_NAME + VOYAGE_NUM. So, if there are two rows, e.g. which one should SQL choose? That's why you need to either aggregate or group by.

BTW GROUP BY VESSEL_NAME, VOYAGE_NUM (no '+') is more natural and easier to read and will work the same.

---

quote:

---

Originally posted by gbritton

Think about it for a minute. If you GROUP BY (VESSEL_NAME + VOYAGE_NUM) that means that SQL has to aggregate the other columns somehow. That is, there is likely to be more than one row in the result set (before grouping) for each VESSEL_NAME + VOYAGE_NUM. So, if there are two rows, e.g. which one should SQL choose? That's why you need to either aggregate or group by.

BTW GROUP BY VESSEL_NAME, VOYAGE_NUM (no '+') is more natural and easier to read and will work the same.

---