SQLTeam.com | Weblogs | Forums

Need to query the forwards file for instances of records where 2 records have the following criteria


#1

i have been asked to do trhis

Expiry Date are equal
Portfolio/Clearer are equal
Tax Lot/Transaction Code – one record has Buy, the other SellShort
Instr/Instrument are equal
Instr/insaddr are equal
Portfolio/PB Account Number are equal
Tax Lot/Quantity – netting (summing) these across the 2 records results in Zero

i did up a quick select query to find the records first but getting an error on the sum. what do i do to get the query working.

Select * 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'
and SUM([Tax Lot_Quantity]) = '0'

#2

This perhaps?

Select * 
from dbo.HegdeServWorkingTaxLotTableName AS T1
    JOIN dbo.HegdeServWorkingTaxLotTableName AS T2
	ON T1.PKey <> T2.PKey
where 
and T1.[Instr_Expiry Date] = T2.[Instr_Expiry Date]
and T1.Portfolio_Clearer = T2.Portfolio_Clearer
and T1.Instr_Instrument = T2.Instr_Instrument
and T1.Instr_nsaddr = T2.Instr_nsaddr
and T1.[Portfolio_PB Account Number] = T2.[Portfolio_PB Account Number]
and T1.[Tax Lot_Transaction Code] = 'Buy' AND T2.[Tax Lot_Transaction Code] = 'SellShort'
and T1.[Tax Lot_Quantity] + T2.[Tax Lot_Quantity] = 0

#3

NOTE: if your collation is case INsensitive then the equality tests will match if there is a difference in case, as they will if one has trailing spaces and the other not. I presume this would not matter, but if it must be "exact match" then you could force a Binary Collation on the equality tests to make them more restrictive. I think to check if one has more trailing spaces than the other you have to do a DATALENGTH() test (I don't think Binary Collation alone is enough)


#4

HI THANKS FOR REPLY

this pulled back the rows im looking for

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]

How would i get my update to work with this then if i wanted the update to be something like this based on the above results

Update dbo.HegdeServWorkingTaxLotTableName
set [DM2 Code] ='TEST'
WHERE [Tax Lot_Quantity] = '0.00'

#5
with cte as (
<copy query from previous post>
)

update cte
set [DM2 Code] ='TEST'
WHERE [Tax Lot_Quantity] = '0.00'

#6

Hi thanks for reply

im getting
Invalid column name 'DM2 Code'.


#7

add that column to the select statement


#8

i did and i get the fowling error

Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.


#9

remove the group by. You're only using it to get DISTINCT results (and the DISTINCT keyword is easier!) but that doesn't matter when you want to update rows


#10

get this error if i remove the group by clause
Msg 8120, Level 16, State 1, Line 3
Column 'dbo.HegdeServWorkingTaxLotTableName.Fund_Fund_Code ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


#11

remove the line

[Tax Lot_Quantity1] = sum([Tax Lot_Quantity])


#12

how will i no what values are equal to zero when they are sumed if i dont use the sum


#13

well your update statement doesn't refer to the sum! it refers to the individual column.


#14

i tried it a different way. the select query gets me the rows back i expect but the updates updates every single row. any idea why

update dbo.HegdeServWorkingTaxLotTableName
Set [DM2 Code] = 'TEST1'
where Instr_nsaddr IN
(
SELECT a.Instr_nsaddr from dbo.HegdeServWorkingTaxLotTableName a
FULL OUTER JOIN dbo.HegdeServWorkingTaxLotTableName b
ON a.Instr_nsaddr = b.Instr_nsaddr
AND a.[Instr_Expiry Date] = b.[Instr_Expiry Date]
AND a.Portfolio_Clearer = b.Portfolio_Clearer
AND a.Instr_Instrument = b.Instr_Instrument
AND a.[Portfolio_PB Account Number] = b.[Portfolio_PB Account Number]
GROUP BY a.Instr_nsaddr


)

#15

This (as I expect you have found out) isn't going to do anything useful - you are comparing a column to itself, so they will always be equal.

I can't see that you need a FULL OUTER JOIN - and using one probably makes the whole deal far more complicated than it needs to be (accounting for rows that appear to be in one, but not the other).

Was there something wrong with the self-joining table suggestion I made earlier?


#16

The only test is that the current Instr_nsaddr is returned by the subquery. But in the subquery you have a full outer join! So every Instr_nsaddr in the table would be returned. you probably want something like:

update tax
Set [DM2 Code] = 'TEST1'
from dbo.HegdeServWorkingTaxLotTableName as tax
join (
<your original query, including the aggregation and adding>
having sum([Tax Lot_Quantity]) = 0
) sub
on tax. Instr_nsaddr  = sub. Instr_nsaddr 

of course I can't test this since I don't have your tables, but that's the general idea