IF OBJECT_ID('tempdb..#temp_fuelupevents') IS NOT NULL
    DROP TABLE #temp_fuelupevents

SELECT
Distance,
DateTime,
DriverName,
TotalFuelUsed,
DeviceId,
devicename,
(
CASE
    WHEN
        LEN(SUBSTRING(devicename,CHARINDEX('-',devicename)+1,LEN(devicename))) = 2
    THEN
        '00' + SUBSTRING(devicename,CHARINDEX('-',devicename)+1,LEN(devicename))
        + '-'
        + SUBSTRING(devicename,0,CHARINDEX('-',devicename))

    WHEN
        LEN(SUBSTRING(devicename,CHARINDEX('-',devicename)+1,LEN(devicename))) = 3
    THEN
        '0' + SUBSTRING(devicename,CHARINDEX('-',devicename)+1,LEN(devicename))
        + '-'
        + SUBSTRING(devicename,0,CHARINDEX('-',devicename))

    ELSE
        SUBSTRING(devicename,CHARINDEX('-',devicename)+1,LEN(devicename))
        + '-'
        + SUBSTRING(devicename,0,CHARINDEX('-',devicename))
END) AS reversed
INTO #temp_fuelupevents
FROM WPW_GPS.dbo.View_FuelUpEvents
WHERE TotalFuelUsed <> ' ' AND distance <> ' '

SELECT
g.GroupName,
fue.Distance/1000 AS distance,
fue.DateTime,
fue.DriverName,
fue.TotalFuelUsed,
(fue.TotalFuelUsed/fue.Distance)*100000 AS Economy,
li.Equipment,
li.STQty,
fue.devicename,
fue.reversed

FROM #temp_fuelupevents fue

LEFT OUTER JOIN
WPW_GPS.dbo.DeviceGroup dg
ON fue.DeviceId = dg.DeviceId

LEFT OUTER JOIN
WPW_GPS.dbo.Groups g
ON dg.GroupId = g.GroupId

LEFT OUTER JOIN
Workticket.dbo.LabourItems li
ON li.equipment = fue.reversed

WHERE TotalFuelUsed <> ' ' AND distance <> ' '