Untitled

 avatar
unknown
plain_text
5 months ago
2.2 kB
3
Indexable
let
    Start_Date = Date.ToText(Start_Date, "dd-MMM-yyyy"),  // Convert Start_Date parameter to required format
    End_Date = Date.ToText(End_Date, "dd-MMM-yyyy"),      // Convert End_Date parameter to required format

    Source = Value.NativeQuery(
        PostgreSQL.Database("pgpaas-atrd-dev-002.postgres.database.azure.com", "udpmpg002"),
        "select 
           to_char(pa.delivered,'MM') as ""Delivery month"",
           pa.delivered as ""Delivery Day"",
           pa.period as ""Delivery Period"",
           sum(pa.trade_volume) as ""P-Trade Volume-MWh"",
           pa.trade_price as ""P-Trade Price-(£)"",
           a.ticket as ""Ticket"",
           a.buy_sell as ""Trade Buy Sell"",
           a.counterparty as ""Counterparty"",
           a.memo as ""Memo"",
           a.memo2 as ""Memo2"",
           a.book as ""Book"",
           t.name as ""Trader Name"",
           sp.efa_period as ""EFA Block"",
           a.traded_on as ""Traded on Day""
        from 
           bo_user_uniper.pm_settlement_period sp,
           bo_user_uniper.pm_trade_period_asof pa,
           bo_user_uniper.pm_trade_asof a,
           pos_mgnt.bo_trader t
        where 
           pa.trade_asof_date = a.asof_date
           and sp.settlement_day = pa.delivered
           and sp.settlement_period = pa.period
           and pa.ticket = a.ticket
           and a.trader = t.trader
           and a.asof_date = (to_date('" & End_Date & "', 'dd-MON-yyyy') + 1)  -- Replaced hardcoded date with End_Date parameter
           and a.book in ('UPSL','PXHS') 
           and (a.counterparty like '%-E' or a.counterparty like 'UPT%') 
           and pa.delivered between to_date('" & Start_Date & "', 'dd-MON-yyyy') and to_date('" & End_Date & "', 'dd-MON-yyyy')  -- Replaced hardcoded dates with Start_Date and End_Date parameters
        group by 
           to_char(pa.delivered,'MM'),
           pa.delivered,
           pa.period,
           pa.trade_price,
           a.ticket,
           a.buy_sell,
           a.counterparty,
           a.memo,
           a.memo2,
           a.book,
           t.name,
           sp.efa_period,
           a.traded_on",
        null,
        [EnableFolding=true]
    )
in
    Source
Editor is loading...
Leave a Comment