June 7, 2012 at 3:03 pm
I have hours and minutes achieving like this
ID Time(hrs:mins)
1 100:29
2 200:30
3 400:20
needed average of time in (hrs:mins) in an expression
any help is appreciated
June 7, 2012 at 3:06 pm
Can you post some ddl for this table so we know what datatypes you are working with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 3:10 pm
Acieving thru function:
ALTER FUNCTION [dbo].[Usp_GetHoursMins1]
(
@para_start_time varchar(30),
@para_end_time varchar(30)
)
RETURNS VARCHAR(20)
As
BEGIN
-- Declare the return variable here
DECLARE
@hrs_min as varchar(20),
@total_time as int,
@datecalc as datetime,
@iDateCnt as int,
@minutesleft as int,
@hours as int,
@proctime as varchar(20),
@start_time as datetime,
@end_time as datetime
Set @start_time = CONVERT(datetime, @para_start_time)
Set @end_time = CONVERT(datetime, @para_end_time)
Set @total_time = DateDiff("n", @start_time, @end_time)
Set @iDateCnt = 0
Set @datecalc = @start_time
WHILE DateDiff(DAY, @datecalc, @end_time) <> 0
Begin
Set @datecalc = DateAdd("d", 1, @datecalc)
If DATEPART(DW,@datecalc) = 1 or DATEPART(DW,@datecalc) = 7
Begin
Set @iDateCnt = @iDateCnt + 1440 -- 24 hours per weekend day
End
Else
Begin
Set @iDateCnt = @iDateCnt + 960 -- 720 for 12 hr work day, 960 if 8 hrs
End
End
Set @total_time = @total_time - @iDateCnt
IF @total_time < 1
BEGIN
set @hours = 0
set @minutesleft = 0
END
ELSE
BEGIN
set @hours = (@total_time / 60)
IF @hours > 0
BEGIN
set @minutesleft = @total_time - (@hours * 60)
END
ELSE
BEGIN
set @minutesleft = @total_time
END
END
Set @hrs_min = Replace(Str(@hours, 4), ' ', '0') + ':' + Replace(Str(@minutesleft, 2), ' ', '0')
IF left(@hrs_min, 1) = '0' and Len(@hrs_min) > 4
BEGIN
Set @hrs_min = Right(@hrs_min, (Len(@hrs_min) - 1))
END
--INSERT INTO @TIMEVALS( hrs_min) VALUES (@hrs_min)
RETURN @hrs_min
END
June 7, 2012 at 3:15 pm
trying taking:
parameters :
start_time END_time
2012-05-17 14:16:17.927 getdate()
2012-05-19 18:41:13.607 getdate()
June 7, 2012 at 3:26 pm
Well the explanation is a bit sketchy but let's use the data you posted in your post. I used your function to create a temp table.
create table #HoursMin
(
ID int identity,
HM varchar(20)
)
insert #HoursMin
select '100:29' union all
select '200:30' union all
select '400:20'
So if I understand what you want you would like to get the average from this entire result set?
Hours would be 233.3333
Minutes would be 26.33333
So the result you would be looking for is 233.27???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 3:30 pm
yes...
June 7, 2012 at 3:31 pm
yes but in hrs:mins format
June 7, 2012 at 3:35 pm
if we can convert hours to mins and then take average of it??? in expression??
June 7, 2012 at 3:35 pm
Something like this?
USE [Sandbox]
GO
/****** Object: UserDefinedFunction [dbo].[itvf_FormatTimeHHMM] Script Date: 06/07/2012 15:30:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.itvfn_FormatTimeHHMM (
@TimeInMinutes INT
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN(
SELECT CASE SIGN(@TimeInMinutes) WHEN -1 THEN '-' ELSE '' END +
CAST(ABS(@TimeInMinutes) / 60 AS VARCHAR) + ':' +
RIGHT('0' + CAST(ABS(@TimeInMinutes) % 60 AS VARCHAR),2) AS FormatTime
);
GO
CREATE TABLE #TestTab(
Id INT,
TimeInt varchar(16)
);
INSERT INTO #TestTab
SELECT 1, '100:29' UNION ALL
SELECT 2, '200:30' UNION ALL
SELECT 3, '400:20';
GO
WITH BaseData AS (
SELECT
AVG(CAST(SUBSTRING(TimeInt,1,CHARINDEX(':',TimeInt)-1) AS INT) * 60 + CAST(RIGHT(TimeInt,2) AS INT)) AvgMinutes
FROM
#TestTab
)
SELECT * FROM BaseData CROSS APPLY dbo.itvfn_FormatTimeHHMM(AvgMinutes)
;
GO
DROP FUNCTION dbo.itvfn_FormatTimeHHMM;
DROP TABLE #TestTab;
GO
June 7, 2012 at 4:00 pm
yes...I converted your sql expression to SSRS expression , but getting avg minutes:2259:33333:(
June 7, 2012 at 4:04 pm
Anyways I took round ()off to get avg time in minutes in ssrs exp
June 21, 2012 at 10:22 am
this works in excel, sure it would in SSRS
get minutes EG(120)/1440 =0.08
format as HH:MM =02:00
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply