> On 11 Dec 2020, at 18:24, Chris Stephens <cstephen...@gmail.com> wrote: > > I'm trying to create a visual representation of a 6x8 grid of samples on a > rack using the following SQL format: > > with rack_display as ( > select sr.ts rack_ts > , sr.rack_id > , r.rack_barcode > , 1 as row_pos > , max(case when rack_well = 0 then 'A1: '||sample_barcode end) as col1 > , max(case when rack_well = 1 then 'A2: '||sample_barcode end) as col2 > , max(case when rack_well = 2 then 'A3: '||sample_barcode end) as col3 > , max(case when rack_well = 3 then 'A4: '||sample_barcode end) as col4 > , max(case when rack_well = 4 then 'A5: '||sample_barcode end) as col5 > , max(case when rack_well = 5 then 'A6: '||sample_barcode end) as col6 > from rack r > , sample_rack sr > , sample s > where r.rack_id = sr.rack_id > and sr.sample_id = s.sample_id > and sr.rack_well < 6 > group by sr.ts, sr.rack_id, r.rack_barcode, row_pos > union all > select sr.ts rack_ts > , sr.rack_id > , r.rack_barcode > , 2 as row_pos > , max(case when rack_well = 6 then 'B1: '||sample_barcode end) as col1 > , max(case when rack_well = 7 then 'B2: '||sample_barcode end) as col2 > , max(case when rack_well = 8 then 'B3: '||sample_barcode end) as col3 > , max(case when rack_well = 9 then 'B4: '||sample_barcode end) as col4 > , max(case when rack_well = 10 then 'B5: '||sample_barcode end) as > col5 > , max(case when rack_well = 11 then 'B6: '||sample_barcode end) as > col6 > from rack r > , sample_rack sr > , sample s > where r.rack_id = sr.rack_id > and sr.sample_id = s.sample_id > and sr.rack_well >= 6 > and sr.rack_well < 12 > group by sr.ts, sr.rack_id, r.rack_barcode, row_pos > union all > ... > ) > select * from rack_display order by rack_ts, rack_id, row_pos; > > the "union all"s continue for another 6 blocks. reports would filter on > rack_id and timestamp.
Is time really what groups these batches? I would double-check whether you may be omitting to store some data relevant to this process. > if timestamps for each load of a rack were guaranteed to be the same, this > would work. however, the "sr.ts" values may vary by a few seconds so there is > potential for the "group by" to break. ts differences will be a minimum of 5 > minutes for each distinct load of a rack. > > what i think i need is to manufacture a group by column based off rows in > "sample_rack" that have "ts" values that are < 1 minute from each other and > rack_id is the same. i'm coming up blank on how to accomplish that though. > my first thought was to create an interval of +/- 1 min then find all rows > that overlap and assign a group number but i'm not sure how to accomplish > that. You could date_trunc those timestamps to the minute and group on that. > there's also no guarantee an entire rack is full of samples so some "cells" > of display might be null. i think that makes the use of tablefunc crosstab a > little harder. if i remember correctly, it does not handle missing values > well. i'm open to any pivoting strategy. Many reporting tools have features to support just that. We use WebFOCUS, which calls those ACROSS columns. It’s a common requirement in reporting. Alban Hertroys -- There is always an exception to always.
