CODEWITHSUNDEEP
sqloracle-database
paddyusa
paddyusa

Reputation: 39

Group by Month Using a Column That has Daily Dates

I need to add in an extra 'group by' field to the below code to group by Month in relation to the trade_date field. The code currently results in a table with column headers matching the 4 fields in the beginning 'select' statement. I need to add a 5th that uses the trade_date field to group by month, not day. Any thoughts?

select  
  TRADEABLE_INSTR_NAME, 
  TRADING_USERID, 
  UNDERLYING_SYM_BLOOMBERG,
  SUM(DMA_FEE) AS DMA_FEE_SUBTOTAL
  FROM
  (
SELECT
    
    a.transaction_id,
    b.amount,
    b.charge_id,
    c.charge_type_id,
    d.charge_group,
    e.account_name,
    f.sig_entity_label,
    e.trading_userid,
    e.exchange_id,
    e.tradeable_instr_name,
    g.instr_subtype_label,
    g.underlying_sym_bloomberg,
    g.fut_expiration_date,
    e.trade_date,
    timestamp_table.timeformated,
    a.rate * a.basis_value AS dma_fee,
    SUM(A.RATE * A.BASIS_VALUE) OVER () as DMA_FEE_TOTAL
FROM
    repldoadm.ire_estimate_trans_map       a
    LEFT JOIN repldoadm.ire_charges_estimate         b ON a.estimate_id = b.estimate_id
    LEFT JOIN repldoadm.ire_charges_lu               c ON b.charge_id = c.charge_id
    LEFT JOIN repldoadm.ire_charge_types_lu          d ON c.charge_type_id = d.charge_type_id
    LEFT JOIN repldoadm.vw_ire_trade_transactions    e ON a.transaction_id = e.trans_id
    LEFT JOIN (
        SELECT
            to_char(create_ts, 'HH24:MI:SS') AS timeformated,
            trans_id
        FROM
            repldoadm.vw_ire_trade_transactions
    )                                      timestamp_table ON ( a.transaction_id = timestamp_table.trans_id )
    LEFT JOIN repldoadm.vw_ire_accounts              f ON e.account_name = f.pb_account_name
    LEFT JOIN stig_adm.instrument_universe           g ON e.tradeable_instr_name = g.short_name
WHERE
        timeformated >= '07:00:00'
    AND timeformated <= '18:00:00'
    AND fut_expiration_date >= add_months(trunc(sysdate), - 12)
    AND trade_date = '02-AUG-21'
    AND e.exchange_id = '30'
    AND charge_group = 'EXECUTION_BROKER_CHARGES'
    AND estimate_run = 'Late'
    --AND transaction_id IN ( '791088074', '791309067' )
)
group by TRADEABLE_INSTR_NAME, TRADING_USERID, UNDERLYING_SYM_BLOOMBERG;

Upvotes: 0

Views: 60

Answers (1)

MT0
MT0

Reputation: 167822

Group by TRUNC(trade_date, 'MM')

SELECT TRADEABLE_INSTR_NAME, 
       TRADING_USERID, 
       UNDERLYING_SYM_BLOOMBERG,
       SUM(DMA_FEE) AS DMA_FEE_SUBTOTAL,
       TRUNC( trade_date, 'MM' ) AS trade_month
FROM   (
  <your huge sub-query>
)
group by
       TRADEABLE_INSTR_NAME,
       TRADING_USERID,
       UNDERLYING_SYM_BLOOMBERG,
       TRUNC(trade_date, 'MM');

Your query would effectively be:

select TRADEABLE_INSTR_NAME, 
       TRADING_USERID, 
       UNDERLYING_SYM_BLOOMBERG,
       TRUNC(trade_date, 'MM') AS trade_month
       SUM(DMA_FEE) AS DMA_FEE_SUBTOTAL
FROM   (
  SELECT e.trading_userid,
         e.tradeable_instr_name,
         g.underlying_sym_bloomberg,
         e.trade_date,
         a.rate * a.basis_value AS dma_fee
  FROM   repldoadm.ire_estimate_trans_map a
         INNER JOIN repldoadm.ire_charges_estimate b
         ON a.estimate_id = b.estimate_id
         INNER JOIN repldoadm.ire_charges_lu c
         ON b.charge_id = c.charge_id
         INNER JOIN repldoadm.ire_charge_types_lu d
         ON c.charge_type_id = d.charge_type_id
         INNER JOIN repldoadm.vw_ire_trade_transactions e
         ON (   a.transaction_id = e.trans_id
            AND TO_CHAR(e.create_ts, 'HH24:MI:SS') BETWEEN '07:00:00'
                                                       AND '18:00:00'
            )
         LEFT JOIN repldoadm.vw_ire_accounts f
         ON e.account_name = f.pb_account_name
         INNER JOIN stig_adm.instrument_universe g
         ON e.tradeable_instr_name = g.short_name
  WHERE  g.fut_expiration_date >= add_months(trunc(sysdate), - 12)
  AND    e.trade_date = DATE '2021-08-02'
  AND    e.exchange_id = '30'
  AND    d.charge_group = 'EXECUTION_BROKER_CHARGES'
  AND    estimate_run = 'Late'
  --AND    transaction_id IN ( '791088074', '791309067' )
)
group by
       TRADEABLE_INSTR_NAME,
       TRADING_USERID,
       UNDERLYING_SYM_BLOOMBERG,
       TRUNC(trade_date, 'MM');

Note: Because you are requiring non-NULL values in the WHERE clause your LEFT JOINs become INNER JOINs.

Note: You do not need to SELECT columns in the inner query that you are not using in the outer query.

Note: I do not know if you need to join repldoadm.vw_ire_accounts f as you do not appear to use it.

Upvotes: 2

Related Questions