Database

SELECT 
 
case i.ordStatus 
when 'Queued' then 'N'
when 'Filled' then 'A'
when 'Partial Filled' then 'A'
when 'Cancel' then 'C'
end AS 'ordStatus',
 
f.code AS 'exchCode', 
g.code AS 'companyCode', 
h.code AS 'branchCode',
c.code AS 'clientCode',
i.transType AS 'transType',
 
case i.ordStatus 
when 'Queued' then ((i.quantity * i.price * i.exchRate) / j.denomination)
when 'Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Partial Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Cancel' then ((i.adjust * i.price * i.exchRate) / j.denomination)
end AS 'ordAmount',
 
case i.ordStatus 
when 'Queued' then 0
when 'Filled' then 0
when 'Partial Filled' then 0
when 'Cancel' then 0
end AS 'matchAmount'
 
FROM TM_TradingProfile a 
LEFT JOIN TM_clientTradingProfile b 
ON a.id = b.id  
LEFT JOIN TM_Client c 
ON c.id = b.TM_client_fk 
LEFT JOIN BKL_User d 
ON d.id = c.id 
JOIN BKL_Authentication e 
ON c.id = e.BKL_user_fk 
LEFT JOIN RM_Exchange f
ON a.RM_exchange_fk = f.id
LEFT JOIN BKL_Company g
ON a.BKL_company_fk = g.id
LEFT JOIN BKL_Branch h
ON a.BKL_branch_fk=h.id
LEFT JOIN RM_MarketTransaction i
ON a.tradingAccountNumber = i.tradingAccNo
LEFT JOIN bkl_forexexch j
ON i.tradCurr = j.currencyCodeFrom
 
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
 
WHERE a.tradingAccountNumber='ST3273'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PARTNOTE]
GO

CREATE TABLE [dbo].[PARTNOTE] (
	[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NOTE_NUM] [float] NULL ,
	[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyDatabase].[dbo].[PARTNOTE]
GO

CREATE TABLE [MyDatabase].[dbo].[PARTNOTE] (
	[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NOTE_NUM] [float] NULL ,
	[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]