S format?Hello,
Try this as the expression for your textbox:
=Floor(Fields!TotalSeconds.Value / 3600) & ":"
& Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60 & ":"
& Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60
Hope this helps.
Jarret
|||Since you get to use VB in reports, try the following:
Code Snippet
Format$(Timeserial(0, 0, seconds), "hh:mm:ss")
Larry
|||Thanks Larry, this worked!|||This method worked also. Thanks Jarret.Hello to all,
I have a stored procedure. If i give this commandexce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha
My Procedure Codes are here:
set ANSI_NULLSONset QUOTED_IDENTIFIERON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTERPROCEDURE [dbo].[ShortestPath](@.IDMemberint, @.IDOther varchar(1000),@.Levelint, @.Path varchar(100)=null output)
AS
BEGIN
if( @.Level= 1)
begin
select @.Path=convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @.IDMember
andPATINDEX('%'+@.IDOther+'%',(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDMember))> 0
end
if(@.Level= 2)
begin
selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B
where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andPATINDEX('%'+@.IDOther+'%',B.RelationshipIDs)> 0
end
if(@.Level= 3)
begin
selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C
where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andPATINDEX('%'+@.IDOther+'%',C.RelationshipIDs)> 0
end
if( @.Level= 4)
begin
selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D
where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andPATINDEX('%'+@.IDOther+'%',D.RelationshipIDs)> 0
end
if(@.Level= 5)
begin
selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E
where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andPATINDEX('%'+@.IDOther+'%',E.RelationshipIDs)> 0
end
if(@.Level= 6)
begin
selecttop 1 @.Path=''from wtcomValidRelationships
end
END
Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?
If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.
You run it like this:
EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.
Where
Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?
If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.
You run it like this:
EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.
Where
Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?
If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.
You run it like this:
EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.
Where
Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?
If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.
You run it like this:
EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.
This problem can happen , whereever a stored procedure has radically paths through it with reference to the most efficient way of processing.
|||Hello,
I have tried this EXEC sp_recompile N'TABLENAME. But this problem can not be resolved.
Somebody tell me that thecharindex function is not good for searching. Better use in function. I tried to use in function. Another Problem comes: Member 3430 has Relationship with Member 3418, but i usedin function, Member 3430 can not be found that it has relationship with Member 3418. Here is my test code:
declare @.IDMint;
declare @.IDO varchar(100);
set @.IDM= 3418;
set @.IDO='3430'
selectconvert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= 3418
and(@.IDOin(select B.RelationshipIDsfrom wtcomValidRelationshipsas Bwhere B.IDMember= @.IDM))
Maybe somebody can help me? Thanks
Best Regards
Pinsha
||| Try splittingdbo].[ShortestPath] intodbo].[ShortestPathLevel1],dbo].[ShortestPathLevel2] ect.so that each only has the logic to process one level. This will ensure that each is optimised correctly.
