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'
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'