difference between dates

Hi,
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.

Can You help me?

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