FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE DailyTradeTransactions (
  [FundID] int
    ,[AssetID] int
    ,[TransactionTypeCode] varchar(20)
    ,[TradeDate] datetime2
    ,[NetAmountBase] money
    ,[BrokerNumber] varchar(20)
    ,[TradeID] int Primary Key
    ,[TradeIDCancel] int
);

--Load Test Data
INSERT INTO DailyTradeTransactions
VALUES
 (1,1,'SELL' ,'2024-01-20 01:00',10.12,'78912',0,null)
,(1,1,'SELL' ,'2024-01-20 01:01',10.12,'12345',1,null)
,(1,1,'SELL' ,'2024-01-20 01:02',10.12,'78912',2,null)
,(1,1,'SELL' ,'2024-01-20 01:03',10.12,'12345',3,2)
,(1,1,'CSELL','2024-01-20 01:04',10.12,'78912',4,null)
,(1,1,'CSELL','2024-01-20 01:05',10.12,'12345',5,null)
,(1,1,'CSELL','2024-01-20 01:06',10.12,'78912',6,5)
,(1,1,'CSELL','2024-01-20 01:07',10.12,'78912',7,null)
,(1,1,'CSELL','2024-01-20 01:08',10.12,'78912',8,null)

DECLARE @StartDate datetime2 = '2024-01-20'
		,@EndDate datetime2 = '2024-01-23'

SELECT     
    [FundID]
    ,[AssetID]
    ,[TransactionTypeCode]
    ,[TradeDate]
    ,[NetAmountBase]
    ,[BrokerNumber]
    ,[TradeID]
    ,[TradeIDCancel]
FROM [DailyTradeTransactions] t
WHERE TransactionTypeCode IN ('SELL','CSELL')
    AND TradeDate BETWEEN @StartDate AND @EndDate
    AND BrokerNumber NOT IN ('12345','23456','34567','45678','56789','67891')
    AND TradeIDCancel IS NULL
    AND NOT EXISTS (select 1 from DailyTradeTransactions ti where t.TradeID = ti.TradeIDCancel)

FundID	AssetID	TransactionTypeCode	TradeDate	NetAmountBase	BrokerNumber	TradeID	TradeIDCancel
1	1	SELL	2024-01-20 01:00:00.0000000	10.1200	78912	0	NULL
1	1	CSELL	2024-01-20 01:04:00.0000000	10.1200	78912	4	NULL
1	1	CSELL	2024-01-20 01:07:00.0000000	10.1200	78912	7	NULL
1	1	CSELL	2024-01-20 01:08:00.0000000	10.1200	78912	8	NULL

(9 rows affected)

(4 rows affected)