SELECT 
e.ID As TaskRunID,
CASE 
    WHEN e.TaskType = 1 THEN '--OUTBOUND--'
    WHEN e.TaskType = 2 THEN '--OUTBOUND--'
    WHEN ds.isTombStoned = 1 THEN ds.DataSourceName || ' (Deleted)'
    ELSE ds.DataSourceName
    END
AS DataSourceName,
CASE
    WHEN instr(ds.settings,'<ProviderTypeName>')>1 THEN substr(ds.Settings,instr(ds.Settings,'<ProviderTypeName>')+18,instr(ds.Settings,'</ProviderTypeName>')-(instr(ds.Settings,'<ProviderTypeName>')+18))
    WHEN instr(ds.Settings,'<ProviderName>')>1 THEN substr(ds.Settings,instr(ds.Settings,'<ProviderName>')+14,instr(ds.Settings,'</ProviderName>')-(instr(ds.Settings,'<ProviderName>')+14))
    ELSE 'N/A'
    END
AS DataSourceProvider,
ds.isTombStoned as DataSourceIsDeleted,
CASE 
    WHEN e.TaskType = 1 THEN 'DWH Table - '|| ta.TableName
    WHEN e.TaskType = 2 THEN 'DWH Table - '|| ta.TableName
    WHEN t.isTombStoned = 1 THEN t.TaskName || ' (Deleted)'
    ELSE t.TaskName
    END
AS TaskName,
t.isTombStoned as TaskIsDeleted,
CASE e.TaskType
    WHEN 0 THEN 'Source -> ODX'
    WHEN 1 THEN 'ODX -> DWH'
    WHEN 2 THEN 'Preview'
    WHEN 5 THEN 'Synchronize'  
    ELSE 'Other'
    END
AS TaskType,
CASE e.isScheduled
    WHEN 0 THEN e.UserName
    WHEN 1 THEN 'Schedule'
    END
AS StartedBy,
strftime('%Y-%m-%d %H:%M:%S',StartTime/10000000 - 62135596800,'unixepoch') StartTime,
strftime('%Y-%m-%d %H:%M:%S',EndTime/10000000 - 62135596800,'unixepoch') EndTime,
strftime('%H:%M:%S',(EndTime-StartTime)/10000000 - 62135596800,'unixepoch') Duration,
(strftime('%s',(EndTime)/10000000 - 62135596800,'unixepoch')-strftime('%s',(StartTime)/10000000 - 62135596800,'unixepoch'))/60.0 DurationInMinutes,



CASE e.State
    WHEN 4 Then 'Completed Successfully'
    WHEN 6 Then 'Completed with Errors'  
    WHEN 7 Then 'Failed'
    ELSE e.State
    END        
as Status,
CASE    
    WHEN l.Message IS NULL THEN '  ----  '
    ELSE l.Message
    END
AS [Details (Preview)]

FROM [ExecutionTasks] e

Left Join
    ( SELECT l.Id, l.ExecutionTaskId, l.Severity, l.message
        FROM ExecutionLogs l
        WHERE l.Id = 
          (SELECT min(Id) FROM ExecutionLogs l1 WHERE l.ExecutionTaskId = l1.ExecutionTaskId)
        AND l.Severity > 1
    ) l
ON e.Id = l.ExecutionTaskId

Left Join
    (
        SELECT hex(t.id) as TaskID, t.Name as TaskName, t.isTombstoned
        FROM TaskVersions t
        WHERE t.VersionNumber = 
          (SELECT max(VersionNumber) FROM TaskVersions b WHERE b.id = t.id)
    ) as t
ON hex(e.TaskId) = t.TaskID

Left Join Tasks t2
ON hex(e.taskid) = hex(t2.ID)

Left Join 
    (
        SELECT hex(ds.id) as DataSourceID, ds.Name as DataSourceName, ds.isTombStoned, ds.Settings
        FROM DataSourceVersions ds
        WHERE ds.VersionNumber = 
          (SELECT max(VersionNumber) FROM DataSourceVersions b WHERE b.id = ds.id)
    ) as ds
ON ds.DataSourceID=hex(t2.DataSourceID)

Left Join DataSourceMetaStructures ta
ON hex(e.OwnerId) = hex(ta.TableId)

--WHERE strftime('%Y-%m-%d',StartTime/10000000 - 62135596800,'unixepoch') <'2020-11-30'--StartTime
--WHERE strftime('%Y-%m-%d',EndTime/10000000 - 62135596800,'unixepoch') >'2020-11-01'--EndTime

Order By e.StartTime Desc 
Limit 1000