HI
I'm writing a query that selects different barcodes. Every barcode has a specific sequence. But in the database the barcode exicsts multiple times. Everytime a sequence is added the barcode is visable in a different row. For example:
I don't want to use a date filter because sometimes two sequences happen at the same date. Now I've used three select filters but I'm wondering if it's possible to use an other way.
SELECT
wc.dn_barcode
, (
SELECT da_waarnemingsequence
FROM collo_dwh.collo wc2
WHERE wc2.dn_barcode = wc.dn_barcode
AND length(sequence) =
(
SELECT max(length(sequence)
FROM collo_dwh.collo wc3
WHERE wc3.dn_barcode = wc2.dn_barcode
)
) sequence
FROM
(database wc)
WHERE
wc.da_datum_sortering1 BETWEEN "date_add"('day', -7, current_date) AND current_date
AND wc.da_landcode_gea = 'NL'
AND wc.sequence LIKE '%A1%B1%'
AND wc.sequenceNOT LIKE '%I%'