Stock Transaction List

[spillaip]

I am trying to write a Stock Transactions report between two dates, need help with the query. The format is:

Date of Transaction
Type of transaction
Stock
Qty
Price
Cost (Qty*Price)
Current Price
Current Value

Help is greatly appreciated.

[Fanntiago]

I think you can add the buy and sell volume during the day

[spillaip]

I have already input the stock transactions. The need is to get a query to get list of stock transactions.

[Renato]

No developers are currently active.
Users who are active in the forum will probably not have such a good knowledge of SQL to be able to fulfil such requests.

MMEX is a classic open source project and there is no entitlement to support. The forum here is a user forum, which means that users help each other.
I have tried to document further explanations about Open Source here: viewtopic.php?t=11132

[nstein]

I would start with something like this:

Code: Select all

SELECT 
  c.TRANSDATE AS 'Date', 
  CASE 
    WHEN c.TRANSCODE = "Withdrawal" THEN 'Buy' 
    ELSE 'Sell' 
  END AS 'Type', 
  s.STOCKNAME AS 'Name', 
  s.SYMBOL AS 'Symbol', 
  i.SHARENUMBER AS 'Qty', 
  i.SHAREPRICE AS 'Price', 
  ROUND(i.SHARENUMBER * i.SHAREPRICE, 4) AS 'Amount', 
  ROUND(h.VALUE,4) AS 'Current Price',
  ROUND(i.SHARENUMBER * h.VALUE ,4) AS 'Current Amount'
FROM CHECKINGACCOUNT_V1 AS c
JOIN TRANSLINK_V1 AS l ON l.CHECKINGACCOUNTID = c.TRANSID
JOIN STOCK_V1 AS s ON s.STOCKID = l.LINKRECORDID
JOIN SHAREINFO_V1 AS i ON i.CHECKINGACCOUNTID = c.TRANSID
JOIN (SELECT SYMBOL, VALUE FROM STOCKHISTORY_V1 AS sh 
  WHERE DATE = (
    SELECT MAX(DATE) 
    FROM STOCKHISTORY_V1 
    WHERE SYMBOL = sh.SYMBOL)) 
  AS h ON h.SYMBOL = s.SYMBOL
WHERE l.LINKTYPE = "Stock"
ORDER BY c.TRANSDATE