CHK_OPO	BBTradeDb..t_bb_opo tickets for an account/position

--------------------------------------------------------------------------------
-- BBTradeDb..t_bb_opo tickets for an account/position
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set Date1="`date '+%b %d %Y'`"
\set Date2="${Date1}"
--
\if [ "${Date2}" = "" ]
    \set Date2="$date"
\fi
--
print "BBTradeDb..t_bb_opo ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
select date_received=convert(varchar(20), date_rcvd, 116)
     , seqno
     , open_pos=open_position
     , open_avg=open_avg_cost
     , curr_pos=current_position
     , curr_avg=current_avg_cost
     , real_pnl=realized_pnl
     , trader
     , secid
     , bb_id
  from BBTradeDb..t_bb_opo
 where "${Date1}" <= trade_date and trade_date <= "${Date2}"
   and trader = "${ACCT}"
   and (secid = "${SECURITY}" or bb_id = substring("${SECURITY}", 1, 8))
order by date_rcvd
;-m vert
--------------------------------------------------------------------------------


CHK_OTR	BBTradeDb..t_bb_otr tickets for an account's movements

--------------------------------------------------------------------------------
-- BBTradeDb..t_bb_otr tickets for an account's movements
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set Date1="`date '+%b %d %Y'`"
\set Date2="${Date1}"
--
\if [ "${Date2}" = "" ]
    \set Date2="$date"
\fi
--
print "BBTradeDb..t_bb_otr  ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
select received=convert(char(8),date_rcvd,8)
     , seqno
     , tran_num
     , orig_tnum=orig_tran_num
     , rec_type
     , txtype = trans_type
     , c4c=cancel_correct_fg
     , rf=retail_feed_fg
     , bs=buysell_fg
     , trade_amt
     , price
     , discount_rate
     , long_note3
     , long_note4
     , trd_time=convert(char(8),trade_time,8)
     , asof_time=convert(char(8),asof_trade_time,8)
     , trd_date=convert(char(11),trade_date,106)
     , asof_date=convert(char(11),asof_trade_date,106)
     , trader_name
     , secid
     , bb_id
     , ticker
 from BBTradeDb..t_bb_otr
where ("${Date1}" <= trade_date and trade_date <= "${Date2}")
  and trader_name = "${ACCT}"
  and (secid = "${SECURITY}" or bb_id = "${SECURITY}")
order by date_rcvd
;-m vert
--------------------------------------------------------------------------------


FST_POS	FstPosition_audit for selected account, security

--------------------------------------------------------------------------------
-- FstPosition_audit for selected account, security
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set Date2="${Date1}"
--
\if [ "${Date2}" = "" ]
    \set Date2="$date"
\fi
--
-- use temporary variables
declare @CUSIP varchar(20),
        @BB_ID varchar(20),
        @ACCTk int,
        @SECURITYk int
--
set nocount on
--
-- use temporary variables
select @SECURITYk = ItpIns#key
     , @CUSIP = cusip
     , @BB_ID = bb_id
  from FstDb..FstInsFixed
 where cusip = "${SECURITY}"
    or bb_id = "${BB_ID}"
--
select @ACCTk = ItpTransBucket#key
  from FstDb..FstAccount
 where name = "${ACCT}"
--
print "FstDb..FstPosition_audit  ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
set nocount off
select cusip=@CUSIP
     , bb_id=@BB_ID
     , inskey=@SECURITYk
     , curr_pos=current_trading_position
     , real_pnl=todays_realized_pl#amount
     , update_time=convert(varchar(8), last_update#time, 8)
     , trade_date=convert(varchar(11), bb_trade_key#trade_date, 106)
     , bb_seqno=bb_trade_key#bb_seqno
     , avg_price=todays_avg_price#amount
  from FstDb..FstPosition_audit
 where trans_bucket_key = @ACCTk
   and ins_key = @SECURITYk
   and ("${Date1}" <= bb_trade_key#trade_date and
                      bb_trade_key#trade_date <= "${Date2}")
;-m vert
--------------------------------------------------------------------------------


TRD_CLOSE	closingPosition records from TradeDb

--------------------------------------------------------------------------------
-- closingPosition records from TradeDb
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set Date2="${Date1}"
\set FROM_DAYBEFORE=y
--
\if [ "${Date2}" = "" ]
    \set Date2="$date"
\fi
--
declare @DateLo datetime
      , @DateHi datetime
      , @sDateLo varchar(11)
      , @sDateHi varchar(11)
\if [ "${FROM_DAYBEFORE}" = y ]
    \set SHIFT=1
\else
    \set SHIFT=0
\fi
set nocount on
select @DateLo=dateadd(day, 0 - ${SHIFT}, "${Date1}"),
       @DateHi=dateadd(day, 1 - ${SHIFT}, "${Date2}")
--
-- make sure we pick up the previous non-weekend day if a weekend
select @DateLo=case datepart(weekday, @DateLo)
               when 1 then dateadd(day, 0 - 2, @DateLo)
               when 7 then dateadd(day, 0 - 1, @DateLo)
                      else @DateLo
               end
select @DateHi=case datepart(weekday, @DateHi)
               when 1 then dateadd(day, 0 - 2, @DateHi)
               when 7 then dateadd(day, 0 - 1, @DateHi)
                      else @DateHi
               end
select @sDateLo=convert(varchar(11), @DateLo, 106),
       @sDateHi=convert(varchar(11), dateadd(day, -1, @DateHi), 106)
set nocount off
--
print "TradeDb..closingPosition  ${ACCT} ${SECURITY}  (%1! to %2!)"
      , @sDateLo, @sDateHi
print ""
--
select report_date_time=convert(varchar(20), report_date, 116)
     , secid
     , pos=position
     , settle_pos=settle_position
     , ticker
     , coupon
     , maturity=convert(varchar(11), maturity_date, 106)
     , trader
  from TradeDb..closingPosition
 where trader="${ACCT}"
   and secid="${SECURITY}"
   and (@DateLo <= report_date and report_date < @DateHi)
;-m vert
--------------------------------------------------------------------------------


TRD_STAT	TradeDb..genericTrades tickets for an account/position

--------------------------------------------------------------------------------
-- TradeDb..genericTrades tickets for an account/position
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set Date2="${Date1}"
\set TP_ONLY=y
\set CURRENT_ONLY=y
--
\if [ "${TP_ONLY}" = 'y' ]
    \set TP_CLAUSE="and fst_app like '%TicketProcessor%'"
\fi
--
\if [ "${CURRENT_ONLY}" = 'y' ]
    \set CURRENT_CLAUSE="and is_current = 1"
\else
    \set CURRENT_DISPLAY=', x=case is_current when 1 then "Y" else "N" end'
\fi
--
\if [ "${Date2}" = "" ]
    \set Date2="$date"
\fi
--
set nocount on
declare @hDate1 int
      , @hDate2 int
select @hDate1=convert(int,convert(char(8),convert(datetime,"${Date1}"),112)),
       @hDate2=convert(int,convert(char(8),convert(datetime,"${Date2}"),112))
set nocount off
--
print "TradeDb..genericTrades  ${ACCT} ${SECURITY}  (${Date1} to ${Date2})"
print ""
--
select upd_time=convert(char(8), st.update_time, 108)
     , date=g.hit_date
     , group_id=substring(g.group_id,1,10)
     , fst_tran=g.fst_tran_num
     , bb_tran=map.bb_tran_num
     --, map.*
     , orig_fst=g.orig_fst_tran_num
     , orig_bb=g.orig_tran_num
     , grp_seq=g.group_tran_num
     , g.seqno
     , g.ticket_type
     , g.action_type
     ${CURRENT_DISPLAY}
     , side=case g.trade_side when 1 then "S" else "B" end
     , post_trd=case
                when g.trade_date=g.asof_trade_date then " "
                else convert(varchar(8), g.asof_trade_date, 112) end
     , quantity = convert(numeric(15,0), quantity)
     , st.rcode
     , st.fst_app
     , st.comment
     , account=g.trader_account
     , g.secid
  from TradeDb..genericTrades g
     , TradeDb..genericTradeMap map
     , TradeDb..genericTradeStatus st
where g.fst_tran_num = map.fst_tran_num
  and g.fst_tran_num *= st.fst_tran_num
  and (@hDate1 <= g.hit_date and g.hit_date <= @hDate2)
  and trader_account = "${ACCT}"
  and (secid = "${SECURITY}" or secid = "${BB_ID}")
  ${CURRENT_CLAUSE}
  ${TP_CLAUSE}
-- order by g.fst_tran_num
order by st.update_time
;-m vert
--------------------------------------------------------------------------------


_ Application Error	TradeDb..Application_Error table>>>

--------------------------------------------------------------------------------
-- Fetch records for Date1 from TradeDb..Application_Error
\set Date1="`date '+%b %d %Y'`"
\if [ "${Date1}" = "" ]
    \set Date1="$date"
\fi
--
set nocount on
declare @tradeDate datetime
select @tradeDate="${Date1}"
set nocount off
--
select * from TradeDb..Application_Error
 where datediff(dd,date_stamp,@tradeDate) = 0
   and sql_error_code != 0

_ Malc's PosAudit	Fetches FstDb..FstPosition_audit records

--------------------------------------------------------------------------------
-- Malc's position audit report fetches FstDb..FstPosition_audit records
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set TP_ONLY=y
\set CURRENT_ONLY=y
--
\if [ "${Date1}" = "" ]
    \set Date1="$date"
\fi
--
select nocount on
declare @tradeDate datetime
      , @traderName varchar(20)
      , @secid varchar(20)
select @tradeDate = "${Date1}"
     , @secid = "${SECURITY}"
     , @traderName = "${ACCT}"
select nocount off
--
select acct.name
     , ins.cusip
     , ins.ticker
     , ins.bb_id
     , pos.current_trading_position
     , previous_trading_position
     , pos.todays_realized_pl#amount
     , pos.todays_avg_price#amount
     , last_update#time
  from FstDb..FstPosition_audit pos
     , FstDb..FstAccount acct
     , FstDb..FstInsFixed ins
 where pos.bb_trade_key#trade_date=@tradeDate
   and pos.key#trans_bucket_key=acct.ItpTransBucket#key
   and pos.key#ins_key=ins.ItpIns#key
   and acct.name=@traderName
   and ins.bb_id like @secid
 order by acct.name
        , ins.cusip
        , ins.ticker
        , pos.last_update#time
--------------------------------------------------------------------------------


_ Malc's latency query	gives latency in seconds

--------------------------------------------------------------------------------
-- Malc's trade flow latency query gives latency in seconds
set rowcount 0
--
declare @now       datetime
declare @tradeDate datetime
declare @hitDate   int
declare @trader    char(12)
declare @groupId   char(10)
declare @groupNY   char(10)
declare @groupLDN  char(10)
--
select @groupNY='BTSFeed'
     , @groupLDN='BTSFeedLDN'
--
select @tradeDate=getdate()
select @hitDate=convert(int,convert(char(12),@tradeDate,112))
     , @trader='%'
     , @groupId=@groupNY
--
-- Blooomberg trades and their receipt by BTSFeed
select otr.trader_name
     , otr.secid
     , otr.ticker
     , otr.bb_id
     , date_rcvd
     , otr.seqno
     , otr.rec_type
     , trade_time "BBG Entered"
     , otr.date_rcvd "BBG Rcvd"
     , misc.update_time "BTSFeed Read"
     , trade.entry_time "FST Received"
     , status.update_time "FST Processed"
     , datediff(ss,otr.date_rcvd,status.update_time) "Total Latency (sec)"
     , (datediff(mi,dateadd(ss,datediff(ss,'1/1/1900',otr.trade_time)
     , otr.trade_date)
     , otr.date_rcvd)+1440)%1440 "BBG Flow Latency (min)"
  from t_bb_otr otr
     , t_bb_misc misc
     , TradeDb..genericTrades trade
     , TradeDb..genericTradeStatus status
where otr.trade_date=convert(char(12),@tradeDate,101)
  and otr.trader_name like @trader 
  and misc.trade_date=otr.trade_date
  and misc.seqno=otr.seqno
  and misc.fst_env='FIPROD'
  and trade.group_id=@groupId
  and trade.hit_date=@hitDate
  and otr.seqno=trade.seqno
  and trade.fst_tran_num=status.fst_tran_num
  and status.is_current=1
order by otr.seqno desc
;-m vert
--------------------------------------------------------------------------------


_ Malc's trades	Uses TradeDb..EnumValues for ticket type mnemonics

--------------------------------------------------------------------------------
-- Malc's trade report uses TradeDb..EnumValues for ticket type mnemonics
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
\set HitDate1="`date '+%Y%m%d'`"
\set TP_ONLY=y
\set CURRENT_ONLY=y
--
\if [ "${Date1}" = "" ]
    \set Date1="$date"
\fi
--
set nocount on
declare @tradeDate datetime
      , @traderName varchar(20)
      , @secid varchar(20)
      , @hitdate int
select @tradeDate = "${Date1}"
     , @secid = "${SECURITY}"
     , @traderName = "${ACCT}"
--
\if [ "${HitDate1}" = "" ]
    select @hitdate = convert(int, convert(char(8), @tradeDate, 112))
\else
    select @hitdate = convert(int, "${HitDate1}")
\fi
set nocount off
--
select convert(char(8), trade.update_time, 108)  "Trd Time"
     , convert(char(8), status.update_time, 108) "Stat Tm"
     , convert(char(8), map.update_time, 108)    "Map Time"
     , trade.fst_tran_num
     , map.bb_tran_num
     , trade.group_id
     , trade.seqno,map.bb_seqno
     , val1.description
     , val2.description
     , trade_side
     , quantity
     , orig_tran_num
     , orig_fst_tran_num
     , group_tran_num
     , longnote4
     , fst_env
     , fst_app
     , is_current
     , trade.rcode
     , trade.comment
     , status.rcode
     , status.comment
  from TradeDb..genericTrades trade
     , TradeDb..genericTradeStatus status
     , TradeDb..genericTradeMap map
     , TradeDb..EnumValues val1
     , TradeDb..EnumValues val2
 where trade.hit_date=@hitdate 
   and trade.hit_date=status.hit_date
   and trade.group_id=status.group_id
   and trade.seqno=status.seqno
   and trade.hit_date=map.hit_date
   and trade.group_id=map.group_id
   and trade.seqno=map.seqno
   and trade.trader_account=@traderName
   and trade.secid like @secid
 --and substring(trade.secid,1,char_length(rtrim(@secid)))=@secid
   and trade.fst_tran_num=status.fst_tran_num
   and is_current=1
   and val1.key_type='TICKET_TYPE'
   and val2.key_type='TICKET_ACTION'
   and val1.key_value=ticket_type
   and val2.key_value=action_type
 order by trade.fst_tran_num
--------------------------------------------------------------------------------


_ Malc's uncancelled bb	non-cancel t_bb_otr records without corresponding cancellations

--------------------------------------------------------------------------------
-- Malc's uncancelled bb trades (from BBTradeDb..t_bb_otr)
\set ACCT=FSTTDR
\set SECURITY=912828CK4
\set BB_ID="${SECURITY}"
\set Date1="`date '+%b %d %Y'`"
--
\if [ "${Date1}" = "" ]
    \set Date1="$date"
\fi
--
set nocount on
declare @tradeDate datetime
      , @traderName varchar(20)
      , @secid varchar(20)
select @tradeDate = "${Date1}"
     , @secid = "${SECURITY}"
     , @traderName = "${ACCT}"
set nocount off
--
select date_rcvd
     , seqno
     , rec_type
     , tran_num
     , buysell_fg
     , trade_amt
     , orig_tran_num
     , retail_feed_fg
  from BBTradeDb..t_bb_otr otr1
 where trade_date=@tradeDate
   and trader_name=@traderName
   and bb_id like @secid --substring(bb_id,1,char_length(rtrim(@secid)))=@secid
   and rec_type not between 100 and 200 -- cancel rec_types
   and not exists
       ( select 1
           from BBTradeDb..t_bb_otr otr2
          where otr1.tran_num=otr2.tran_num
            and rec_type between 100 and 200)
 order by seqno
--------------------------------------------------------------------------------


~	TradeDb..genericTrades action and ticket type to string
tkt_type_act="["+convert(char(2),ticket_type)+","+
                    convert(char(2),action_type)+":"+
                    -- text version
                    convert(varchar(12),
                            case action_type
                                 when  1 then "   New"
                                 when  2 then "   Cxl"
                                 when  3 then "  Corr"
                                 when  4 then "  PNew"
                                 when  5 then "  PCxl"
                                 when  6 then " PCorr"
                                 when  7 then "   Rej"
                                 when  8 then "  Conf"
                                 when  9 then "Cx4Cor"
                                 when 10 then " Unchg"
                                 when 11 then "Cor4Cx"
                                 when 12 then "Unconf"
                                 when 13 then " UnCxl"
                                 when 14 then "Cx4Rej"
                                 else "???"+ltrim(convert(char(2),action_type))
                            end
                            + " " +
                            case ticket_type
                                 when 40 then "Sale "
                                 when 41 then "Tradr"
                                 when 42 then "Match"
                                 when 43 then "Mastr"
                                 when 44 then "Alloc"
                                 when 45 then "Slate"
                                 when 46 then "Jrnl "
                                 when 47 then "Swap "
                                 when 48 then "ExtJnl"
                                 when 50 then "Future"
                                 when 51 then "Option"
                                 else "???"+ltrim(convert(char(2),ticket_type))
                            end)
                    + "]"

~~	<<<SQSH settings variables>>>
\set echo=1                     # trace SQSH SQL - executed SQL will be printed
\set headers=1                  # generate column headers
\set footers=1                  # generate (%d rows affected) messages
\set output_parms=1             # enable display of "exec sp" output params
\set datetime='%h %d %Y %T.%u'  # set default output format for dates
\set date='%h %d %Y'            # format for $date pseudovariable value
\set time='%H:%M:%S'            # format for $time pseudovariable value
\set style=hor                  # output style: hor|vert|bcp|html|meta|pretty
\set width=256                  # display width (default 80, max 256)