-------------------- SMART Transactions report ---------------------------------------------------------------------------------------
-------------------- Find period at https://www.whatdotheyknow.com/request/136295/response/334611/attach/4/Attachment%20B.PDF.pdf ----

-------------------- Field 1 +2 - DAY field + ENCTS Total by Day

        SELECT to_char(cj.txn_timestamp,'YYYY-MM-DD') DAY, SUM(journey_count) JCount, 'ENCTS' Scheme
        FROM report.journeys_by_retailer cj
        WHERE
        cj.txn_timestamp >= to_date('30-04-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.txn_timestamp <= to_date('27-05-17', 'DD-MM-YY')  ------------- UPDATE ME
        AND cj.product_retailer NOT IN (127,115,8300)
        GROUP BY to_char(cj.txn_timestamp,'YYYY-MM-DD')
        ORDER BY to_char(cj.txn_timestamp,'YYYY-MM-DD');

-------------------- Field 3 - Stagecoach Total by Day

        SELECT to_char(cj.txn_timestamp,'YYYY-MM-DD') DAY,  SUM(journey_count) JCount, 'Stagecoach' Scheme
        FROM report.journeys_by_retailer cj
        WHERE
        cj.txn_timestamp >= to_date('30-04-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.txn_timestamp <= to_date('27-05-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.product_retailer = 127
        GROUP BY to_char(cj.txn_timestamp,'YYYY-MM-DD')
        ORDER BY to_char(cj.txn_timestamp,'YYYY-MM-DD');

 -------------------- Field 4 - Guided Bus

        SELECT to_char(cj.txn_timestamp,'YYYY-MM-DD'),  SUM(journey_count) JCount, 'Guided Bus'Scheme
        FROM report.journeys_by_retailer cj
        WHERE
        cj.txn_timestamp >= to_date('30-04-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.txn_timestamp <= to_date('27-05-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.product_retailer = 115
        GROUP BY to_char(cj.txn_timestamp,'YYYY-MM-DD')
        ORDER BY to_char(cj.txn_timestamp,'YYYY-MM-DD');

----------------- Field 5 - Oxford

        SELECT to_char(cj.txn_timestamp,'YYYY-MM-DD'),  SUM(journey_count) JCount, 'Oxford Bus' Scheme
        FROM report.journeys_by_retailer cj
        WHERE
        cj.txn_timestamp >= to_date('30-04-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.txn_timestamp <= to_date('27-05-17', 'DD-MM-YY') ------------- UPDATE ME
        AND cj.product_retailer = 8300
        GROUP BY to_char(cj.txn_timestamp,'YYYY-MM-DD')
        ORDER BY to_char(cj.txn_timestamp,'YYYY-MM-DD');

----------------------------------