I have two problems.
1)I'm using datediff function to count difference in minutes between
two dates - and it's working fine.
But I would like to ask if there is any function (or maybe U could tell
me how to do it) that is able to count difference between two dates in
minutes, including only days from Monday to Friday and hours from 9:00
am to 5:15 pm.
2)My second question reference to the first one. Let's suppose that
I've managed to count correctly difference between two dates. But for
example: 120332 minutes is not telling me a lot. So I would like to
show this measure in this format DD:HH:MM
(DaysDays:HoursHours:MinutesMinutes) - aggregation function for this
measure is SUM.
I was trying to use MDX language to change 120332 into DD:HH:MM and
show this result in report but it didn't work - I don't know how
to do it correctly. Or maybe U have a better idea how I should do it.
Here's an approach to the 2nd problem - that of formatting minutes as DD:HH:MM
>>
With
Member [Measures].[TimeInMins] as
120332, FORMAT_STRING = '#,#'
Member [Measures].[TimeDD] as
Format(Int([Measures].[TimeInMins]/1440),
"#:")
Member [Measures].[TimeHHMM] as
Format(TimeSerial(0, [Measures].[TimeInMins] -
(Int([Measures].[TimeInMins]/1440) * 1440), 0),
"HH:mm")
Member [Measures].[TimeFormatted] as
[Measures].[TimeDD] + [Measures].[TimeHHMM]
select {[Measures].[TimeInMins],
[Measures].[TimeFormatted]} on 0
from [Adventure Works]
TimeInMins TimeFormatted
120,332 83:13:32
>>
This article discusses how various values of FORMAT_STRING work:
http://windowssdk.msdn.microsoft.com/en-us/library/ms718137.aspx
>>
Contents of FORMAT_STRING
The FORMAT_STRING property contains a format string that was used to generate the FORMATTED_VALUE property. The contents of FORMAT_STRING vary, depending on the data type of the value.
...
>>
|||Thanks for help!!!Here is code of working function (in sql server 2005):
MEMBER Measures.Time AS
right("0" + CStr(Int((Int(ET/60))/24)), 2)+ ":"
+ right("0" + CStr(Int(ET/60)-(Int((Int(ET/60))/24))*24), 2)+ ":"
+ right("0" + CStr(ET - (Int((Int(ET/60))/24)*24 + Int(Int(ET/60)-(Int((Int(ET/60))/24))*24))*60 ),2)
And to the first problem i wrote a TSQL function (maybe it isn't the best way - but for my problem is ok ;) )
DECLARE kursor CURSOR FOR
SELECT Case_number_, Arrival_Time, Closed_Time, datediff(day,Arrival_Time,Closed_Time) as roznica FROM data
DECLARE @.Case_number nvarchar(15)
DECLARE @.Arrival_Time datetime
DECLARE @.Closed_Time datetime
DECLARE @.Arrival_Time_help datetime
DECLARE @.Time_begin nvarchar(6)
DECLARE @.Time_end nvarchar(6)
DECLARE @.number_days int
DECLARE @.counter int
DECLARE @.no_weekend int
DECLARE @.dzien nvarchar(15)
DECLARE @.Resolved_Time int
DECLARE @.help_value int
OPEN kursor
FETCH NEXT FROM kursor INTO @.Case_number, @.Arrival_Time, @.Closed_Time, @.number_days
WHILE (@.@.FETCH_STATUS=0)
BEGIN
SET @.number_days =@.number_days +1
SET @.counter=@.number_days
SET @.Arrival_Time_help=@.Arrival_time
WHILE (@.counter>0)
BEGIN
SET @.dzien=datename(weekday,@.Arrival_Time_help)
if(@.dzien='Saturday' or @.dzien='Sunday')
BEGIN
SET @.number_days=@.number_days-1
END
SET @.Arrival_Time_help=DATEADD(day,1,@.Arrival_Time_help)
SET @.counter= @.counter-1
END
SET @.Resolved_Time=0
if(@.number_days=1)
SET @.Resolved_Time=datediff(mi,@.Arrival_Time,@.Closed_Time)
else
BEGIN
SET @.Time_begin=datename(hh,@.arrival_time)+':'+datename(mi,@.arrival_time)
SET @.Time_end='17:15'
SET @.help_value=datediff(mi,@.time_begin, @.time_end)
if(@.help_value>0)
SET @.Resolved_Time=@.help_value
SET @.Time_begin='9:00'
SET @.Time_end=datename(hh,@.Closed_time)+':'+datename(mi,@.Closed_time)
SET @.help_value=datediff(mi,@.time_begin, @.time_end)
if(@.help_value>0)
SET @.Resolved_Time=@.Resolved_Time+@.help_value
END
if(@.number_days>2)
BEGIN
SET @.Resolved_Time=@.Resolved_Time+(@.number_days-2)*495
END
UPDATE data set
Time_Period = @.Resolved_Time
WHERE Case_number_=@.Case_number
FETCH NEXT FROM kursor INTO @.Case_number, @.Arrival_Time, @.Closed_Time, @.number_days
END
CLOSE kursor
DEALLOCATE kursor
Remus Rusanu wrote: