Trying To find a Match in computed columns

[skosmicki]

I need to create an function similar to the "MATCH" function in Excel
that evaluates a number within a set of numbers and returns whether
there is a match. I have put the example of what I see in excel in the
check column. The "0" answer in the result column is in the fourth
account in the list. Somehow I need to loop through the accounts
comparing the result to the total and indicate a match in the check
column. It wouldn't even need to tell me the row number; it could be a
0 or 1.

account total result check
12377026 6.84 124.21
12377026 131.05 0 4
12377026 164.38 -33.33
12377026 0 131.05
12377026 78.71 52.34
12377167 -31.34 221.89
12377167 31.34 159.21
12377167 38.55 152 5
12377167 31.34 159.21
12377167 152 38.55
12377167 490.91 -300.36
12377167 0 190.55
12377167 0 190.55
12377167 -31.34 43.34
12377167 31.34 -19.34
12377167 38.55 -26.55
12377167 31.34 -19.34
12377167 152 -140
12377167 490.91 -478.91
12377167 0 12
12377167 0 12
12377363 47.05 84
12377363 131.05 0
12377363 -45.38 176.43
12377363 -47.05 178.1
12377363 47.04 84.01
12377363 -47.04 178.09
12377363 47.05 84
12377363 541.11 -410.06
12377363 0 131.05
12377363 672.15 -541.1
12377507 37.64 152.91

[Erland Sommarskog]

(sk*******@sfmc-gi.org) writes:

I need to create an function similar to the "MATCH" function in Excel
that evaluates a number within a set of numbers and returns whether
there is a match. I have put the example of what I see in excel in the
check column. The "0" answer in the result column is in the fourth
account in the list. Somehow I need to loop through the accounts
comparing the result to the total and indicate a match in the check
column. It wouldn't even need to tell me the row number; it could be a
0 or 1.

I'm afraid that the MATCH function is unknown to me. I tried to read
about it the Excel Help, but in a hurry I could not make much out of it.

I was trying understand the numbers. I looks bit like credit/debit,
but the names "total" and "result" indicates something else.

So I would suggest that you give a more detailed explaination of your
business problem.

An extra hint is that if you include a CREATE TABLE statment for your
table and INSERT statements with the sample data, you are likely to
get a tested solution.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 26 May 2006 12:07:40 -0700, sk*******@sfmc-gi.org wrote:

I need to create an function similar to the "MATCH" function in Excel
that evaluates a number within a set of numbers and returns whether
there is a match. I have put the example of what I see in excel in the
check column. The "0" answer in the result column is in the fourth
account in the list. (snip)

Hi skosmicki,

Fourth by what definition? I can see that it's fourth in the order yoou
wrote the rows, but I don't see any appparent logic in the ordering of
rows with the same account. Remember that SQL Server doesn't keep track
of the order in which rows are inserted - if that's relevant to you,
you'll have to add a column for it.
Somehow I need to loop through the accounts
comparing the result to the total and indicate a match in the check
column. It wouldn't even need to tell me the row number; it could be a
0 or 1.

Maybe something like this? (Untested - see www.aspfaq.com/5006 if you
prefer a tested reply)

SELECT a.account, a.total, a.result,
CASE WHEN b.account IS NOT NULL THEN 1 ELSE 0 END AS check
FROM YourTable AS a
LEFT OUTER JOIN YourTable AS b
ON a.account = b.account
AND a.result = b.total

--
Hugo Kornelis, SQL Server MVP

[skosmicki]

Thanks Hugo - that's exactly what I did. It's a much cleaner result
set then looping through all the transactions.
Thanks Erland for your advice on the CREATE TABLE - I'll do so next
time. Keep up the good work!
Sherry