hi
i have the following select statement that pulls back the records i want. but there are duplicate records based on field [Instr_nsaddr] i just want to delete one of them out doesnt matter which one. whats the best way to do it
Select
Fund_Fund_Code,
[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
[Instr_nsaddr],
[Portfolio_PB Legal Eyntit],
[Tax Lot_Quantity1] = sum([Tax Lot_Quantity])
from dbo.HegdeServWorkingTaxLotTableName
where [Instr_Expiry Date] = [Instr_Expiry Date]
and Portfolio_Clearer = Portfolio_Clearer
and Instr_Instrument = Instr_Instrument
and Instr_nsaddr = Instr_nsaddr
and [Portfolio_PB Account Number] = [Portfolio_PB Account Number]
and [Tax Lot_Transaction Code] = 'Buy' or [Tax Lot_Transaction Code] = 'SellShort'
group by Fund_Fund_Code,[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
[Instr_nsaddr],
[Portfolio_PB Legal Eyntit]
having sum([Tax Lot_Quantity]) = 0
dont think it will work but not sure
i tried this
delete from dbo.HegdeServWorkingTaxLotTableName where [Instr_nsaddr] not in
(
Select
Fund_Fund_Code,
[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
min([Instr_nsaddr]),
[Portfolio_PB Legal Eyntit],
[Tax Lot_Quantity1] = sum([Tax Lot_Quantity])
from dbo.HegdeServWorkingTaxLotTableName
where [Instr_Expiry Date] = [Instr_Expiry Date]
and Portfolio_Clearer = Portfolio_Clearer
and Instr_Instrument = Instr_Instrument
and Instr_nsaddr = Instr_nsaddr
and [Portfolio_PB Account Number] = [Portfolio_PB Account Number]
and [Tax Lot_Transaction Code] = 'Buy' or [Tax Lot_Transaction Code] = 'SellShort'
group by Fund_Fund_Code,[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
[Instr_nsaddr],
[Portfolio_PB Legal Eyntit]
having sum([Tax Lot_Quantity]) = 0 )
but go the following error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error?
Can you add a test to the main table, make all 'Y' and just change the additional record to 'N', then you can select excluding 'N'
I mentioned this in a previous thread that you (IIRC??) posted.
What are you expecting that this will do?
All these tests are TRUE for ALL rows ...