473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Trying To find a Match in computed columns

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

May 26 '06 #1
3 1606
(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****@sommarskog.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
May 26 '06 #2
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
May 26 '06 #3
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

May 30 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Paulo Andre Ortega Ribeiro | last post by:
I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname: LOWER(LEFT (fname,1) + lname) Is there any difference...
2
by: tperovic | last post by:
Using SS2K, I'm getting the following error while bulk inserting: Column 'warranty_expiration_date' cannot be modified because it is a computed column. Here is my bulk insert statement: ...
9
by: DMAC | last post by:
If i want to split a computed column into two or more columns based on the the length (its a varchar) of the computed column, how often will sql server determine what the computed column is?...
1
by: GJK | last post by:
In SQL Sever, do the size of computed columns gets added to the total size of the tables? Does SQL server stores the actual values in computed columns? Thanks _GJK
12
by: Andy Jeffries | last post by:
Hi all, I want to write a function that given two elements will make their heights equal to the larger element. I've had a go at it and it works, but it makes both their heights larger than...
4
by: Henning N?rg?rd | last post by:
I'm working as software developer mostely om SQL-server platform. On SQL-server we are using a lot of "Computed Columns" Does anyone know if that is possible to do in DB2 too ? I mean - define...
3
by: Raymond Du | last post by:
Hi, Can I have computed columns in a datagrid? If yes, how? TIA
0
by: am72de | last post by:
Hi all, I have two identical DataTables. One of the columns is a computed one. With a DataReader I try to copy the DataRows from one table to the other, but I get an InvalidOperationException,...
7
by: Aamir Mahmood | last post by:
Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.