SELECT

 --AREA SOLD TO
  [SOLDTOCODE]  AS [SOLDTOCODE],   --nvarchar(10)
  [SOLDTONAME]  AS [SOLDTONAME],   --nvarchar(40)
  LEFT
  (
   [dbo].[GetCountryName]
   (
    [SOLDTOCOUNTRY],
    [ROUTE],
    [CARRIER],
    [SOLDTOCODE]
   ),
   2
  )     AS [SOLDTOCOUNTRY],   --nvarchar(3)

 --AREA SHIP TO
  [SHIPTOCODE]  AS [SHIPTOCODE],   --nvarchar(10)
  [SHIPTONAME]  AS [SHIPTONAME],   --nvarchar(40)
  [SHIPTOPOSTALCODE] AS [SHIPTOPOSTAL],   --nvarchar(10)
  [SHIPTOCITY]  AS [SHIPTOCITY],   --nvarchar(40)
  LEFT
  (
   [dbo].[GetCountryName]
   (
    [SHIPTOCOUNTRY],
    [ROUTE],
    [CARRIER],
    [SHIPTOCODE]
   ),
   2
  )     AS [SHIPTOCOUNTRY],   --nvarchar(3)

 --AREA DATES
  CONVERT
  (
   DATE,
   [DATEADDED]
  )     AS [EDIDATE],    --smalldatetime
  CONVERT
  (
   DATE, 
   [SHIPDATE]
  )     AS [SHIPDATE],    --smalldatetime

 --AREA TRACKING
  LEFT
  (
   [SHIPREF],
   6
  )     AS [SHIPMENT],    --int
  [CASEID]   AS [CASEID],    --nvarchar(20)
  [HUID]    AS [HUID],     --nvarchar(20)
  [ROUTE]    AS [ROUTE],     --nvarchar(6)
  [CARRIER]   AS [CARRIER],    --nvarchar(10)

 --AREA DIMENSIONS
  [CASEWIDTH]   AS [WIDTH],     --decimal(13,3)
  [CASEHEIGHT]  AS [HEIGHT],    --decimal(13,3)
  [CASELENGTH]  AS [LENGTH],    --decimal(13,3)
  'CM'    AS [DIMUNIT],    --char(2)
  CONVERT
  (
   DECIMAL(18,3),
   ([CASEWIDTH] * [CASEHEIGHT] * [CASELENGTH])
  )     AS [VOLUME],    --decimal(18,3)
  'CM3'    AS [VOLUNIT],    --char(3)
  SUM([CASEWEIGHT]) AS [WEIGHT],    --decimal(13,3)
  'KG'    AS [WGTUNIT],    --char(2)

 --AREA MATERIAL SPECS
  [MATERIALEXTNUM] AS [MATLEXT],    --nvarchar(35)
  LEFT
  (
   [MATERIALHAZ],
   6
  )     AS [HAZMAT],    --nvarchar(18)
  [HTC]    AS [HTC],     --nvarchar(17)
  COUNT([HTC])  AS [HTCQTY],    --int

 --AREA OTHER DATA
  LEFT
  (
   [SAPSALESTYPE],
   4
  )     AS [SAPTYPE]    --nvarchar(4)

FROM
  [dbo].[SAPDATA]

WHERE 
  (YEAR([DATEADDED]) = YEAR(GetDate())
OR 
  YEAR([DATEADDED]) = YEAR(GetDate())-1)

GROUP BY
  [SOLDTOCODE],
  [SOLDTONAME],
  LEFT([dbo].[GetCountryName]([SOLDTOCOUNTRY],[ROUTE],[CARRIER],[SOLDTOCODE]),2),
  [SHIPTOCODE],
  [SHIPTONAME],
  [SHIPTOPOSTALCODE],
  [SHIPTOCITY],
  LEFT([dbo].[GetCountryName]([SHIPTOCOUNTRY],[ROUTE],[CARRIER],[SHIPTOCODE]),2),
  CONVERT(DATE,[DATEADDED]),
  CONVERT(DATE,[SHIPDATE]),
  LEFT([SHIPREF],6),
  [CASEID],
  [HUID],
  [ROUTE],
  [CARRIER],
  [CASEWIDTH],
  [CASEHEIGHT],
  [CASELENGTH],
  CONVERT(DECIMAL(18,3),([CASEWIDTH] * [CASEHEIGHT] * [CASELENGTH])),
  [MATERIALEXTNUM],
  LEFT([MATERIALHAZ],6),
  [HTC],
  LEFT([SAPSALESTYPE],4)
  
ORDER BY 
  CONVERT(DATE,[DATEADDED]);