SQL getting MAX Number from many inner joins plz help

Ninnad Jagtap

Active member
Joined
Apr 12, 2005
Messages
35
Programming Experience
Beginner
hi

i want to get the max number from the group of tables which is in the inner join sending u query i have written i want to get the max sequence number

SELECT distinct CONVERT(VARCHAR(15),GAP.GAPGatePassDate,101) as [Date],CONVERT(VARCHAR(15),
GAP.GAPGatePassDate,108) as [Time],VCO.VCOContainerNo as [Container No],CON.CONSizeCode as [Size],
CON.CONTypeCode as [Type],CMO.CMOEmptyStatus as [Status], CMO.CMORepairStatus as [Snd/Dmg],
CMO.CMOLinerCode as [Liner],COCustomerProfile.CUPName as [Agent],GAP.GAPIgmNo as [IGM No],GAP.GAPItemNo as [Item No],
VEH.VEHTransporterName as [Transporter],GAP.GAPOriginDestnName as [Ex-Where],VEH.VEHVehicleNo as [Vehicle No],
CMO.CMOAgentSealNo as [Agent Seal],CMO.CMOCustomsSealNo as [Custom Seal], GAP.GAPRemarks as [Remark],(CMO.CMOSequenceNo )
FROM GAVehicleContainer VCO
INNER JOIN GAVehicle VEH ON VCO.VCOGatePassNo = VEH.VEHGatePassNo AND VCO.VCOVehicleNo = VEH.VEHVehicleNo
INNER JOIN GAGatePass GAP ON VEH.VEHGatePassNo = GAP.GAPGatePassNo
INNER JOIN COContainerMovement CMO ON GAP.GAPGatePassDate = CMO.CMOInDate AND VCO.VCOContainerNo = CMO.CMOContainerNo
--CMO.CMOSequenceNo = (select max(CMO.CMOSequenceNo) from CoContainerMovement where CMO.CMOMoveStatus = 'GI')
INNER JOIN COContainer CON ON VCO.VCOContainerNo = CON.CONContainerNo
LEFT OUTER JOIN COCustomerProfile ON GAP.GAPChaCode = COCustomerProfile.CUPCustomerCode
WHERE (GAP.GAPInOut = 'I') AND (CMO.CMODeleteFlag = 'N') AND (VCO.VCODeleteFlag = 'N') AND (GAP.GAPDeleteFlag = 'N')
AND (CMO.CMOMoveStatus = 'GI') AND (CMO.CMOLinerCode <> 'MSK') AND (CMO.CMOLinerCode <> 'SCL')
AND (VEH.VEHDeleteFlag = 'N') AND GAP.GAPGatePassDate between '01-Jan-2005 8:00:00 AM' and '06-Jan-2005 8:00:00 AM'
 

ryager

Member
Joined
May 3, 2005
Messages
8
Location
Florida
Programming Experience
10+
I think I can do that.

Hmmm... Lets See....

SELECT distinct
CONVERT(VARCHAR(15),GAP.GAPGatePassDate,101) as [Date],
CONVERT(VARCHAR(15),GAP.GAPGatePassDate,108) as [Time],
VCO.VCOContainerNo as [Container No],
CON.CONSizeCode as [Size],
CON.CONTypeCode as [Type],
CMO.CMOEmptyStatus as [Status],
CMO.CMORepairStatus as [Snd/Dmg],
CMO.CMOLinerCode as [Liner],
COCustomerProfile.CUPName as [Agent],
GAP.GAPIgmNo as [IGM No],
GAP.GAPItemNo as [Item No],
VEH.VEHTransporterName as [Transporter],
GAP.GAPOriginDestnName as [Ex-Where],
VEH.VEHVehicleNo as [Vehicle No],
CMO.CMOAgentSealNo as [Agent Seal],
CMO.CMOCustomsSealNo as [Custom Seal],
GAP.GAPRemarks as [Remark],
CMO.CMOSequenceNo --- (CMO.CMOSequenceNo )
FROM
GAVehicleContainer VCO
INNER JOIN GAVehicle VEH
ON VCO.VCOGatePassNo = VEH.VEHGatePassNo AND
VCO.VCOVehicleNo = VEH.VEHVehicleNo
INNER JOIN GAGatePass GAP
ON VEH.VEHGatePassNo = GAP.GAPGatePassNo
INNER JOIN COContainerMovement CMO
ON GAP.GAPGatePassDate = CMO.CMOInDate AND
VCO.VCOContainerNo = CMO.CMOContainerNo AND
CMO.CMOSequenceNo in
(select
max(CMO.CMOSequenceNo)
from
CoContainerMovement
where
CMO.CMOMoveStatus = 'GI')
INNER JOIN COContainer CON
ON VCO.VCOContainerNo = CON.CONContainerNo
LEFT OUTER JOIN COCustomerProfile
ON GAP.GAPChaCode = COCustomerProfile.CUPCustomerCode
WHERE
(GAP.GAPInOut = 'I') AND
(CMO.CMODeleteFlag = 'N') AND
(VCO.VCODeleteFlag = 'N') AND
(GAP.GAPDeleteFlag = 'N') AND
(CMO.CMOMoveStatus = 'GI') AND
(CMO.CMOLinerCode <> 'MSK') AND
(CMO.CMOLinerCode <> 'SCL') AND
(VEH.VEHDeleteFlag = 'N') AND
GAP.GAPGatePassDate
between '01-Jan-2005 8:00:00 AM' and '06-Jan-2005 8:00:00 AM'




Yeah that looks about right. :)
 
Top Bottom