Need to go down path to find if everything is settled (recursive possibly)

I have the following table.


GO
/****** Object: Table [dbo].[itTransactionProcess] Script Date:
05/01/2007 10:42:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[itTransactionProcess](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[LotNumber] [int] NOT NULL,
[CurrentProcessStepID] [int] NOT NULL,
[NextProcessStepID] [int] NULL,
[CategoryID] [int] NULL,
[ProductID] [int] NULL,
[ProductVariantID] [int] NULL,
[ParentTransactionID] [int] NULL,
[TransactionDateEntered] [datetime] NULL,
[TransactionDateExit] [datetime] NULL,
[Settlement] [money] NULL,
[Completed] [int] NULL,
CONSTRAINT [PK_itTransactionProcess] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Sample data is as follows


Basically what I need to do is return the lotid where all path have a
settlement date.

this is my current procedure

/****** Object: StoredProcedure [dbo].
[getPendingSettlementDetails] Script Date: 05/01/2007 10:47:47
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getPendingSettlementDetails]
AS

declare @LotNumbersTable table(LotNumber int)
insert into @LotNumbersTable EXEC GetPendingSettlementsLotNumbers

Declare @ResultsTable table(LotNumber int, Company varchar(150),
Contact varchar(150), DateReceived datetime, DateComplete datetime,
SettlementLength int)
Declare @LotNumber int

Declare @DateRecieved datetime, @DateComplete datetime
Declare @NumberOfDaysForSettlement int
Declare @Company varchar(150)
Declare @Contact varchar(150)

select @LotNumber = min(LotNumber) from @LotNumbersTable
while @LotNumber is not null begin
Select @DateRecieved = min(TransactionDateEntered) from
itTransactionProcess where LotNumber = @LotNumber
Select @DateComplete = max(TransactionDateExit) from
itTransactionProcess where LotNumber = @LotNumber and Settlement is
not null
SET @NumberOfDaysForSettlement = DATEDIFF(DAY, @DateRecieved,
@DateComplete)
Select @Company = Company from SP_Active_Lot_Deliveries where LotID =
@LotNumber
Select @Contact = ContactName from SP_Active_Lot_Deliveries where
LotID = @LotNumber
INSERT INTO @ResultsTable (LotNumber, DateReceived, DateComplete,
SettlementLength, Company, Contact) Values
(@LotNumber, @DateRecieved, @DateComplete,
@NumberOfDaysForSettlement, @company, @contact)
select @LotNumber = min(LotNumber) from @LotNumbersTable where
LotNumber > @LotNumber
end
Select * From @ResultsTable where SettlementLength is not null

here is sample data
"TransactionID","LotNumber","CurrentProcessStepID","NextProc essStepID","CategoryID","ProductID","ProductVariantID","Pare ntTransactionID","TransactionDateEntered","TransactionDateEx it","Settlement","Completed"
"628","47","1","2","5","","","","","2007-05-01
10:23:15.747000000","",""
"629","47","1","3","17","","","","","2007-05-01
10:23:15.747000000","0.25",""
"630","47","1","4","34","","","","","2007-05-01
10:23:15.747000000","-0.15",""
"631","47","1","3","38","","","","","2007-05-01
10:23:15.747000000","-0.15",""
"632","47","1","4","33","","","","","2007-05-01
10:23:15.747000000","-0.35",""
"633","47","1","3","15","","","","","2007-05-01
10:23:15.747000000","10",""
"634","47","2","3","86","","","628","2007-05-01
10:23:15.747000000","2007-05-01 10:32:41.320000000","-0.35",""
"635","47","3","","17","","","629","2007-05-01
10:23:15.747000000","","",""
"636","47","4","","34","","","630","2007-05-01
10:23:15.747000000","","",""
"637","47","3","","38","","","631","2007-05-01
10:23:15.747000000","","",""
"638","47","4","","33","","","632","2007-05-01
10:23:15.747000000","","",""
"639","47","3","","15","","","633","2007-05-01
10:23:15.747000000","","",""
"640","47","2","3","85","","","628","2007-05-01
10:24:47.983000000","2007-05-01 10:32:41.320000000","0.05",""
"641","47","2","4","88","","","628","2007-05-01
10:24:56.343000000","2007-05-01 10:32:41.333000000","0.8",""
"642","47","2","4","9","","","628","2007-05-01
10:25:07.517000000","2007-05-01 10:32:41.333000000","-0.15",""
"643","47","2","4","100","","","628","2007-05-01
10:25:22.470000000","2007-05-01 10:32:41.333000000","-0.35",""
"644","47","2","4","90","","","628","2007-05-01
10:25:44.297000000","2007-05-01 10:32:41.333000000","-0.35",""
"645","47","2","4","12","","","628","2007-05-01
10:25:59.347000000","2007-05-01 10:32:41.333000000","-0.15",""
"646","47","2","4","26","","","628","2007-05-01
10:26:12.610000000","2007-05-01 10:32:41.333000000","-0.35",""
"647","47","2","3","94","","","628","2007-05-01
10:26:29.523000000","2007-05-01 10:32:41.333000000","-3",""
"648","47","2","3","95","","","628","2007-05-01
10:26:47.323000000","2007-05-01 10:32:41.333000000","-0.35",""
"649","47","2","3","38","","","628","2007-05-01
10:27:01.450000000","2007-05-01 10:32:41.333000000","-0.15",""
"650","47","2","4","33","","","628","2007-05-01
10:27:15.533000000","2007-05-01 10:32:41.333000000","-0.35",""
"651","47","2","4","34","","","628","2007-05-01
10:27:33.767000000","2007-05-01 10:32:41.333000000","-0.15",""
"652","47","2","3","96","","","628","2007-05-01
10:27:46.850000000","2007-05-01 10:32:41.350000000","-0.35",""
"653","47","2","3","97","","","628","2007-05-01
10:28:00.917000000","2007-05-01 10:32:41.350000000","0.05",""
"654","47","2","4","36","","","628","2007-05-01
10:28:10.813000000","2007-05-01 10:32:41.350000000","-15",""
"655","47","2","4","37","","","628","2007-05-01
10:28:25.347000000","2007-05-01 10:32:41.350000000","0.35",""
"656","47","2","3","98","","","628","2007-05-01
10:28:36.917000000","2007-05-01 10:32:41.350000000","-0.35",""
"694","47","2","10","26","","","628","2007-05-01
10:32:17.170000000","2007-05-01 10:32:41.350000000","",""
"695","47","2","10","35","","","628","2007-05-01
10:32:27.883000000","2007-05-01 10:32:41.350000000","45",""
"696","47","3","","86","","","634","2007-05-01
10:32:41.320000000","","",""
"697","47","3","","85","","","640","2007-05-01
10:32:41.333000000","","",""
"698","47","4","","88","","","641","2007-05-01
10:32:41.333000000","","",""
"699","47","4","","9","","","642","2007-05-01
10:32:41.333000000","","",""
"700","47","4","","100","","","643","2007-05-01
10:32:41.333000000","","",""
"701","47","4","","90","","","644","2007-05-01
10:32:41.333000000","","",""
"702","47","4","","12","","","645","2007-05-01
10:32:41.333000000","","",""
"703","47","4","","26","","","646","2007-05-01
10:32:41.333000000","","",""
"704","47","3","","94","","","647","2007-05-01
10:32:41.333000000","","",""
"705","47","3","","95","","","648","2007-05-01
10:32:41.333000000","","",""
"706","47","3","","38","","","649","2007-05-01
10:32:41.333000000","","",""
"707","47","4","","33","","","650","2007-05-01
10:32:41.333000000","","",""
"708","47","4","","34","","","651","2007-05-01
10:32:41.333000000","","",""
"709","47","3","","96","","","652","2007-05-01
10:32:41.350000000","","",""
"710","47","3","","97","","","653","2007-05-01
10:32:41.350000000","","",""
"711","47","4","","36","","","654","2007-05-01
10:32:41.350000000","","",""
"712","47","4","","37","","","655","2007-05-01
10:32:41.350000000","","",""
"713","47","3","","98","","","656","2007-05-01
10:32:41.350000000","","",""
"714","47","10","","26","","","694","2007-05-01
10:32:41.350000000","","",""
"715","47","10","","35","","","695","2007-05-01
10:32:41.350000000","","",""


If you follow transaction id 714 up through the parent transaction ids
it doesn't not have a settlement cost yet lot 47 shows up as settled.

Thanks for you help.

Re: Need to go down path to find if everything is settled (recursive possibly)

The procedure I entered above is incorrect.

here is the correct one.

ALTER Procedure [dbo].[GetPendingSettlementsLotNumbers]
(
@CurrentParentTransactionID int = 0
)
as
/*---------------------------------------------------------- --
Lists the contents of a table designed to represent a multi-
branch tree. The result set takes the form:
ItemID TreeLevel Label
Tree traversal is done non-recursively (to avoid SQL Server's
limit of 32 nested procedure calls)
------------------------------------------------------------ */


