Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 2 TABLES (portfolio and Transactions)
- PORTFOLIO
- Exportdate Accno Marketvalue
- 01.01.2017 1 100
- 01.01.2017 2 50
- 01.01.2017 3 200
- 02.01.2017 1 140
- 02.01.2017 2 70
- 02.01.2017 3 210
- 03.01.2017 1 120
- 03.01.2017 2 70
- 03.01.2017 3 256
- TRANSACTIONS
- Exportdate Accno Amt
- 01.01.2017 1 100
- 01.01.2017 2 50
- 01.01.2017 3 200
- 02.01.2017 1 11
- 02.01.2017 2 2
- 02.01.2017 3 4
- 03.01.2017 1 5
- 03.01.2017 2 7
- 03.01.2017 3 1
- My desired result:
- MYQUERY (costprice)
- Exportdate accno marketvalue sumAmt Return
- 01.01.2017 1 100 100 0
- 01.01.2017 2 50 50 0
- 01.01.2017 3 200 200 0
- 02.01.2017 1 140 111 29
- 02.01.2017 2 70 52 18
- 02.01.2017 3 210 204 6
- 03.01.2017 1 120 116 4
- 03.01.2017 2 70 59 11
- 03.01.2017 3 256 205 51
- SELECT
- x.ExportDate
- , x.AccNo
- , x.MarketValue
- , lag(x.marketvalue) OVER (ORDER BY x.portofolioID, x.accno) as last
- ,(SELECT
- SUM(y.amt) OVER(ORDER BY y.exportdate, y.accno)
- from myDB.dbo.view_trans y
- where y.exportdate <= x.exportdate and y.AccNo = x.accno) as costprice
- FROM MyDb.dbo.Portofolio as X
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement