"Andy in S. Jersey" wrote:

> This post continues much of the work done in the thread:
>
> Subject: UDF that takes a UDT as a parameter (CLR)
> (Kent has been very helpful)
>
> but is a little different
>
> I have created a UDT that essentially represents an interval between two
> integers.
> (the code for the UDT can be found at the thread above)
>
> An example is below:
>
> 4:10
>
> I can now use this type in a table.  Let's say we have a table below:
>
> interval (column of type IntervalUDT
> _______
> 4:10
> 5:10
> 9:10
> 6:10
>
> I would like to create some operator that would collapse these intervals
> into a union of them yielding the table:
>
> interval
> ______
> 4:10
>
> Another example would be:
>
> interval
> ______
> 2:4
> 8:10
> 3:3
> 9:10
>
> The union (or collapsing) in this case would be:
>
> interval
> ______
> 2:4
> 8:10
>
> Notice you don't necessarily have to return a single row every time, you
> could return one row, or just as many rows as you started with. 
>
> I have already created a UDF that takes two interval types and returns a UDT:
>
> public static IntervalUDT UnionUDF(IntervalUDT i1, IntervalUDT i2)
>
> but I would like to expand this to include the operator to the table level. 
>
> Do I need to create a TVF via
>
> [SqlFunction(FillRowMethodName="RowMethod",TableDefinition="table def")]
>
> ?
>
> Thanks in Advance
>

"Andy in S. Jersey" wrote:

> Just wanted to get everyone up to date on what I have been trying.  I did get
> the TVF code below to return the values I add via ilist.Add.
>
> [SqlFunction(FillRowMethodName = "GetRows2", TableDefinition = "v
> nvarchar(50)")]
>
>     public static IEnumerable UnionTVF (IntervalUDT A, IntervalUDT B)
>     {
>         List<string> alist = new List<string>();
>         List<string> ilist = new List<string>();
>         ilist.Add("2:4");
>         ilist.Add("6:10");
>         return (ilist);
>     }
>
>
>     public static void GetRows2(object o, out SqlString v)
>     {
>         v = (o == null ? SqlString.Null : new SqlString((string)(o)));
>     }
>
> No calculations are going on, but at least I got something that looks like
> an interval, but in reality it is just nvarchar(50)
>
> "Andy in S. Jersey" wrote:
>
> > This post continues much of the work done in the thread:
> >
> > Subject: UDF that takes a UDT as a parameter (CLR)
> > (Kent has been very helpful)
> >
> > but is a little different
> >
> > I have created a UDT that essentially represents an interval between two
> > integers.
> > (the code for the UDT can be found at the thread above)
> >
> > An example is below:
> >
> > 4:10
> >
> > I can now use this type in a table.  Let's say we have a table below:
> >
> > interval (column of type IntervalUDT
> > _______
> > 4:10
> > 5:10
> > 9:10
> > 6:10
> >
> > I would like to create some operator that would collapse these intervals
> > into a union of them yielding the table:
> >
> > interval
> > ______
> > 4:10
> >
> > Another example would be:
> >
> > interval
> > ______
> > 2:4
> > 8:10
> > 3:3
> > 9:10
> >
> > The union (or collapsing) in this case would be:
> >
> > interval
> > ______
> > 2:4
> > 8:10
> >
> > Notice you don't necessarily have to return a single row every time, you
> > could return one row, or just as many rows as you started with. 
> >
> > I have already created a UDF that takes two interval types and returns a UDT:
> >
> > public static IntervalUDT UnionUDF(IntervalUDT i1, IntervalUDT i2)
> >
> > but I would like to expand this to include the operator to the table level. 
> >
> > Do I need to create a TVF via
> >
> > [SqlFunction(FillRowMethodName="RowMethod",TableDefinition="table def")]
> >
> > ?
> >
> > Thanks in Advance
> >

"John Bell" wrote:

> Hi
>
> Itzik Ben-Gan wrote some articles in SQL Server magazine about calculating
> intervals maybe they will help! They were based on holding a lower and upper
> value separately, which I think your UDT should do and have a udt.lower() and
> udt.upper() method to get these value.
>
> John
>
> "Andy in S. Jersey" wrote:
>
> > Just wanted to get everyone up to date on what I have been trying.  I did get
> > the TVF code below to return the values I add via ilist.Add.
> >
> > [SqlFunction(FillRowMethodName = "GetRows2", TableDefinition = "v
> > nvarchar(50)")]
> >
> >     public static IEnumerable UnionTVF (IntervalUDT A, IntervalUDT B)
> >     {
> >         List<string> alist = new List<string>();
> >         List<string> ilist = new List<string>();
> >         ilist.Add("2:4");
> >         ilist.Add("6:10");
> >         return (ilist);
> >     }
> >
> >
> >     public static void GetRows2(object o, out SqlString v)
> >     {
> >         v = (o == null ? SqlString.Null : new SqlString((string)(o)));
> >     }
> >
> > No calculations are going on, but at least I got something that looks like
> > an interval, but in reality it is just nvarchar(50)
> >
> > "Andy in S. Jersey" wrote:
> >
> > > This post continues much of the work done in the thread:
> > >
> > > Subject: UDF that takes a UDT as a parameter (CLR)
> > > (Kent has been very helpful)
> > >
> > > but is a little different
> > >
> > > I have created a UDT that essentially represents an interval between two
> > > integers.
> > > (the code for the UDT can be found at the thread above)
> > >
> > > An example is below:
> > >
> > > 4:10
> > >
> > > I can now use this type in a table.  Let's say we have a table below:
> > >
> > > interval (column of type IntervalUDT
> > > _______
> > > 4:10
> > > 5:10
> > > 9:10
> > > 6:10
> > >
> > > I would like to create some operator that would collapse these intervals
> > > into a union of them yielding the table:
> > >
> > > interval
> > > ______
> > > 4:10
> > >
> > > Another example would be:
> > >
> > > interval
> > > ______
> > > 2:4
> > > 8:10
> > > 3:3
> > > 9:10
> > >
> > > The union (or collapsing) in this case would be:
> > >
> > > interval
> > > ______
> > > 2:4
> > > 8:10
> > >
> > > Notice you don't necessarily have to return a single row every time, you
> > > could return one row, or just as many rows as you started with. 
> > >
> > > I have already created a UDF that takes two interval types and returns a UDT:
> > >
> > > public static IntervalUDT UnionUDF(IntervalUDT i1, IntervalUDT i2)
> > >
> > > but I would like to expand this to include the operator to the table level. 
> > >
> > > Do I need to create a TVF via
> > >
> > > [SqlFunction(FillRowMethodName="RowMethod",TableDefinition="table def")]
> > >
> > > ?
> > >
> > > Thanks in Advance
> > >

"Andy in S. Jersey" wrote:

> This post continues much of the work done in the thread:
>
> Subject: UDF that takes a UDT as a parameter (CLR)
> (Kent has been very helpful)
>
> but is a little different
>
> I have created a UDT that essentially represents an interval between two
> integers.
> (the code for the UDT can be found at the thread above)
>
> An example is below:
>
> 4:10
>
> I can now use this type in a table.  Let's say we have a table below:
>
> interval (column of type IntervalUDT
> _______
> 4:10
> 5:10
> 9:10
> 6:10
>
> I would like to create some operator that would collapse these intervals
> into a union of them yielding the table:
>
> interval
> ______
> 4:10
>
> Another example would be:
>
> interval
> ______
> 2:4
> 8:10
> 3:3
> 9:10
>
> The union (or collapsing) in this case would be:
>
> interval
> ______
> 2:4
> 8:10
>
> Notice you don't necessarily have to return a single row every time, you
> could return one row, or just as many rows as you started with. 
>
> I have already created a UDF that takes two interval types and returns a UDT:
>
> public static IntervalUDT UnionUDF(IntervalUDT i1, IntervalUDT i2)
>
> but I would like to expand this to include the operator to the table level. 
>
> Do I need to create a TVF via
>
> [SqlFunction(FillRowMethodName="RowMethod",TableDefinition="table def")]
>
> ?
>
> Thanks in Advance
>