Untitled

 avatar
unknown
plain_text
4 months ago
2.2 kB
5
Indexable
let
    Start_Date_Formatted = Text.Proper(Date.ToText(Start_Date, "dd-MMM-yyyy")),
    End_Date_Formatted = Text.Proper(Date.ToText(End_Date, "dd-MMM-yyyy")),
    Source = Value.NativeQuery(
        PostgreSQL.Database("pgpaas-atrd-dev-002.postgres.database.azure.com", "udpmpg002", [CommandTimeout=#duration(0, 1, 40, 0)]),
        "select #(lf)   
           to_char(pa.delivered,'MM') as ""Delivery month"",#(lf)   
           pa.delivered as ""Delivery Day"",#(lf)   
           pa.period as ""Delivery Period"",  #(lf)   
           sum(pa.trade_volume) as ""P-Trade Volume-MWh"", #(lf)   
           pa.trade_price as ""P-Trade Price-(£)"",#(lf)   
           a.ticket as ""Ticket"",#(lf)   
           a.buy_sell as ""Trade Buy Sell"",#(lf)   
           a.counterparty  as ""Counterparty"",#(lf)   
           a.memo as ""Memo"",  #(lf)   
           a.memo2 as ""Memo2"",#(lf)   
           a.book as ""Book"",#(lf)   
           t.name as ""Trader Name"",#(lf)   
           sp.efa_period as ""EFA Block"",#(lf)   
           a.traded_on as ""Traded on Day""#(lf)
        from  
           bo_user_uniper.pm_trade_period pa,#(lf)   
           bo_user_uniper.pm_settlement_period sp,#(lf)   
           bo_user_uniper.pm_trade a, #(lf)   
           pos_mgnt.bo_trader t#(lf)
        where 
           sp.settlement_day=pa.delivered  #(lf)
           and sp.settlement_period=pa.period   #(lf)
           and pa.ticket=a.ticket    #(lf)
           and a.trader=t.trader    #(lf)
           and a.book in ('UPSL','PXHS')  #(lf)
           and (a.counterparty like '%-E' or a.counterparty like 'UPT%') #(lf)
           and pa.delivered between to_date('" & Start_Date_Formatted & "','dd-MON-yyyy') and to_date('" & End_Date_Formatted & "','dd-MON-yyyy')  #(lf)
        group by 
           to_char(pa.delivered,'MM'), #(lf)
           pa.delivered,#(lf)
           pa.period, #(lf)
           pa.trade_price, #(lf)
           a.ticket, #(lf)
           a.buy_sell, #(lf)
           a.counterparty, #(lf)
           a.memo, #(lf)
           a.memo2, #(lf)
           a.book, #(lf)
           t.name, #(lf)
           sp.efa_period, #(lf)
           a.traded_on",
        null,
        [EnableFolding=true]
    )
in
    Source
Editor is loading...
Leave a Comment