newstock_portfolio.sql

--select stmt to get list of symbol and first purchasedate from portfolio table
SELECT STOCKMASTER.EXCHANGE, STOCKMASTER.SYMBOL, strftime('%d-%m-%Y', min(STOCKPORTFOLIO.PURCHASE_DATE)) as FirstPurchaseDate,
		strftime('%d-%m-%Y', max(STOCKPORTFOLIO.PURCHASE_DATE)) as LastPurchaseDate, sum(STOCKPORTFOLIO.PURCHASE_QTY) as SUMUNITS FROM STOCKPORTFOLIO
		INNER JOIN STOCKMASTER ON STOCKMASTER.ROWID = STOCKPORTFOLIO.STOCKMASTER_ROWID
		WHERE STOCKPORTFOLIO.MASTER_ROWID = 1
		GROUP BY STOCKPORTFOLIO.STOCKMASTER_ROWID
		ORDER BY STOCKPORTFOLIO.STOCKMASTER_ROWID ASC, FirstPurchaseDate ASC

		----query to get specific symbol transactions		
SELECT STOCKPORTFOLIO.ROWID as ID, STOCKPORTFOLIO.MASTER_ROWID as MASTERID, STOCKPORTFOLIO.STOCKMASTER_ROWID as STOCKID, STOCKMASTER.EXCHANGE, 
		STOCKMASTER.SYMBOL, STOCKMASTER.COMP_NAME, STOCKMASTER.SERIES, strftime('%d-%m-%Y', STOCKPORTFOLIO.PURCHASE_DATE) AS PURCHASE_DATE, 
		STOCKPORTFOLIO.PURCHASE_PRICE, STOCKPORTFOLIO.PURCHASE_QTY, STOCKPORTFOLIO.COMMISSION_TAXES, STOCKPORTFOLIO.INVESTMENT_COST 
		FROM STOCKMASTER 
		INNER JOIN STOCKPORTFOLIO ON STOCKPORTFOLIO.STOCKMASTER_ROWID = STOCKMASTER.ROWID 
		WHERE STOCKPORTFOLIO.MASTER_ROWID = 1 AND STOCKPORTFOLIO.STOCKMASTER_ROWID = 229 
		ORDER BY STOCKPORTFOLIO.STOCKMASTER_ROWID ASC, STOCKPORTFOLIO.PURCHASE_DATE ASC
		
		----to get stockdata for specific symbol and timestamp >= supplied date
SELECT ROWID as DAILYID, SYMBOL, EXCHANGENAME as EXCHANGE, TYPE, DATA_GRANULARITY, OPEN, HIGH, LOW, CLOSE, ADJ_CLOSE, VOLUME, 
		 strftime("%d-%m-%Y", TIMESTAMP ), TIMESTAMP
		FROM STOCKDATA 
				WHERE SYMBOL = 'BHARTIARTL' AND EXCHANGENAME = 'NS' AND DATA_GRANULARITY = '1d' AND TIMESTAMP >= '2021-11-04'
		ORDER BY TIMESTAMP ASC
		
delete from STOCKDATA where SYMBOL = 'BHARTIARTL'