This is another one that could be more helpful: -
--worksheet.status=3 in progress
--worksheet.status=5 completed with errors
--worksheet.status=7 completed with warning
--worksheet.status=3 in progress
--worksheetstore.status=3 warning
--worksheetstore.status=1 processed successfully
--worksheetstore.status=0 not processed
--worksheetstore.status=2 processed with error
select region,storecode,t1.TheStoreID,storename,lastconnected,last401succeeded,lastworksheetsucceeded
from
(select store.region,storecode,store.id as ThestoreID,store.name as StoreName,max(eventtime) as LastConnected
from store left join HQEventlog
on store.id=storeid
group by store.region,storecode,store.id,store.name) t1
inner join
(select store.id as TheStoreID,max(dateprocessed) as Last401Succeeded
from store left join WorksheetStore on store.id=WorksheetStore.storeid and Worksheetstore.Status=1
left join worksheet on worksheet.ID=worksheetStore.WorksheetID and style=401
group by store.region,storecode,store.id,store.name) t2
on t1.thestoreid=t2.thestoreid
inner join
(select store.id as TheStoreID,max(dateprocessed) as LastWorksheetSucceeded
from store left join WorksheetStore on store.id=WorksheetStore.storeid and Worksheetstore.Status=1
group by store.region,storecode,store.id,store.name) t3
on t1.thestoreid=t3.thestoreid
order by region,storecode,t1.TheStoreID
A Nashat
