Can anyone suggest a good approach to this problem?
I need to match transactions from two tables on several columns. When
I have a match, I need to set a match flag and a match number in each
table. There can be duplicate transactions in either table. If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.
Results:
table 1
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
5 5 Y 3
5 5
table 2
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
1 2
5 5 Y 3>> Can anyone suggest a good approach to this problem?
Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006
Anith|||I have two temp tables loaded with the transactions I want to match
with the other table. They need to match on mid, tran_date, amount,
and card. When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction. It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table. If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load. I'll be matching on several thousands of transactions.
Let me know if you need more information. Thanks.
create table #setl (
mid char(6) not null,
load_number int not null,
detail_number int not null,
batch_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
create table #debit (
mid char(6) not null,
load_number int not null,
detail_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
desired results:
#setl
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2
2005-09-30 .01 4444 null null
#debit
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2|||First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.
Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us
If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.
Anith
Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts
Saturday, February 25, 2012
Matching on null?
I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one of
the columns being null. For instance, here's one of the problem rows from the
transactions...
item id shelf owner group bank sum
15214 NULL300220MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214NULL300220MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
|||"Mike C#" wrote:
> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one of
the columns being null. For instance, here's one of the problem rows from the
transactions...
item id shelf owner group bank sum
15214 NULL300220MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214NULL300220MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))
|||"Mike C#" wrote:
> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury
Matching on null?
I'm sure I already know the answer to this, but...
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one o
f
the columns being null. For instance, here's one of the problem rows from th
e
transactions...
item id shelf owner group bank sum
15214 NULL 300 220 MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214 NULL 300 220 MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))|||"Mike C#" wrote:
> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury
I have two tables, one of transactions and another of inventory. The
inventory table is essentially a SUMmed GROUPed version of the transactions.
We do a lot of queries on inventory though, and since it only changes once a
night it makes sense to keep the rollup.
Anyway one of the users noticed that some of the inventory rows are missing
information. After a little poking about, I found that this was due to one o
f
the columns being null. For instance, here's one of the problem rows from th
e
transactions...
item id shelf owner group bank sum
15214 NULL 300 220 MS 8.0
That NULL in the second column is perfectly fine, from a data point of view.
Now compare this with the same row in the inventory table...
15214 NULL 300 220 MS
So why is it that when I try to insert that 8.0 into the inventory table it
fails to match? If I simply remove the = on that second column it works, but
that is only valid in the case they are NULL, which is the exception.
I know this has something to do with a long debate about NULL in SQL, but I
don't pretend to understand it. What's the solution here? Will this work...
WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))NULL is not equal to NULL. So the NULL in your first table is not equal to
any NULL in the second table.
You could do something like what you proposed below, or you could do
something like this:
WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Here I'm assuming that -1 is an invalid shelf # (which may or may not be the
case ... substitute your own value here if it is).
BTW, what are the primary keys on those two tables? It sort of looks like
it should be (item id, shelf) or some combination including those (based
just on what you posted), in which case shelf shouldn't be NULL anyway.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6652ED0C-74D0-413E-BF05-DDDC2F4F7E7A@.microsoft.com...
> I'm sure I already know the answer to this, but...
> I have two tables, one of transactions and another of inventory. The
> inventory table is essentially a SUMmed GROUPed version of the
> transactions.
> We do a lot of queries on inventory though, and since it only changes once
> a
> night it makes sense to keep the rollup.
> Anyway one of the users noticed that some of the inventory rows are
> missing
> information. After a little poking about, I found that this was due to one
> of
> the columns being null. For instance, here's one of the problem rows from
> the
> transactions...
> item id shelf owner group bank sum
> 15214 NULL 300 220 MS 8.0
> That NULL in the second column is perfectly fine, from a data point of
> view.
> Now compare this with the same row in the inventory table...
> 15214 NULL 300 220 MS
> So why is it that when I try to insert that 8.0 into the inventory table
> it
> fails to match? If I simply remove the = on that second column it works,
> but
> that is only valid in the case they are NULL, which is the exception.
> I know this has something to do with a long debate about NULL in SQL, but
> I
> don't pretend to understand it. What's the solution here? Will this
> work...
> WHERE (p.shelfId= m.shelfIdOR (p.shelfId IS NULL and m.shelfId IS NULL))|||"Mike C#" wrote:
> WHERE COALESCE(p.shelfId, -1) = COALESCE(m.shelfId, -1)
Ohhh, I like that. In this particular case the Id is (not my fault!) a
string, so '' would likely be the best solution. Thanks!
Maury
Subscribe to:
Comments (Atom)