logic of sum() with joining the same table

hi everybody
I have a question about the sum() function. when I join two tabeles and one
of them is the main table which I used in the from statement, sum function I
used for the joined table is giving the sum incorrectly(it is governing time
s
the other joined tabele). how can i eleminate the problem? do I have to use
query hint or someting. if so how?
CREATE TABLE [order] (
[id] [int] NULL ,
[date_order] [datetime] NULL ,
[product] [char] (10) ,
[quantity] [int] NULL
)
go
CREATE TABLE [distribute] (
[id] [int] NULL ,
[date_distribute] [datetime] NULL ,
[product] [char] (10),
[quantity] [int] NULL
)
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
isnull(sum(d2.quantity),0)as forobservingdist,
isnull(sum(o2.quantity),0)as forobservingord
from [order] o1
left join [order] o2 on o1.id>=o2.id and o1.product=o2.product
left join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
left join Distribute d2 on d2.id<=d1.id and d2.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.product
the query below has the same logic with the above query. I used UDFs for the
sum functions.
when you run the queries, forobservingord column must be the same as
forobservingord in the results of the query below
CREATE FUNCTION getorderdogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(o2.quantity) from [order] o2 where o2.id<=@.id and
o2.product=@.product
RETURN @.sum
END
go
CREATE FUNCTION getdistributedogan
(@.id int,@.product nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
select @.sum = sum(d2.quantity) from Distribute d2 where d2.id<=@.id and
d2.product=@.product
RETURN isnull(@.sum,0)
END
select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
d1.date_distribute,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
,d1.date_distribute,d1.quantity,d1.productYou're description of the problem is not very clear. Firstly, neither of you
r
tables contains a primary key. This is critical to understanding why your su
m
function may be producing unexpected results. I'm assuming that the sum func
tion
is coming out too high because there are values being included multiple time
s.
In addition, your query formatting leaves something to be desired and makes
it
difficult to read the query.
Lastly, show us some sample data and result. It might help us understand wha
t
you are trying to achieve and why you have both tables joined twice in the
query.
Thomas|||hi thomas
the query is the basis of a fifo report. I didnt want to use a cursor and so
Iwrote such a query. I solved the problem with UDFs but i want to learn the
logic and how to use query hint for sum function
id is primary key for both tables
and here are the sample data:)
"In addition, your query formatting leaves something to be desired and makes
it
difficult to read the query." didnt understand that part. I would like to
change my query formating if you explain what is wrong.
tnx
insert into distribute (id,date_distribute,product,quantity) values
(51,'2005-01-01','aaa',10)
insert into distribute (id,date_distribute,product,quantity) values
(52,'2005-01-04','aaa',13)
insert into distribute (id,date_distribute,product,quantity) values
(53,'2005-01-05','aaa',3)
insert into distribute (id,date_distribute,product,quantity) values
(54,'2005-01-06','aaa',-2)
insert into distribute (id,date_distribute,product,quantity) values
(55,'2005-01-07','aaa',8)
insert into distribute (id,date_distribute,product,quantity) values
(56,'2005-01-08','aaa',45)
insert into distribute (id,date_distribute,product,quantity) values
(57,'2005-01-10','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(11,'2005-01-01','aaa',10)
insert into [order] (id,date_order,product,quantity) values
(12,'2005-01-03','aaa',20)
insert into [order] (id,date_order,product,quantity) values
(13,'2005-01-05','aaa',30)
insert into [order] (id,date_order,product,quantity) values
(14,'2005-01-08','aaa',15)
insert into [order] (id,date_order,product,quantity) values
(15,'2005-01-09','aaa',10)
"Thomas Coleman" wrote:

> You're description of the problem is not very clear. Firstly, neither of y
our
> tables contains a primary key. This is critical to understanding why your
sum
> function may be producing unexpected results. I'm assuming that the sum fu
nction
> is coming out too high because there are values being included multiple ti
mes.
> In addition, your query formatting leaves something to be desired and make
s it
> difficult to read the query.
> Lastly, show us some sample data and result. It might help us understand w
hat
> you are trying to achieve and why you have both tables joined twice in the
> query.
>
> Thomas
>
>|||> the query is the basis of a fifo report. I didnt want to use a cursor and sod">
> Iwrote such a query. I solved the problem with UDFs but i want to learn th
e
> logic and how to use query hint for sum function
> id is primary key for both tables
> and here are the sample data:)
Ok. The only thing missing now is exactly what is it that you want? What wou
ld
the results look like?
In addition, some clarity on the meaning of the two tables would be helpful.
Is
it the case that the Order table contains products that were ordered and the
Distribute table contains products that were actually distributed? What are
the
columns "ForObservingDist" and "ForObservingOrd" supposed to denote?