--table to hold the result set: a tree structure arranged by level
declare @IndentedTree table(TransactionID int, ParentTransactionID
int, TreeLevel int, LotNumber int, SettlementPrice decimal(32,9))


--table to track where we are in the tree
--this represents a stack turned upside down (with the most resent
item on
--the bottom)
declare @UnvisitedNodes table(StackID int identity(1,1), TransactionID
int, ParentTransactionID int, TreeLevel int, LotNumber int,
SettlementPrice decimal(32,9))
declare @LastTreeLevel int


set nocount on


--initialize the unvisited nodes list
set @LastTreeLevel = 0
INSERT INTO @UnvisitedNodes (TransactionID, ParentTransactionID,
TreeLevel, LotNumber, SettlementPrice)
--SELECT TransactionID, @LastTreeLevel, Label
--FROM tree
--WHERE ParentTransactionID = @CurrentParentTransactionID
--ORDER BY Label desc
Select TransactionID, ParentTransactionID, @LastTreeLevel,
LotNumber, Settlement
From itTransactionProcess
Where ParentTransactionID = @CurrentParentTransactionID and
Settlement is null
ORDER BY ParentTransactionID desc
--Select * from @UnvisitedNodes
--loop through levels of the tree structure
while ((SELECT count(*) FROM @UnvisitedNodes) <> 0)
begin
--add the top item to the result set
INSERT INTO @IndentedTree
SELECT TransactionID, ParentTransactionID, TreeLevel, LotNumber,
SettlementPrice
FROM @UnvisitedNodes
WHERE StackID = (SELECT max(StackID) FROM @UnvisitedNodes)
--Select * from @IndentedTree
Delete From @IndentedTree where TransactionID in (Select
ParentTransactionID from itTransactionProcess)

--get the top item's ID
set @CurrentParentTransactionID = (
SELECT TransactionID
FROM @UnvisitedNodes
WHERE StackID = (SELECT max(StackID) FROM @UnvisitedNodes))

--get the top item's indentation level
set @LastTreeLevel = (
SELECT TreeLevel
FROM @UnvisitedNodes
WHERE StackID = (SELECT max(StackID) FROM @UnvisitedNodes))


--delete that item from the list

DELETE FROM @UnvisitedNodes WHERE TransactionID =
@CurrentParentTransactionID



--add the children of the current item to the top of the list of
unvisited
--nodes
INSERT INTO @UnvisitedNodes (TransactionID, ParentTransactionID,
TreeLevel, LotNumber)
--SELECT TransactionID, @LastTreeLevel + 1, LotNumber
--FROM tree
--WHERE ParentTransactionID = @CurrentParentTransactionID
--ORDER BY LotNumber desc
Select TransactionID, ParentTransactionID, @LastTreeLevel + 1,
LotNumber
From itTransactionProcess
Where ParentTransactionID = @CurrentParentTransactionID and
Settlement is null
ORDER BY ParentTransactionID desc
end


--return the result set
Select LotId From ItLots where LotID not in
(SELECT LotNumber
FROM @IndentedTree) and LotID in (Select LotNumber from
itTransactionProcess)

