distinct?

laserbaronen

Hey! Im an sql beginner and i have got a small problem with a query:

select avg(dbo.GetProcessingTime(InterchangeId)) from Messages

i want to call the function only once per InterchangeId like

select distinct InterchangeId from Messages -- Run the avg(Function(...)) on this result only

Any suggestions?

betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);

Morgonen är tröttmans mecka

Blue_Boy

select distinct avg(dbo.GetProcessingTime(InterchangeId)) from Messages

I Love T-SQL "Don't torture yourself,let the life to do it for you."

laserbaronen

Hm yeah but that selects the avg(.. only once? and avg(distinct functioncall(... gets the result of the functioncall distinct(what if it is the same for two different Ids?) if I understand it correctly :confused:

betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);

Morgonen är tröttmans mecka

Blue_Boy

Can you post here data how are stored on table, and write result which you want to get.

I Love T-SQL "Don't torture yourself,let the life to do it for you."

laserbaronen

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

Blue_Boy

try to group by InterchangeId like: select distinct avg(dbo.GetProcessingTime(InterchangeId)) from Messages Group By InterchangeId Having WrittenDate between @startDate and @endDate

I Love T-SQL "Don't torture yourself,let the life to do it for you."

laserbaronen

I think i solved it! :D

ALTER proc [dbo].[GetAverageProcessingTime](@startDate datetime, @endDate datetime)
as
begin
DECLARE @InterchangeIds TABLE
(
InterchangeId uniqueIdentifier
)

insert into @InterchangeIds (InterchangeId)
select distinct m.InterchangeId from Messages m where WrittenDate between @startDate and @endDate

select avg(dbo.GetProcessingTime(InterchangeId)) from @InterchangeIds
end

thanks

betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);

Morgonen är tröttmans mecka

Blue_Boy

Cool,sorry for my unusefull answers :(

I Love T-SQL "Don't torture yourself,let the life to do it for you."