|
|
Posted by Designing Solutions WD on 05/01/07 15:53
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","NextProcessStepID","CategoryID","ProductID","ProductVariantID","ParentTransactionID","TransactionDateEntered","TransactionDateExit","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.
Navigation:
[Reply to this message]
|