SELECT tblWaybillOrder.[Waybill]                  AS [Waybill]
        ,tblWaybillMaster.[Status]                  AS [WaybillStatus]
        ,COUNT(tblPickingSlipSource.[SourceNumber]) AS [TotalSaleOrders]
        ,COUNT(tblPickingSlip.[PickingSlipNumber])  AS [TotalPickingSlips]
        ,tblWaybillShipperMaster.[Name]             AS [Carrier]
        ,tblWaybillMaster.[CreatedDate]             AS [CreatedDate]
        ,tblWaybillMaster.[CreatedBy]               AS [CreatedBy]
        ,tblWaybillMaster.[Vehicle]                 AS [Vehicle]
  FROM WarehouseCompanyTest.dbo.tblPickingSlip
  INNER JOIN WarehouseCompanyTest.dbo.tblPickingSlipSource
    ON tblPickingSlip.[PickingSlipNumber] = tblPickingSlipSource.[PickingSlipNumber]
  LEFT OUTER JOIN WarehouseCompanyTest.dbo.tblWaybillOrder
    ON tblWaybillOrder.[PickingSlipNumber] = tblPickingSlip.[PickingSlipNumber]
  LEFT OUTER JOIN WarehouseCompanyTest.dbo.tblWaybillMaster
    ON tblWaybillMaster.[Waybill] = tblWaybillOrder.[Waybill]
  LEFT OUTER JOIN WarehouseCompanyTest.dbo.tblWaybillShipperMaster
    ON tblWaybillMaster.[ShipperId] = tblWaybillShipperMaster.[Id]
  WHERE tblPickingSlip.[Status] NOT IN ('CANCELED', 'COMPLETE')
    AND tblWaybillMaster.[Status] = 'CREATED'
    AND tblWaybillOrder.[Selected] = '1'
  GROUP BY tblWaybillOrder.[Waybill]
          ,tblWaybillMaster.[Status]
          ,tblWaybillMaster.[ShipperId]
          ,tblWaybillShipperMaster.[Name]
          ,tblWaybillMaster.[CreatedDate]
          ,tblWaybillMaster.[CreatedBy]
          ,tblWaybillMaster.[Vehicle];

select WBO.Waybill, WBO.SalesOrder, iSNULL(WBO.DispatchNote,'Await'+ WBO.SalesOrder) as DispatchNote, isnull(STO.ShippingTrackId,0) as ShippingTrackId , 
isnull(P.PalletNumber,0) as PalletNumber, isnull(P.ActualMass,0) as ActualMass, isNull(P.TheoreticalMass,0) as TheoreticalMass, 
ISNULL(P.[Length],0) as [Length] ,isnull( P.Width,0) as [Width], isnull(P.Height,0) as [Height],
 (cast(ISNULL(P.[Length],0) as decimal(18,6))*cast(isnull( P.Width,0) as decimal(18,6))*cast(isnull(P.Height,0) as decimal(18,6))/5000) as [Volumetric Weight],
 isnull(P.ShippingTrackIdType,0) as ShippingTrackIDType
from tblWaybillOrder WBO with (nolock)
left join tblShippingTrackIdOrder STO with(nolock) on STO.WaybillId=WBO.Waybill and STO.SalesOrder=WBO.SalesOrder
left join tblPallet P with (nolock) on P.PalletNumber=STO.ShippingTrackId
where WBO.Selected='1' and iSNULL(STO.Selected,1)='1' --and WBO.Waybill=@Waybill
group by  WBO.Waybill, WBO.SalesOrder, WBO.DispatchNote, iSNULL(WBO.DispatchNote,'Pending'+ WBO.SalesOrder), isnull(STO.ShippingTrackId,0) , 
isnull(P.PalletNumber,0), isnull(P.ActualMass,0), isNull(P.TheoreticalMass,0) , 
ISNULL(P.[Length],0),isnull( P.Width,0), isnull(P.Height,0) ,
 (cast(ISNULL(P.[Length],0) as decimal(18,6))*cast(isnull( P.Width,0) as decimal(18,6))*cast(isnull(P.Height,0) as decimal(18,6))/5000),
 isnull(P.ShippingTrackIdType,0)