Re: Need to go down path to find if everything is settled (recursive possibly)

Please disreguard post.

Everything work but when I moved the database over to a new server the
default values didn't stick to so the parent trasactionid was never
initiallized to 0.

Thanks,

Re: Need to go down path to find if everything is settled (recursive possibly)

Designing Solutions WD (michael.grassman@gmail.com) writes:
> Please disreguard post.
>
> Everything work but when I moved the database over to a new server the
> default values didn't stick to so the parent trasactionid was never
> initiallized to 0.

Which version of SQL Server are you? If you are on SQL 2005, you should
look into a new features know as Common Table Expressions, or CTEs for
short. A special form is recursive CTE which permits you to handle a
hierarchy in a single statement. Look up the topic "WITH common table
expression" in Books Online for further details.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Need to go down path to find if everything is settled (recursive possibly)

>> Lists the contents of a table designed to represent a multi-branch tree. The result set takes the form: ItemID TreeLevel Label Tree traversal is done non-recursively (to avoid SQL Server's limit of 32 nested procedure calls) <<

Have you gotten a copy of TREES & HIERARCHIES IN SQL? There are
several ways to model trees without *any* procedural traversal code at
all.

You also have more NULL-able columns int his one table than the entire
payroll data base of a major automobile.

You used an IDENTITY as a key; is the real key (lot_nbr,
process_step) ?

Many of your data element names are just plain wrong. Think how silly
"category_id" is for an attribute; it is either " of>_category" or "_id", but not both an identifier
(unique to one and only one instance of an entity) and a category
(value appears in many entities). Ditto monsters like
"current_process_step_id" as opposed to a mere "process_step" or
"process_id" instead. Get a copy of ISO-11179 rules for data element
names.

Learn what data and meta data are so you will not mix them in the
table.

Re: Need to go down path to find if everything is settled (recursive possibly)

"--CELKO--" wrote in message
news:1178072664.340603.278310@o5g2000hsb.googlegroups.com...
>>> Lists the contents of a table designed to represent a multi-branch
>>> tree. The result set takes the form: ItemID TreeLevel Label Tree
>>> traversal is done non-recursively (to avoid SQL Server's limit of 32
>>> nested procedure calls) <<
>
> Have you gotten a copy of TREES & HIERARCHIES IN SQL? There are
> several ways to model trees without *any* procedural traversal code at
> all.
>
> You also have more NULL-able columns int his one table than the entire
> payroll data base of a major automobile.

Now I'm curious, how many cars have a payroll database. :-)




--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Re: Need to go down path to find if everything is settled (recursive possibly)

On May 1, 11:00 pm, "Greg D. Moore \(Strider\)"
wrote:
> "--CELKO--" wrote in message
>
> news:1178072664.340603.278310@o5g2000hsb.googlegroups.com...
>
> >>> Lists the contents of a table designed to represent a multi-branch
> >>> tree. The result set takes the form: ItemID TreeLevel Label Tree
> >>> traversal is done non-recursively (to avoid SQL Server's limit of 32
> >>> nested procedure calls) <<
>
> > Have you gotten a copy of TREES & HIERARCHIES IN SQL? There are
> > several ways to model trees without *any* procedural traversal code at
> > all.
>
> > You also have more NULL-able columns int his one table than the entire
> > payroll data base of a major automobile.
>
> Now I'm curious, how many cars have a payroll database. :-)

You gotta be careful... they'll try to sneak one in with their option
packages...