[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Help with a view

Rob

3/26/2007 1:59:00 PM

CREATE TABLE [dbo].[tblManyToMany] (
[Ord] [char] (10) NULL ,
[Inv] [char] (10) NULL ,
[FrtKey] [char] (10) NULL
) ON [PRIMARY]
GO

INSERT INTO tblManyToMany Values('1','1','XYZ')
INSERT INTO tblManyToMany Values('1','1','ABC')
INSERT INTO tblManyToMany Values('1','1','DEF')
INSERT INTO tblManyToMany Values('3','4','XYZ')
INSERT INTO tblManyToMany Values('4','7','ABC')
INSERT INTO tblManyToMany Values('2','3','ZZZ')
INSERT INTO tblManyToMany Values('3','5','ZZZ')
INSERT INTO tblManyToMany Values('4','8','ZAK')

Given the above, I am trying to match Freight bills with invoices. There
is a many to many relationship.

What I would like to do is create goups such that all like Ord + Inv are
grouped with any and all instances of FrtKey.

AKA...

('1','1','XYZ')
('1','1','ABC')
('1','1','DEF')
('3','4','XYZ')
('4','7','ABC')

The above would represent 1 group because they are all related. I want to
keep all freight bills together, while at the same time, keep all Ord + Inv
together.

Any Ideas ?
7 Answers

masri999

3/26/2007 2:58:00 PM

0

On Mar 26, 6:59 pm, "Rob" <r...@yahoo.com> wrote:
> CREATE TABLE [dbo].[tblManyToMany] (
> [Ord] [char] (10) NULL ,
> [Inv] [char] (10) NULL ,
> [FrtKey] [char] (10) NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tblManyToMany Values('1','1','XYZ')
> INSERT INTO tblManyToMany Values('1','1','ABC')
> INSERT INTO tblManyToMany Values('1','1','DEF')
> INSERT INTO tblManyToMany Values('3','4','XYZ')
> INSERT INTO tblManyToMany Values('4','7','ABC')
> INSERT INTO tblManyToMany Values('2','3','ZZZ')
> INSERT INTO tblManyToMany Values('3','5','ZZZ')
> INSERT INTO tblManyToMany Values('4','8','ZAK')
>
> Given the above, I am trying to match Freight bills with invoices. There
> is a many to many relationship.
>
> What I would like to do is create goups such that all like Ord + Inv are
> grouped with any and all instances of FrtKey.
>
> AKA...
>
> ('1','1','XYZ')
> ('1','1','ABC')
> ('1','1','DEF')
> ('3','4','XYZ')
> ('4','7','ABC')
>
> The above would represent 1 group because they are all related. I want to
> keep all freight bills together, while at the same time, keep all Ord + Inv
> together.
>
> Any Ideas ?

Your request is not clear . Can you elobarate with example what you
want ?

Rob

3/26/2007 3:30:00 PM

0

Hi,

Sorry I was not clear... hope this helps...
1. The table has NO primary key and is composed of the following...
2. There are combinations of Orders (Ord) and Invoices (Inv)
3. There are Freight Bill numbers (FrtKey)

The same FryKey may appear on many combinations of Ord + Inv
There may be several instances of Ord + Inv with different FrtKeys

I am trying to establish Group numbers that would link all the related ones
together...

Notice that Ord 1 , Inv 1 has 3 different FrtKey values (XYZ, ABC, and DEF)
Notice that Ord 3, Inv 4 has 1 different FrtKey value (XYZ)
- since XYZ appears on Ord 1, Inv 1 it needs to be grouped with it
Notice that Ord 4, Inv 7 has 1 different FrtKey value (ABC)
- since ABC appears on Ord 1, Inv 1 it needs to be grouped with it

The result set I would like to achieve in this case would be

Ord,Inv, FrtKey
('1','1','XYZ') Group = 1
('1','1','ABC') Group = 1
('1','1','DEF') Group = 1
('3','4','XYZ') Group = 1
('4','7','ABC') Group = 1

('2','3','ZZZ') Group=2
('3','5','ZZZ') Group=2

('4','8','ZAK') Group=3

Thanks !




"M A Srinivas" <masri999@gmail.com> wrote in message
news:1174921100.436392.43690@y66g2000hsf.googlegroups.com...
> On Mar 26, 6:59 pm, "Rob" <r...@yahoo.com> wrote:
>> CREATE TABLE [dbo].[tblManyToMany] (
>> [Ord] [char] (10) NULL ,
>> [Inv] [char] (10) NULL ,
>> [FrtKey] [char] (10) NULL
>> ) ON [PRIMARY]
>> GO
>>
>> INSERT INTO tblManyToMany Values('1','1','XYZ')
>> INSERT INTO tblManyToMany Values('1','1','ABC')
>> INSERT INTO tblManyToMany Values('1','1','DEF')
>> INSERT INTO tblManyToMany Values('3','4','XYZ')
>> INSERT INTO tblManyToMany Values('4','7','ABC')
>> INSERT INTO tblManyToMany Values('2','3','ZZZ')
>> INSERT INTO tblManyToMany Values('3','5','ZZZ')
>> INSERT INTO tblManyToMany Values('4','8','ZAK')
>>
>> Given the above, I am trying to match Freight bills with invoices.
>> There
>> is a many to many relationship.
>>
>> What I would like to do is create goups such that all like Ord + Inv are
>> grouped with any and all instances of FrtKey.
>>
>> AKA...
>>
>> ('1','1','XYZ')
>> ('1','1','ABC')
>> ('1','1','DEF')
>> ('3','4','XYZ')
>> ('4','7','ABC')
>>
>> The above would represent 1 group because they are all related. I want
>> to
>> keep all freight bills together, while at the same time, keep all Ord +
>> Inv
>> together.
>>
>> Any Ideas ?
>
> Your request is not clear . Can you elobarate with example what you
> want ?
>


markc600

3/26/2007 4:02:00 PM

0

For SQL Server 2005

WITH Grps1(Ord,Inv,FrtKey,Grp) AS
(SELECT Ord,Inv,FrtKey,
RANK() OVER(ORDER BY Ord,Inv)
FROM tblManyToMany),
Grps2(Ord,Inv,FrtKey,Grp) AS
(SELECT Ord,Inv,FrtKey,Grp
FROM Grps1
UNION ALL
SELECT b.Ord,b.Inv,b.FrtKey,a.Grp
FROM Grps1 a
INNER JOIN Grps1 b ON b.FrtKey=a.FrtKey AND b.Grp>a.Grp)
SELECT Ord,Inv,FrtKey,
DENSE_RANK() OVER(ORDER BY MIN(Grp)) AS Grp
FROM Grps2
GROUP BY Ord,Inv,FrtKey
ORDER BY Grp,Ord,Inv

Rob

3/26/2007 4:43:00 PM

0

Wow... mind if I ask how i might insert that result set into a table...


<markc600@hotmail.com> wrote in message
news:1174924906.478928.67930@l77g2000hsb.googlegroups.com...
> For SQL Server 2005
>
> WITH Grps1(Ord,Inv,FrtKey,Grp) AS
> (SELECT Ord,Inv,FrtKey,
> RANK() OVER(ORDER BY Ord,Inv)
> FROM tblManyToMany),
> Grps2(Ord,Inv,FrtKey,Grp) AS
> (SELECT Ord,Inv,FrtKey,Grp
> FROM Grps1
> UNION ALL
> SELECT b.Ord,b.Inv,b.FrtKey,a.Grp
> FROM Grps1 a
> INNER JOIN Grps1 b ON b.FrtKey=a.FrtKey AND b.Grp>a.Grp)
> SELECT Ord,Inv,FrtKey,
> DENSE_RANK() OVER(ORDER BY MIN(Grp)) AS Grp
> FROM Grps2
> GROUP BY Ord,Inv,FrtKey
> ORDER BY Grp,Ord,Inv
>


Rob

3/26/2007 4:49:00 PM

0

Never mind... I needed to lose the order by...
Thanks for your help...

"Rob" <robc1@yahoo.com> wrote in message
news:hoqdnYv9mtKRZZrbnZ2dnUVZ_o-knZ2d@comcast.com...
> Wow... mind if I ask how i might insert that result set into a table...
>
>
> <markc600@hotmail.com> wrote in message
> news:1174924906.478928.67930@l77g2000hsb.googlegroups.com...
>> For SQL Server 2005
>>
>> WITH Grps1(Ord,Inv,FrtKey,Grp) AS
>> (SELECT Ord,Inv,FrtKey,
>> RANK() OVER(ORDER BY Ord,Inv)
>> FROM tblManyToMany),
>> Grps2(Ord,Inv,FrtKey,Grp) AS
>> (SELECT Ord,Inv,FrtKey,Grp
>> FROM Grps1
>> UNION ALL
>> SELECT b.Ord,b.Inv,b.FrtKey,a.Grp
>> FROM Grps1 a
>> INNER JOIN Grps1 b ON b.FrtKey=a.FrtKey AND b.Grp>a.Grp)
>> SELECT Ord,Inv,FrtKey,
>> DENSE_RANK() OVER(ORDER BY MIN(Grp)) AS Grp
>> FROM Grps2
>> GROUP BY Ord,Inv,FrtKey
>> ORDER BY Grp,Ord,Inv
>>
>
>


Rob

3/26/2007 5:28:00 PM

0

Hi,

This came close to accomplishing what I need, but I still found duplicate
Grp numbers for the same combination of Ord + Inv...

I still must not be providing a good explanation...

If you Insert these rows Notice that Ord '236904' Inv '038301' appears with
2 diferent Grp numbers...

Maybe this cannot be done except via some procedural code...

insert into tblManyToMany values ('215398','036745','257855131')
insert into tblManyToMany values ('236904','038301','257842306')
insert into tblManyToMany values ('236904','038301','257855131')
insert into tblManyToMany values ('236904','036748','257855131')
insert into tblManyToMany values ('239357','055301','257855131')
insert into tblManyToMany values ('239357','036750','257855131')
insert into tblManyToMany values ('244791','036754','257855131')
insert into tblManyToMany values ('247778','039018','257855131')
insert into tblManyToMany values ('247778','036514','257855131')
insert into tblManyToMany values ('253103','036765','257855131')
insert into tblManyToMany values ('253103','042490','257855131')
insert into tblManyToMany values ('253103','055309','257855131')
insert into tblManyToMany values ('809801','109581','257855131')

This data is very hard to work with...

Thanks !

<markc600@hotmail.com> wrote in message
news:1174924906.478928.67930@l77g2000hsb.googlegroups.com...
> For SQL Server 2005
>
> WITH Grps1(Ord,Inv,FrtKey,Grp) AS
> (SELECT Ord,Inv,FrtKey,
> RANK() OVER(ORDER BY Ord,Inv)
> FROM tblManyToMany),
> Grps2(Ord,Inv,FrtKey,Grp) AS
> (SELECT Ord,Inv,FrtKey,Grp
> FROM Grps1
> UNION ALL
> SELECT b.Ord,b.Inv,b.FrtKey,a.Grp
> FROM Grps1 a
> INNER JOIN Grps1 b ON b.FrtKey=a.FrtKey AND b.Grp>a.Grp)
> SELECT Ord,Inv,FrtKey,
> DENSE_RANK() OVER(ORDER BY MIN(Grp)) AS Grp
> FROM Grps2
> GROUP BY Ord,Inv,FrtKey
> ORDER BY Grp,Ord,Inv
>


markc600

3/27/2007 2:41:00 PM

0

I think this a more complex that I first thought. I expect there
is a simpler (and quicker) solution than this.

WITH Grps(Ord,Inv,FrtKey,Grp) AS
(SELECT Ord,Inv,FrtKey,
DENSE_RANK() OVER(ORDER BY Ord,Inv)
FROM tblManyToMany),
Recur(StFrtKey,StGrp,FrtKey,Grp) AS
(SELECT FrtKey,Grp,FrtKey,Grp
FROM Grps
UNION ALL
SELECT a.StFrtKey,a.StGrp,b.FrtKey,b.Grp
FROM Recur a
INNER JOIN Grps b ON b.FrtKey=a.FrtKey AND b.Grp<a.Grp
UNION ALL
SELECT a.StFrtKey,a.StGrp,c.FrtKey,c.Grp
FROM Recur a
INNER JOIN Grps b ON b.FrtKey=a.FrtKey AND b.Grp<a.Grp
INNER JOIN Grps c ON c.FrtKey not in (b.FrtKey,a.FrtKey) AND
c.Grp=b.Grp
)
SELECT g.Ord,
g.Inv,
g.FrtKey,
DENSE_RANK() OVER(ORDER BY MIN(r.Grp)) AS Grp
FROM Grps g
INNER JOIN Recur r ON g.Grp=r.StGrp
GROUP BY g.Ord,g.Inv,g.FrtKey
ORDER BY Grp,Ord,Inv,FrtKey