How do I count vessels with certain criteria.

[n1ef5ng1]

Please see attached below.

Below is the table to describe more on our data structure

If any VES_NAME & VOYAGE with SEQ = 1 and BOX + CARGO > 0 will count as 1 only, even it has SEQ 2.

For VES_NAME & VOYAGE with SEQ = 1 only and BOX + CARGO = 0 will no count. (1 record only)

For VES_NAME & VOYAGE with SEQ = 1 and BOX + CARGO = 0, but in SEQ = 2 or more, if the Sum(BOX + CARGO) > 0, will have only count as 1 also.

Mean: Count is distinct by VES_NAME & VOYAGE, and Sum(BOX + CARGO) > 0. 

Hence based on our qvw below, is there any way we can get the answer on the text box

YYYYMM	VES_NAME	VOYAGE	SEQ	ATB	GATEWAY	V_TYPE	BOX	CARGO	REMARKS
201501	VESNAME 01	VOY01	1	07/01/2015 10:30:00	X	BARGE	0	0	No count, only Seq 1 and Box + Cargo is 0
201501	VESNAME 02	VOY02	1	23/01/2015 08:55:00	X	TYPE1	0	0	1 Count for VESSEL, same VES_NAME and VOYAGE, add SEQ 1 and 2 Box + Cargo > 0
201501	VESNAME 02	VOY02	2	23/01/2015 19:50:00	X	TYPE1	500	0
201501	BARGE01	01	1	10/01/2015 10:00:00	X	BARGE		400	1 count for BARGE, Box + Cargo > 0
201501	VESNAME 91	VOY91	1	25/01/2015 23:40:00	X	TYPE3	1000	0	1 count for VESSEL, Box + Cargo > 0
201502	VESNAME 03	VOY03	1	16/02/2015 00:10:00	X	TYPE1	0	0	1 Count for VESSEL, same VES_NAME and VOYAGE, add SEQ 1 and 2 Box + Cargo > 0
201502	VESNAME 03	VOY03	2	16/02/2015 01:35:00	X	TYPE1	600	0
201502	VESNAME 01	VOY1502	1	02/02/2015 08:55:00	X	BARGE	0	0	No count, only Seq 1 and Box + Cargo is 0
201502	VESNAME 04	VOY04	1	19/02/2015 08:15:00	X	TYPE3	0	0	1 Count for VESSEL, same VES_NAME and VOYAGE, add SEQ 1 and 2 Box + Cargo > 0
201502	VESNAME 04	VOY04	2	19/02/2015 09:10:00	X	TYPE3	0	700
201502	VESNAME 05	VOY05	1	28/02/2015 22:35:00	X	TYPE2	0	0	No count, only Seq 1 and Box + Cargo is 0
201502	VESNAME 06	VOY06	1	09/02/2015 22:50:00	X	TYPE2	0	0	1 Count for VESSEL, same VES_NAME and VOYAGE, add SEQ 1 and 2 Box + Cargo > 0
201502	VESNAME 06	VOY06	2	09/02/2015 23:05:00	X	TYPE2	800	0
201502	VESNAME 92	VOY92	1	28/02/2015 07:50:00	X	TYPE4	2000	0	1 count for VESSEL, Box + Cargo > 0
201502	BARGE01	02	1	10/01/2015 10:00:00	X	BARGE	800	0	1 count for VESSEL, Box + Cargo > 0

[#2]

Bennn, Your requirement is clear. Can you explain in brief.

[n1ef5ng1]

Hi,

our records will show vessels that berth on our terminals. However we will have two records of the same vessel is the vessel will to berth Twice. Hence our counting of vessels should show as 1 rather than 2 with the same vessel voyage as the primary key identifier.