sql code for checking the status of runs on the fly

[GoogleCodeExporter]

select v.id_run_sample, geneusID_sample, analysis_id, flowcell_serial, lane, 
project, sample_name, count(distinct f.id_file_type),
    if(count(distinct f.id_file_type) > 1, "ANALYSIS",  if(count(distinct f.id_file_type) < 1, "IN PROCESS", "READS AVAIL"))

from view_run_metric v left join file f on f.id_run_sample = v.id_run_sample 
and f.id_file_type IN (41,51,33,14,38)  
group by id_run_sample

Original issue reported on code.google.com by zack...@gmail.com on 28 May 2014 at 11:52

[GoogleCodeExporter]

I just rolled out a new eccpgxt-beta that should be very good at determining 
run status. it does this on the fly using a left join.

joining in the view proved to be too slow due to lack of indexing, so moved to 
the backed query and temporarily added an index on view_run_metric 
id_run_sample resulting in high speed.

Original comment by zack...@gmail.com on 30 May 2014 at 3:47

• Changed state: Done

[GoogleCodeExporter]

Well wish you told me that you added index on files tables too.

On Thu, May 29, 2014 at 8:48 PM, <usc-epigenome-center@googlecode.com>
wrote:

Original comment by natalia....@gmail.com on 31 May 2014 at 2:10

[GoogleCodeExporter]

sorry I forgot to mention it. Luckily it does not need to be generated except 
for the first time.

Original comment by zack...@gmail.com on 31 May 2014 at 2:46