> "In addition, your query formatting leaves something to be desired and mak
es
> it
> difficult to read the query." didnt understand that part. I would like to
> change my query formating if you explain what is wrong.
1. You might consider at least using Pascal casing. Pascal casing means that
the
first letter of each word in a name is capitalized. e.g. Instead of:
forobservingdist you would have: ForObservingDist. This makes it much easier
to
read.
2. Include a space on either side of an operator. So instead of: o1.id>=o2.i
d
Do this: o1.id >= o2.id
3. Include a space after a comman. So instead of o1.id,o1.date_order,o1.prod
uct.
Do this: o1.id, o1.date_order, o1.product.
4. Include some indenting in your query. I generally indent each Join clause
and
each On clause. So re-written, your query would look like:
Select o1.id, o1.date_order, o1.product, o1.quantity
, d1.id As Distribute_id
, IsNull(Sum(d2.quantity), 0) As ForObservingDist
, IsNull(Sum(o2.quantity), 0) As ForObservingOrd
From [order] o1
Left Join [order] o2
On o1.id >= o2.id
And o1.product = o2.product
Left Join Distribute d1
On o1.date_order <= d1.date_distribute
And o1.product = d1.product
Left Join Distribute d2
On d2.id <= d1.id
And d2.product = d1.product
Group By o1.id, o1.date_order, o1.product, o1.quantity
, d1.id, d1.date_distribute, d1.quantity, d1.product
Placing commas at the beginning of a line is my personal preference because
I
make fewer "missing comma" mistakes. SQL purists would want keywords like
Select, Left, Join etc. in all caps so they might write this query like so (
with
commas at the end):
SELECT o1.id, o1.date_order, o1.product, o1.quantity,
d1.id As Distribute_id,
IsNull(Sum(d2.quantity), 0) As ForObservingDist,
IsNull(Sum(o2.quantity), 0) As ForObservingOrd
FROM [order] o1
LEFT JOIN [order] o2
ON o1.id >= o2.id
AND o1.product = o2.product
LEFT JOIN Distribute d1
ON o1.date_order <= d1.date_distribute
AND o1.product = d1.product
LEFT JOIN Distribute d2
ON d2.id <= d1.id
AND d2.product = d1.product
GROUP BY o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
HTH
Thomas|||>> [query formatting] .. didnt understand that part. I would like to
change my query formating if you explain what is wrong. <<
Get a copy of my SQL PROGRAMMING STYLE. I go into painful details and
explain why you use certain formatting techniques, based on readabilty
and eye movement. Years ago when I worked for AIRMIC, I did 6 months
of full time research on this and about a year of part-time follow up.|||Hi Thomas
first of all thanks for your advices about the query style. I will do my
best:))
simply what I want is the ForObservingOrd column must be the same as in the
query with UDFs. the Query with UDFs returns the results I want. I'm sing
the solution with query hint like hash match while left joining [Orders] o2
table if possible.
I've rewrite the question and Razvan suggested to use subqueries instead of
UDFs. it is working but ly it is not what I want.
tnx again
here is the suggestion of Razvan and my reply
You can achieve the same results (with better performance) by using
subqueries instead of UDF-s:
select o1.id, o1.date_order, o1.product,o1.quantity,
d1.id as Distribute_id, d1.date_distribute, (
select sum(d2.quantity) from Distribute d2
where d2.id<=d1.id and d2.product=d1.product
) as forobservingdist, (
select sum(o2.quantity) from [order] o2
where o2.id<=o1.id and o2.product=o1.product
) as forobservingord
from dbo.[order] o1
left outer join Distribute d1
on o1.date_order<=d1.date_distribute and o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity,
d1.id, d1.date_distribute, d1.quantity, d1.product
Razvan
--
tnx Razvan sure I didnt think this:))
do you have any info about using query hint works like that?
I changed the UDFs with subqueries and its working. I looked at the query
execution plan and it looks more simple with the udfs. are you sure this wil
l
work with better performance? probably you are:))
also plan of the query with subqueries shows many hash matches. Can't I use
query hint making hash matches with left join?
thanks again
also the last form of the query is like that
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
then
isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribute
dogan(d1.id,d1.product))
when
d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
then
(o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.
id,d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))>o1.quantity
then
o1.quantity
when
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))<0
then
0
else
(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product
))
end as remainingorder,
dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
dbo.getorderdogan(o1.id,o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having
((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produc
t))+isnull(d1.quantity,0)>0
and
o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.i
d,d1.product)))
or isnull(d1.quantity,0)=0
select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_id
, d1.date_distribute,
case when
0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
then
isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
when
d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
then
(o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product)))
else
isnull(d1.quantity,0)
end as distributed_quantity,
case when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))>o1.quantity
then
o1.quantity
when
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))<0
then
0
else
((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))
end as remainingorder,
(select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
d2.product=d1.product)as forobservingdist,
(select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)as forobservingord
from dbo.[order] o1
left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
o1.product=d1.product
group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
d1.date_distribute, d1.quantity, d1.product
having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
and o1.quantity>((select sum(o2.quantity) from [order] o2 where
o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
or isnull(d1.quantity,0)=0
"Thomas Coleman" wrote:

> Ok. The only thing missing now is exactly what is it that you want? What w
ould
> the results look like?
> In addition, some clarity on the meaning of the two tables would be helpfu
l. Is
> it the case that the Order table contains products that were ordered and t
he
> Distribute table contains products that were actually distributed? What ar
e the
> columns "ForObservingDist" and "ForObservingOrd" supposed to denote?
>
> 1. You might consider at least using Pascal casing. Pascal casing means th
at the
> first letter of each word in a name is capitalized. e.g. Instead of:
> forobservingdist you would have: ForObservingDist. This makes it much easi
er to
> read.
> 2. Include a space on either side of an operator. So instead of: o1.id>=o2
.id
> Do this: o1.id >= o2.id
> 3. Include a space after a comman. So instead of o1.id,o1.date_order,o1.pr
oduct.
> Do this: o1.id, o1.date_order, o1.product.
> 4. Include some indenting in your query. I generally indent each Join clau
se and
> each On clause. So re-written, your query would look like:
> Select o1.id, o1.date_order, o1.product, o1.quantity
> , d1.id As Distribute_id
> , IsNull(Sum(d2.quantity), 0) As ForObservingDist
> , IsNull(Sum(o2.quantity), 0) As ForObservingOrd
> From [order] o1
> Left Join [order] o2
> On o1.id >= o2.id
> And o1.product = o2.product
> Left Join Distribute d1
> On o1.date_order <= d1.date_distribute
> And o1.product = d1.product
> Left Join Distribute d2
> On d2.id <= d1.id
> And d2.product = d1.product
> Group By o1.id, o1.date_order, o1.product, o1.quantity
> , d1.id, d1.date_distribute, d1.quantity, d1.product
> Placing commas at the beginning of a line is my personal preference becaus
e I
> make fewer "missing comma" mistakes. SQL purists would want keywords like
> Select, Left, Join etc. in all caps so they might write this query like so
(with
> commas at the end):
> SELECT o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id As Distribute_id,
> IsNull(Sum(d2.quantity), 0) As ForObservingDist,
> IsNull(Sum(o2.quantity), 0) As ForObservingOrd
> FROM [order] o1
> LEFT JOIN [order] o2
> ON o1.id >= o2.id
> AND o1.product = o2.product
> LEFT JOIN Distribute d1
> ON o1.date_order <= d1.date_distribute
> AND o1.product = d1.product
> LEFT JOIN Distribute d2
> ON d2.id <= d1.id
> AND d2.product = d1.product
> GROUP BY o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
>
> HTH
>
> Thomas
>
>|||Can you provide a sample of the input data and the expected results? Include
enough data to illustrated why Razvan's solution does not work for you.
Thomas
"POKEMON" <POKEMON@.discussions.microsoft.com> wrote in message
news:B648F5AB-2BE0-4CE8-A5E2-D8B6C43FD60B@.microsoft.com...
> Hi Thomas
> first of all thanks for your advices about the query style. I will do my
> best:))
> simply what I want is the ForObservingOrd column must be the same as in th
e
> query with UDFs. the Query with UDFs returns the results I want. I'm si
ng
> the solution with query hint like hash match while left joining [Orders] o2
> table if possible.
> I've rewrite the question and Razvan suggested to use subqueries instead o
f
> UDFs. it is working but ly it is not what I want.
> tnx again
> here is the suggestion of Razvan and my reply
> You can achieve the same results (with better performance) by using
> subqueries instead of UDF-s:
> select o1.id, o1.date_order, o1.product,o1.quantity,
> d1.id as Distribute_id, d1.date_distribute, (
> select sum(d2.quantity) from Distribute d2
> where d2.id<=d1.id and d2.product=d1.product
> ) as forobservingdist, (
> select sum(o2.quantity) from [order] o2
> where o2.id<=o1.id and o2.product=o1.product
> ) as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1
> on o1.date_order<=d1.date_distribute and o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,
> d1.id, d1.date_distribute, d1.quantity, d1.product
> Razvan
> --
> tnx Razvan sure I didnt think this:))
> do you have any info about using query hint works like that?
> I changed the UDFs with subqueries and its working. I looked at the query
> execution plan and it looks more simple with the udfs. are you sure this w
ill
> work with better performance? probably you are:))
> also plan of the query with subqueries shows many hash matches. Can't I us
e
> query hint making hash matches with left join?
> thanks again
> also the last form of the query is like that
> select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_
id
> , d1.date_distribute,
> case when
> 0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.pro
duct))
> then
> isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistribu
tedogan(d1.id,d1.product))
> when
> d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistri
butedogan(d1.id,d1.product))
> then
> (o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d
1.id,d1.product)))
> else
> isnull(d1.quantity,0)
> end as distributed_quantity,
> case when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))>o1.quantity
> then
> o1.quantity
> when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))<0
> then
> 0
> else
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.produ
ct))
> end as remainingorder,
> dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
> dbo.getorderdogan(o1.id,o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
> d1.date_distribute, d1.quantity, d1.product
> having
> ((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.prod
uct))+isnull(d1.quantity,0)>0
> and
> o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1
.id,d1.product)))
> or isnull(d1.quantity,0)=0
>
> --
> select o1.id, o1.date_order, o1.product, o1.quantity, d1.id as Distribute_
id
> , d1.date_distribute,
> case when
> 0>((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))
> then
> isnull(d1.quantity,0)+((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
> when
> d1.quantity>o1.quantity-((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product))
> then
> (o1.quantity-((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id
> and o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 whe
re
> d2.id<=d1.id and d2.product=d1.product)))
> else
> isnull(d1.quantity,0)
> end as distributed_quantity,
> case when
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))>o1.quantity
> then
> o1.quantity
> when
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))<0
> then
> 0
> else
> ((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))
> end as remainingorder,
> (select sum(d2.quantity) from Distribute d2 where d2.id<=d1.id and
> d2.product=d1.product)as forobservingdist,
> (select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity, d1.id ,
> d1.date_distribute, d1.quantity, d1.product
> having (((select sum(o2.quantity) from [order] o2 where o2.id<=o1.id and
> o2.product=o1.product)-(select sum(d2.quantity) from Distribute d2 where
> d2.id<=d1.id and d2.product=d1.product))+isnull(d1.quantity,0)>0
> and o1.quantity>((select sum(o2.quantity) from [order] o2 where
> o2.id<=o1.id and o2.product=o1.product)-(select sum(d2.quantity) from
> Distribute d2 where d2.id<=d1.id and d2.product=d1.product)))
> or isnull(d1.quantity,0)=0
>
> "Thomas Coleman" wrote:
>|||I RESPECT
"--CELKO--" wrote:

> change my query formating if you explain what is wrong. <<
> Get a copy of my SQL PROGRAMMING STYLE. I go into painful details and
> explain why you use certain formatting techniques, based on readabilty
> and eye movement. Years ago when I worked for AIRMIC, I did 6 months
> of full time research on this and about a year of part-time follow up.
>|||sorry i think it is my mistake.
the UDF solution and subquery solution is workin as I wanted.
I just want a solution with query hint. Thats all.
"Thomas Coleman" wrote:

> Can you provide a sample of the input data and the expected results? Inclu
de
> enough data to illustrated why Razvan's solution does not work for you.
>
> Thomas
>
> "POKEMON" <POKEMON@.discussions.microsoft.com> wrote in message
> news:B648F5AB-2BE0-4CE8-A5E2-D8B6C43FD60B@.microsoft.com...
>
>