I have a table called Messages, where many messages can share the same InterchangeId I also have a function to get the processing time for an interchange, it looks like this:
ALTER function [dbo].[GetProcessingTime] ( @interchangeId uniqueidentifier )
returns int
AS
BEGIN
DECLARE @recDate DateTime
DECLARE @sendDate DateTime
DECLARE @return int
-- Get receivetime
select top 1 @recDate = mf.[Event/Timestamp] from dbo.Ports p INNER JOIN
BizTalkDTADb.dbo.dtav_MessageFacts mf
INNER JOIN
dbo.Messages m ON mf.[MessageInstance/InstanceID] = m.MessageId ON
p.Name = mf.[Event/Port] INNER JOIN
dbo.PortTypes pt ON p.FK_PortType = pt.Id
WHERE pt.Description='ReceivePort' and m.[InterchangeId] = @interchangeId
order by mf.[Event/Timestamp] asc --first
select top 1 @sendDate = mf.[Event/Timestamp] from dbo.Ports p INNER JOIN
BizTalkDTADb.dbo.dtav_MessageFacts mf
INNER JOIN
dbo.Messages m ON mf.[MessageInstance/InstanceID] = m.MessageId ON
p.Name = mf.[Event/Port] INNER JOIN
dbo.PortTypes pt ON p.FK_PortType = pt.Id
WHERE pt.Description='SendPort' and m.[InterchangeId] = @interchangeId
order by mf.[Event/Timestamp] desc -- latest
set @return = DATEDIFF(millisecond ,@recDate,@sendDate)
return @return
END
It gets the time difference between the first and the last message in the interchange in milliseconds. Now what i want to do is: get the average processing time for each unique interchange hence:
ALTER proc [dbo].[GetAverageProcessingTime](@startDate datetime, @endDate datetime)
as
select avg(distinct dbo.GetProcessingTime(InterchangeId)) from Messages where WrittenDate between @startDate and @endDate
am confused about the distinct in the last procedure, it feels.. wrong :)
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka