Need UDF to return table variable that traverses an organization hierarchy
Fellow Experts,
Having never quite gotten the hang of recursive queries, I could use your help here. (If this can be done without a recursive query, great.) This will be run in SQL Server 2008 R2.
Consider the following org chart:
In that phony org chart, each box represents a "team", and each team has zero or more "users" assigned to it. A user is only ever assigned to one team.
The teams are stored in the database in a User_Teams table (TeamID, TeamName, ParentTeamID). The users are stored in the database in a User_List table (UserID, UserDispName, TeamID).
What I need is a UDF that, given a particular TeamID, returns a table of all the UserID values for the users assigned to that team, and also the UserID values for all of the teams that fall under that team in the hierarchy.
Thus, in the example above, if I supplied 96 for the @TeamID parameter (that is, the TeamID for Sitcom Central), I would expect the UDF to return a table of all of UserID values for all teams shaded in yellow. Based on the sample data provided here, I would expect the UDF to return a table with the following values:
268 (Parr Jack )
269 (THE Witch Endora )
270 (Stevens Darren )
271 (Stevens Samantha )
272 (Stevens Tabatha )
273 (Brady Mike )
274 (Mertz Fred )
275 (Maid Alice )
276 (Ricardo Ricky )
277 (Brady Cindy )
278 (Brady Bobby )
(Note that I only need the integer IDs. I added the names for ease of reference only.)
You can use the following to generate test data:
Thanks!
Having never quite gotten the hang of recursive queries, I could use your help here. (If this can be done without a recursive query, great.) This will be run in SQL Server 2008 R2.
Consider the following org chart:
In that phony org chart, each box represents a "team", and each team has zero or more "users" assigned to it. A user is only ever assigned to one team.
The teams are stored in the database in a User_Teams table (TeamID, TeamName, ParentTeamID). The users are stored in the database in a User_List table (UserID, UserDispName, TeamID).
What I need is a UDF that, given a particular TeamID, returns a table of all the UserID values for the users assigned to that team, and also the UserID values for all of the teams that fall under that team in the hierarchy.
Thus, in the example above, if I supplied 96 for the @TeamID parameter (that is, the TeamID for Sitcom Central), I would expect the UDF to return a table of all of UserID values for all teams shaded in yellow. Based on the sample data provided here, I would expect the UDF to return a table with the following values:
268 (Parr Jack )
269 (THE Witch Endora )
270 (Stevens Darren )
271 (Stevens Samantha )
272 (Stevens Tabatha )
273 (Brady Mike )
274 (Mertz Fred )
275 (Maid Alice )
276 (Ricardo Ricky )
277 (Brady Cindy )
278 (Brady Bobby )
(Note that I only need the integer IDs. I added the names for ease of reference only.)
You can use the following to generate test data:
CREATE TABLE [dbo].[User_Teams](
[TeamID] [int] NOT NULL,
[TeamName] [nvarchar](50) NOT NULL,
[ParentTeamID] [int] NULL,
)
GO
INSERT INTO User_Teams (TeamID, TeamName, ParentTeamID) VALUES
(3, 'My Corporation', 0),
(4, 'Team 1', 3),
(5, 'Team 2', 4),
(6, 'Team 3', 4),
(7, 'Team 4', 4),
(8, 'Team 5', 17),
(9, 'Team 6', 8),
(10, 'Team 7', 17),
(11, 'Team 8', 10),
(12, 'Team 9', 11),
(13, 'Team 10', 3),
(14, 'Team 11', 5),
(15, 'Team 12', 5),
(16, 'Team 13', 4),
(17, 'Team 14', 5),
(18, 'Team 15', 17),
(19, 'Team 16', 3),
(20, 'Team 17', 10),
(21, 'Team 18', 3),
(22, 'Team 19', 4),
(23, 'Team 20', 22),
(24, 'Team 21', 23),
(25, 'Team 22', 23),
(26, 'Team 23', 23),
(27, 'Team 24', 23),
(28, 'Team 25', 23),
(29, 'Team 26', 13),
(30, 'Team 27', 13),
(31, 'Team 28', 13),
(32, 'Team 29', 4),
(33, 'Team 30', 32),
(34, 'Team 31', 32),
(35, 'Team 32', 32),
(36, 'Team 33', 4),
(37, 'Team 34', 36),
(38, 'Team 35', 36),
(39, 'Team 36', 13),
(40, 'Team 37', 3),
(41, 'Team 38', 35),
(42, 'Team 39', 23),
(43, 'Team 40', 4),
(44, 'Team 41', 4),
(45, 'Team 42', 4),
(46, 'Team 43', 45),
(47, 'Team 44', 4),
(48, 'Team 45', 47),
(49, 'Team 46', 3),
(50, 'Team 47', 49),
(51, 'Team 48', 50),
(52, 'Team 49', 51),
(53, 'Team 50', 22),
(54, 'Team 51', 4),
(55, 'Team 52', 22),
(56, 'Team 53', 55),
(57, 'Team 54', 55),
(58, 'Team 55', 55),
(59, 'Team 56', 16),
(60, 'Team 57', 23),
(61, 'Team 58', 13),
(62, 'Team 59', 47),
(63, 'Team 60', 55),
(64, 'Team 61', 22),
(65, 'Team 62', 3),
(66, 'Team 63', 23),
(67, 'Team 64', 23),
(68, 'Team 65', 23),
(69, 'Team 66', 4),
(70, 'Team 67', 17),
(71, 'Team 68', 17),
(72, 'Team 69', 9),
(73, 'Team 70', 22),
(74, 'Team 71', 36),
(75, 'Team 72', 36),
(76, 'Team 73', 3),
(77, 'Team 74', 3),
(78, 'Team 75', 77),
(79, 'Team 76', 3),
(80, 'Team 77', 77),
(81, 'Team 78', 78),
(82, 'Team 79', 77),
(83, 'Team 80', 81),
(84, 'Team 81', 71),
(85, 'Team 82', 71),
(86, 'Team 83', 71),
(90, 'Team 84', 79),
(91, 'Team 85', 90),
(92, 'Team 86', 91),
(93, 'Team 87', 92),
(95, 'Team 88', 45),
(96, 'Sitcom Central', 3),
(97, 'Brady Bunch', 96),
(98, 'Family Room', 97),
(99, 'Team Boys', 98),
(100, 'Marsha, Marsha, Marsha', 96),
(101, 'Mikes Den', 97),
(102, 'Team Parents', 101),
(103, 'Team Housekeeper', 101),
(104, 'I Love Lucy', 96),
(105, 'The Tropicana Lounge', 104),
(106, 'Team 99', 35),
(107, 'Team 100', 3),
(108, 'Team 101', 98),
(109, 'Team 102', 4),
(110, 'Team 103', 109),
(111, 'Team 104', 110),
(112, 'Team 105', 110),
(113, 'Team 106', 107),
(114, 'Team 107', 107),
(116, 'The Ricardo Residence', 104),
(117, 'Bewitched', 96),
(118, 'Team 110', 107),
(120, 'Team 111', 50),
(121, 'Team 112', 120),
(123, 'Team 113', 3),
(124, 'Team 114', 3),
(125, 'Team 115', 124),
(126, 'Team 116', 79),
(127, 'Team 117', 126),
(128, 'Team 118', 127),
(129, 'Team 119', 128),
(130, 'Team 120', 126)
GO
CREATE TABLE [dbo].[User_List](
[UserID] [int] NOT NULL,
[UserDispName] [nvarchar](256) NOT NULL,
[TeamID] [int] NULL,
)
GO
INSERT INTO User_List (UserID, UserDispName, TeamID) VALUES
(1, 'Goforth Julia', 5),
(2, 'Slack Sophia', 4),
(3, 'Smallwood Chanel', 118),
(4, 'Stevens Faye', 118),
(5, 'Guest Elouise', 118),
(6, 'Beltran Maricela', 118),
(7, 'Mcalister Julius', 118),
(8, 'Nixon Carey', 118),
(9, 'Causey Jendayi', 118),
(10, 'Hopson Jinelle', 118),
(11, 'Luttrell Eris', 118),
(12, 'Reynolds Jovanni', 118),
(13, 'Call Ilithya', 118),
(14, 'Winchester Nituna', 118),
(15, 'Reynoso Elian', 118),
(16, 'Valdes Denzel', 118),
(17, 'Lovejoy Jonathon', 118),
(18, 'Ennis Amelia', 118),
(19, 'Jones Jaimie', 118),
(20, 'Cockrell Glenn', 118),
(21, 'Taggart Graciela', 118),
(22, 'Clawson Mira', 118),
(23, 'Edmonds Jared', 118),
(24, 'Maguire Annette', 118),
(25, 'Mello Kari', 118),
(26, 'Mccrary Barrett', 118),
(27, 'Hidalgo Doreen', 118),
(28, 'Calvert Jean', 118),
(29, 'Brooks Andralyn', 118),
(30, 'Pryor Isoke', 118),
(31, 'Harness Whitney', 118),
(32, 'Drummond Hisana', 118),
(33, 'Speed Joletta', 118),
(34, 'Hornsby Isis', 118),
(35, 'Raney Wyman', 118),
(36, 'Borrego Latoya', 118),
(37, 'Trotter Jaime', 118),
(38, 'Seitz Cana', 118),
(39, 'Christman Idana', 118),
(40, 'Hairston Micheal', 118),
(41, 'Milton Mya', 118),
(42, 'Coe Jeremiah', 118),
(43, 'Seymore Roland', 118),
(44, 'Jack Ghada', 118),
(45, 'Macon Irma', 118),
(46, 'Carmona Alexander', 118),
(47, 'Tobin Wade', 118),
(48, 'Larry Harmon', 118),
(49, 'Peachey Lewa', 118),
(50, 'Baker Mose', 118),
(51, 'Larose Polly', 118),
(52, 'Briggs Keola', 118),
(53, 'Wagoner Yazmin', 118),
(54, 'Leslie Kennedi', 118),
(55, 'Oconner Gaura', 118),
(56, 'Villalobos Essie', 118),
(57, 'Proctor Terri', 118),
(58, 'Linville Irvin', 118),
(59, 'Pickering Rogers', 118),
(60, 'Spears Raekwon', 118),
(61, 'Bowlin Marlene', 118),
(62, 'Gooding Earline', 118),
(63, 'Sells Sarina', 118),
(64, 'Callaghan Harmon', 118),
(65, 'Ripley Elna', 118),
(66, 'Batts Ricardo', 118),
(67, 'Smoot Jamir', 118),
(68, 'Woo Alexandra', 118),
(69, 'Patterson Kieu', 118),
(70, 'Bowden Wilbert', 118),
(71, 'Horan Jessie', 118),
(72, 'Ferris Bian', 118),
(73, 'Putnam Adeola', 118),
(74, 'Dutton Trudy', 118),
(75, 'Frasier Elpida', 118),
(76, 'Pelletier Mikaela', 118),
(77, 'Matlock Chandra', 118),
(78, 'Rountree Enoch', 118),
(79, 'Thibodeau Khalil', 118),
(80, 'Seal Sebastian', 118),
(81, 'Wingfield Berenice', 118),
(82, 'Patterson Noe', 118),
(83, 'Blais Karmita', 118),
(84, 'Phipps Verona', 118),
(85, 'Conn Dorene', 118),
(86, 'Lovelace Jailyn', 118),
(87, 'George Elwyn', 118),
(88, 'Rooney Magdalene', 118),
(89, 'Packer Dayana', 118),
(90, 'Crews Clay', 118),
(91, 'Blanchard Jefferson', 114),
(92, 'Morrissey Donal', 114),
(93, 'Burroughs Giana', 114),
(94, 'Boisvert Hannah', 114),
(95, 'Childress Chad', 114),
(96, 'Kish Kaden', 114),
(97, 'Krause Tory', 114),
(98, 'Jeffrey Agnes', 114),
(99, 'Saenz Abigail', 114),
(100, 'Marcum Letitia', 114),
(101, 'Montano Conley', 114),
(102, 'Clem Juwan', 114),
(103, 'Little Joel', 114),
(104, 'Weatherly Stephanie', 114),
(105, 'Teague Ceana', 114),
(106, 'Jorgenson Martina', 114),
(107, 'Peck Vivian', 114),
(108, 'Lloyd Natesa', 114),
(109, 'Bradshaw Cannon', 114),
(110, 'Cintron Bina', 114),
(111, 'Tanner Accalia', 114),
(112, 'Gorham Ervin', 114),
(113, 'Riddick Storm', 114),
(114, 'Beckwith Anais', 114),
(115, 'Shade Tavon', 114),
(116, 'Barrera Lelia', 114),
(117, 'Lindstrom Rachel', 114),
(118, 'Lankford Perry', 114),
(119, 'Hines Augustine', 114),
(120, 'Messina Pat', 114),
(121, 'Tellez Kailee', 114),
(122, 'Harley Deonte', 114),
(123, 'Cecil Jahzara', 114),
(124, 'Leslie Vesta', 114),
(125, 'Brooks Katrina', 114),
(126, 'Covey Darryl', 114),
(127, 'Mccorkle Lafayette', 114),
(128, 'Spencer Isis', 114),
(129, 'Hyde Hayden', 114),
(130, 'Jacques Niko', 114),
(131, 'Dodge Morris', 114),
(132, 'Tarver Maggie', 114),
(133, 'Michaud Rhett', 114),
(134, 'Geiger Dwayne', 114),
(135, 'Montanez Deanna', 114),
(136, 'Stephens Nakia', 114),
(137, 'Koonce Findlay', 114),
(138, 'Martz Morse', 114),
(139, 'Montoya Aryan', 114),
(140, 'Wylie Leoma', 114),
(141, 'Ashby Tayler', 114),
(142, 'Mccauley Oralia', 114),
(143, 'Barr Theo', 114),
(144, 'Odum Gilead', 114),
(145, 'Kemper Ayden', 114),
(146, 'Sylvester Gino', 114),
(147, 'Hassell Cliff', 114),
(148, 'Bull Pearl', 114),
(149, 'Teal Christen', 114),
(150, 'Parry Armani', 114),
(151, 'Bivins Jadyn', 114),
(152, 'Agee Gaetana', 114),
(153, 'Rau Riley', 114),
(154, 'Stein Jordan', 114),
(155, 'Mcknight Anton', 114),
(156, 'Longoria Alton', 114),
(157, 'Wentz Nathen', 114),
(158, 'Zamora Carolina', 114),
(159, 'Duggan Afia', 114),
(160, 'Rich Patrick', 114),
(161, 'Bader Nina', 114),
(162, 'Sell Carys', 114),
(163, 'Stafford Bo', 114),
(164, 'Wise Elmira', 114),
(165, 'Marx Angeline', 114),
(166, 'Stjohn Kylie', 114),
(167, 'Swan James', 114),
(168, 'Mcallister Guadalupe', 114),
(169, 'Wentz Karna', 114),
(170, 'Grey Abena', 114),
(171, 'Robert Viola', 114),
(172, 'Blaylock Jason', 114),
(173, 'Durham Adrian', 114),
(174, 'Freitas Pauline', 114),
(175, 'Lankford Chelsi', 114),
(176, 'Fitzgerald Mathias', 114),
(177, 'Lewandowski Lunette', 114),
(178, 'Willard Michi', 114),
(179, 'Jewell Lynna', 114),
(180, 'Williford Levi', 114),
(181, 'Chesser Roselyn', 114),
(182, 'Harry Kimoni', 114),
(183, 'Jackson Debra', 114),
(184, 'Hubert Yessenia', 114),
(185, 'Newsom Constantine', 114),
(186, 'Hiatt Sofia', 114),
(187, 'Bone Kali', 114),
(188, 'Pitt Linnet', 114),
(189, 'Malley Kinipela', 114),
(190, 'Prieto Kory', 114),
(191, 'Francois Aviva', 114),
(192, 'Putman Elkan', 114),
(193, 'Munoz Beverly', 114),
(194, 'Fredericks Aimee', 114),
(195, 'Fernandes Emera', 114),
(196, 'Speer Lawerence', 114),
(197, 'Harris Evalyn', 114),
(198, 'Bayne Tristian', 114),
(199, 'Hoy Adicia', 114),
(200, 'Mohr Harlen', 114),
(201, 'Oconner Palma', 114),
(202, 'Melendez Dick', 114),
(203, 'Richardson Roxanne', 114),
(204, 'Sorensen Ivan', 114),
(205, 'Pittman Tammy', 114),
(206, 'Moses Kara', 114),
(207, 'Hussey Kaoru', 114),
(208, 'Rickman Aki', 114),
(209, 'Franz Mark', 114),
(210, 'Bollinger Amaris', 114),
(211, 'Olive Lieu', 114),
(212, 'Monaco Dorsey', 114),
(213, 'Drummond Glendon', 114),
(214, 'Stubbs Odell', 114),
(215, 'Poling Cira', 114),
(216, 'Keefer Ellenis', 114),
(217, 'Barnhill Leighanna', 114),
(218, 'Seymour Elnora', 114),
(219, 'Downing Conner', 114),
(220, 'Boucher Aldan', 114),
(221, 'Solano Johari', 114),
(222, 'Perales Winford', 114),
(223, 'Vail Louann', 114),
(224, 'Fredrickson Kaliska', 114),
(225, 'Pennington Geshi', 114),
(226, 'Ramirez Cora', 114),
(227, 'Cowles Lassie', 114),
(228, 'Madden Reagan', 114),
(229, 'Wylie Darshan', 114),
(230, 'Chastain Lessie', 114),
(231, 'Jacobs Keith', 114),
(232, 'Lockhart Janette', 114),
(233, 'Benson Charan', 114),
(234, 'Buford Joshua', 114),
(235, 'East Belle', 114),
(236, 'Gaddy Marlee', 114),
(237, 'Flint Conway', 114),
(238, 'Slattery Grietje', 114),
(239, 'Amaral Nitza', 114),
(240, 'Mccracken Sal', 114),
(241, 'Dorman Darah', 114),
(242, 'Aquino Caden', 114),
(243, 'Parr Glendon', 114),
(244, 'Canady Rodrick', 114),
(245, 'Bisson Elke', 114),
(246, 'Hope Brandie', 114),
(247, 'Bagwell Belita', 114),
(248, 'Moran Alfred', 114),
(249, 'Feliciano Chava', 114),
(250, 'Squires Leigh', 114),
(251, 'Richard Evette', 114),
(252, 'Osborn Darah', 114),
(253, 'Pendleton Arvil', 114),
(254, 'Cothran Leroy', 114),
(255, 'Denson Kaethe', 114),
(256, 'Baer Colette', 114),
(257, 'Means Lecea', 114),
(258, 'Stroup Malachi', 114),
(259, 'Dobson Florine', 114),
(260, 'Burden Ava', 114),
(261, 'Dugan Easter', 114),
(262, 'Kelsey Cascata', 114),
(263, 'Boyer Aisling', 114),
(264, 'Skelton Derrick', 114),
(265, 'Lessard Lilike', 114),
(266, 'Mosby Lindhurst', 21),
(267, 'Walsh Tyron', 39),
(268, 'Parr Jack ', 96),
(269, 'THE Witch Endora ', 117),
(270, 'Stevens Darren ', 117),
(271, 'Stevens Samantha ', 117),
(272, 'Stevens Tabatha ', 117),
(273, 'Brady Mike ', 97),
(274, 'Mertz Fred ', 104),
(275, 'Maid Alice ', 97),
(276, 'Ricardo Ricky ', 105),
(277, 'Brady Cindy ', 108),
(278, 'Brady Bobby ', 99)Thanks!
ASKER
Kevin,
Naive question: can you run a CTE within a UDF? :)
The end game is to use the result to help filter another query I am running:
My original thought was to:
I'm willing to use any approach that achieves that goal without requiring dynamic SQL.
Going out for a bike ride now before I run out of daylight, so take your time :)
Patrick
Naive question: can you run a CTE within a UDF? :)
The end game is to use the result to help filter another query I am running:
DECLARE @StartDate int, @EndDate int
SET @StartDate = CONVERT(int, CONVERT(varchar, DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0), 112))
SET @EndDate = CONVERT(int, CONVERT(varchar, DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0), 112))
SELECT t.TeamName, u.UserName, u.UserDispName,
CASE u.State
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
ELSE 'Deleted' END AS UserState,
al.AppDispName, al.Productivity AS AppProductivity,
ag.AppGroupName, ag.Productivity AS AppGroupProductivity,
DATEADD(minute, (p.FrameTime / 100) % 100,
DATEADD(hour, p.FrameTime / 10000,
CONVERT(datetime, CONVERT(varchar, p.FrameDate)))) AS IntervalDateTime,
SUM(p.Duration) AS Seconds
FROM Pc_Log p INNER JOIN
User_List u ON p.PcUser = u.UserID INNER JOIN
User_Teams t ON u.TeamID = t.TeamID INNER JOIN
App_List al ON p.Application = al.AppID INNER JOIN
App_groups ag ON al.AppGroup = ag.AppGroupID
WHERE p.Duration > 0 AND
p.FrameDate >= @StartDate AND
p.FrameDate <= @EndDate
GROUP BY t.TeamName, u.UserName, u.UserDispName, u.State,
al.AppDispName, al.Productivity, ag.AppGroupName,
ag.Productivity, p.FrameDate, p.FrameTime
ORDER BY u.UserName, p.FrameDate, p.FrameTime, al.AppDispNameMy original thought was to:
Use a UDF to return a table
Join that table to the query above to only return data for a subset of my users
I'm willing to use any approach that achieves that goal without requiring dynamic SQL.
Going out for a bike ride now before I run out of daylight, so take your time :)
Patrick
Patrick, you may be able to do this without a UDF completely, depending on a full understanding of the end game, but let's start with your thought. Yes, you can use a CTE in a UDF. I was thinking an in-line UDF for performance reasons.
P.S. I almost have the example. I ran first query thinking users were all subordinates and got:
I suspect that one of the levels does not have users, so it missed recursing that leg. I will re-write to recurse teams first then do a single join to user_list.
P.S. I almost have the example. I ran first query thinking users were all subordinates and got:
UserID
-----------
268
269
270
271
272
273
274
275
276I suspect that one of the levels does not have users, so it missed recursing that leg. I will re-write to recurse teams first then do a single join to user_list.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Kevin! Had a super ride, and now I am going to eat some dinner before trying out your code.
>>Having never quite gotten the hang of recursive queries...
recursion can be a bit mind-twisting
Maybe this will help to get your head around it. Instead of worrying about any hierarchy, get the basic recursion construct in your head by generating some numbers. Like this:
http://www.youtube.com/watch?v=AY2ztQ-khHM
Learn about Recursive SQL in Advanced SQL for DB2 (24 mins)
there are other youtube vids on this topic too.
This is an often referenced page
http://www.codeproject.com/Articles/83654/Inside-Recursive-CTEs
Inside Recursive CTEs
and of course 'the manual'
http://msdn.microsoft.com/en-us/library/ms186243.aspx
Recursive Queries Using Common Table Expressions
recursion can be a bit mind-twisting
Maybe this will help to get your head around it. Instead of worrying about any hierarchy, get the basic recursion construct in your head by generating some numbers. Like this:
-- generate some numbers
;with
GenNum --<< the name of this CTE (Common Table Expression)
(num)
as (
select 1 -- A << the "initialize" or "root" query, i.e. we start here
union all -- B << important, we append more results using this
select num + 1
from GenNum -- C << now re-use ITSELF (by its name "GenNum")
where num < 10 -- D << a "LOOP LIMITING COUNTER" i.e. we stop here
-- E << but also, repeat from the union all (B)
-- until there is no more to do
)
select num
from GenNum --<< now we can use those results as if it were a table
;-- your teams
;WITH teamsCTE(TeamID)
AS
(
-- top level
SELECT TeamID
FROM User_Teams
WHERE TeamID = @TeamID -- A << the "initialize" or "root" query, i.e. we start here
UNION ALL -- B << important, we append more results using this
-- subordinates
SELECT c.TeamID
FROM teamsCTE p -- C << now re-use ITSELF (by its name "teamsCTE")
JOIN User_Teams c
ON c.ParentTeamID = p.TeamID -- << repeat from the union all (B)
-- until there is no more to do
)http://www.youtube.com/watch?v=AY2ztQ-khHM
Learn about Recursive SQL in Advanced SQL for DB2 (24 mins)
there are other youtube vids on this topic too.
This is an often referenced page
http://www.codeproject.com/Articles/83654/Inside-Recursive-CTEs
Inside Recursive CTEs
and of course 'the manual'
http://msdn.microsoft.com/en-us/library/ms186243.aspx
Recursive Queries Using Common Table Expressions
ASKER
Perfect, thanks Kevin!
One of these days I will have to wrap my head around how CTEs work. I can (usually) manage recursive procedures in VBA and VBScript, so I'm not sure what my stumbling block is here :)
One of these days I will have to wrap my head around how CTEs work. I can (usually) manage recursive procedures in VBA and VBScript, so I'm not sure what my stumbling block is here :)
>>so I'm not sure what my stumbling block is here
nor I :)
nor I :)
You are most welcome, Patrick! I am glad I could help.
Respectfully yours,
Kevin
P.S. @Paul, it is nice to run into you again as I have enjoyed reading your posts by the way - very helpful. Cheers!
Respectfully yours,
Kevin
P.S. @Paul, it is nice to run into you again as I have enjoyed reading your posts by the way - very helpful. Cheers!
@Kevin np. happy to help (well, I quite enjoyed trying to anyway). Cheers, Paul
Are you good with using a recursive common table expression or do you want the UDF to recurse? I just want to make sure I understand any restrictions you have.
Kevin
P.S. I will put together a sample with CTE while I wait to hear back.