A query is been posted.The time is captured as From_time.When some one answers for the query that time is captured as To_Time.I need the difference between these to as


Official timings are 9.00 am to 6.00 pm
1.only official time is taken into consideration.A query posted after office hours is taken as 6.00 pm.
2.saturdays and sundays are not considered.
3.i have a table as holiday master,so those days are not considered.


Ex:a query is being posted at 4 pm today and it is solved at 2pm tomorrow.
so my answer should be 7 hours.


a query is being posted on friday 3 pm and and is solved on next tuesday 11 am.so friday 3 hours.monday 9 hours and tuesday 2 hours.so totally diff is 14 hours.


can we do with sql query alone or need to use asp as well.
I need to implement this.Please help me out.


Regards,

Sharath.

This assumes that neither from_time nor to_time is on weekend

select name,
    CASE
        WHEN DATEDIFF(day, from_time, to_time) = 0 THEN DATEDIFF(mi, from_time, to_time)
        ELSE 540 * (select count(*) - 2 from fnSeqDates(from_time, to_time) where seqdate between 2 and 6) + -- Number of minutes for whole day, not including from_time and to_time
            1080 - datediff(mi, '09:00:00', from_time) % 1440 + -- Numbers of minutes from from_time to closing at 6 pm same day.
            datediff(mi, '09:00:00', to_time) % 1440 -- Number of minutes since 9 am to to_time same day
    END Mins
FROM MyTable

with the help of my function found here http://www.sqlservercentral.com/colu...tiliretire.asp