To calculate average of date differences in a column in sql server

Question

I need to calculate average of date differences excluding weekends across rows in a column. The query gives me wrong results. But, when i calculate it myself, i get different results. the query is something like -

select zone, avg(datediff(dd,startdate,enddate)-datediff((ww,startdate,enddate)*2)) from table where startdate >'1/1/2013' group by zone

I don't get correct results. Please guide me.

This will help you - http://stackoverflow.com/questions/7497418/sql-query-to-find-average-of-difference-between-dates — MusicLovingIndianGirl, Jan 10 '14 at 06:24
hi, thanks for your reply, but, i have 2 columns. so confused. please guide me — user1416631, Jan 10 '14 at 06:43
How about http://www.sqlservercentral.com/Forums/Topic1262284-392-1.aspx — MusicLovingIndianGirl, Jan 10 '14 at 06:59
http://stackoverflow.com/questions/252519/count-work-days-between-two-dates — Justin, Jan 10 '14 at 07:51

score 0 · Accepted Answer · answered Jan 10 '14 at 10:42

0

try this code:

    select zone , avg(NumOfDays) from
          (select zone, (datediff(dd,startdate,enddate)-datediff((ww,startdate,enddate)*2)) as         NumOfDays from table where startdate >'1/1/2013')
    group by zone

answered Jan 10 '14 at 10:42

Katy

286
2
18

The calculation method is same as the OP's, only syntax is different. What is gained by the change? – Andriy M Jan 14 '14 at 11:49
hey thanks, and many many sorry for late reply. was completely busy with my work – user1416631 Jan 20 '14 at 03:06

Nelly · Answer 2 · 2014-01-14T14:33:15.873

I don't know how your data looks like and what you challenge exactly is but I think your problem is "excluding weekend"? Maybe this helps you?

First create a function that counts every weekend day between your period for filtering it later:

CREATE FUNCTION fnc_GetWeekendDays(@dFrom DATETIME, @dTo   DATETIME)
RETURNS INT AS
BEGIN
   Declare @weekendDays int
   Set @weekendDays = 0

   While @dFrom <= @dTo 
   Begin
      If ((datepart(dw, @dFrom) = 1) OR (datepart(dw, @dFrom) = 7))    
                  Set @weekendDays = @weekendDays + 1
                  Set @dFrom = DateAdd(d, 1, @dFrom)

    End
   Return (@weekendDays)
END

After this, write your query:

Select
zone,
avg(cast((datediff(SECOND,StartTime,EndTime) - dbo.fnc_GetWeekendDays(StartTime, EndTime)*86400) as float))
from
    (
        Select zone,
        CASE 
            WHEN (datepart(dw, startdate) = 1) THEN DATEADD(Day, DATEDIFF(Day, 0, startdate), 1) 
            WHEN (datepart(dw, startdate) = 7) THEN DATEADD(Day, DATEDIFF(Day, 0, startdate), 2) 
            ELSE startdate END as StartTime, 
        CASE 
            WHEN (datepart(dw, enddate) = 1) THEN DATEADD(Day, DATEDIFF(Day, 0, enddate), -2) 
            WHEN (datepart(dw, enddate) = 7) THEN DATEADD(Day, DATEDIFF(Day, 0, enddate), -1) 
            ELSE enddateEND as EndTime
        from  table
    ) subquery
where StartTime < EndTime and startdate >'2013-01-01 00:00:00.000'
Group by zone

You will get your average time in seconds, if you want another format you have to calculate it...

To calculate average of date differences in a column in sql server

2 Answers2