select JB_JobNo as njob  
into "\\Memory\cwnjob" 
from Jobs 
join claimwatch v on jb_Jobno = v.CW_jobno and v.CW_send = True 
top 20;                                                         

CREATE INDEX IF NOT EXISTS "cjob" ON "\\Memory\cwnjob" ("njob"); 

select JB_JobNo,  
min(o.NT_LineNo) as "Onsite", 
max(f.NT_LineNo) as "Job complete", 
max(h.NT_LineNo) as "Handover", 
min(i.NT_LineNo) as "Invoiced", 
max(eb.NT_LineNo) as "estbook", 
min(es.NT_LineNo) as "estimate", 
max(ei.NT_LineNo) as "inspected", 
max(ea.NT_LineNo) as "authorised", 
max(en.NT_LineNo) as "incident", 
max(er.NT_LineNo) as "notif", 
max(ep.NT_LineNo) as "inspbook", 
max(el.NT_LineNo) as "tloss", 
min(p.NT_LineNo) as "parts", 
min(r.NT_LineNo) as "recv",   
max(ca.NT_LineNo) as "canc", 
min(TS_Unique) as "inprog" 
 
into "\\Memory\times" 
from Jobs 
join "\\Memory\cwnjob" on jb_jobno = njob 
left outer join notes h on jb_jobno = h.NT_Identifier and h.NT_Note like 'Set as Collected on%' 
left outer join notes o on jb_jobno = o.NT_Identifier and (o.NT_Note like 'Job flagged as On-Site%' or o.NT_Note like '%Assigned Key Tag number%') 
left outer join notes f on jb_jobno = f.NT_Identifier and Upper(f.NT_Note) like '%SET AS FINISHED%' 
left outer join notes i on jb_jobno = i.NT_Identifier and i.NT_Note like 'Invoiced by %' 
left outer join notes eb on jb_estno = eb.NT_Identifier and eb.NT_Note like 'Estimate Booking Date %' 
left outer join notes es on jb_estno = es.NT_Identifier and (es.NT_Note like 'Estimated Date %' or es.NT_Note like 'Inserted by import %' or es.NT_Note like 'Linked to Job %') 
left outer join notes ei on jb_estno = ei.NT_Identifier and ei.NT_Note like 'Inspected Date %' 
left outer join notes ea on jb_estno = ea.NT_Identifier and ea.NT_Note like 'Authorised Date %' 
left outer join notes en on jb_estno = en.NT_Identifier and en.NT_Note like 'Incident Date %' 
left outer join notes er on jb_estno = er.NT_Identifier and er.NT_Note like 'Repair Notification %' 
left outer join notes ep on jb_estno = ep.NT_Identifier and ep.NT_Note like 'Inspection Booking Date %' 
left outer join notes el on jb_jobno = el.NT_Identifier and el.NT_Note like 'Set as Total Loss on %' 
left outer join notes p on jb_jobno = p.NT_Identifier and p.NT_Note like 'Parts Ordered: %' 
left outer join notes r on jb_jobno = r.NT_Identifier and r.NT_Note like 'Parts Received: %' 
left outer join notes ca on jb_jobno = ca.NT_Identifier and ca.NT_Note like 'Job Cancelled - %' 
left outer join Timeshts on jb_jobno = TS_JobNo and TS_Date > '1991-01-01' 
group by 1;