Calculating Avg of days, broken down by period

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating Avg of days, broken down by period

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-28 : 11:25:53

Hey All

I need some help

Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month

Sounds easy right ?
My query so far
select
ID,
left(CreatedDate,10) as CreatedDate,
left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
from #build

Produces following results
ID CreatedDate Date_Docs_In_Complete__c Days
0063000000ausKGAAY 2010-03-26 2013-07-16 1208
0063000000mC359AAC 2011-06-30 2013-07-03 734
0063000000oyaSPAAY 2011-11-29 2013-07-18 597

Desired outcome results would be
Year Month Date_Docs_In_Complete__c Total Days Avg_Days
2013 07 3 2539 846.3

Really looking forward to your help

Regards
D

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-11-28 : 13:19:55

Hey guys

I have built my query as follows ( I am hoping there is an easier way, to do the calculation rather than the way i have done it_

My query is

select
ID,
YEAR([Date_Docs_In_Complete__c]) as Year_Docs_In,
MONTH([Date_Docs_In_Complete__c])as Month_Docs_In,
left([Date_Docs_In_Complete__c],7) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
into #ABC
from #build
group by ID,CreatedDate,[Date_Docs_In_Complete__c]

select
Year_Docs_In,
Month_Docs_In,
sum(Days)as Days
into #ab2
from #abc
where Year_Docs_In = YEAR(GETDATE())
group by Year_Docs_In,Month_Docs_In

--- Converting number to months so can join onto count table --
select
Days,
Case when Month_Docs_In = 1 then 'January'
when Month_Docs_In = 2 then 'February'
when Month_Docs_In = 3 then 'March'
when Month_Docs_In = 4 then 'April'
when Month_Docs_In = 5 then 'May'
when Month_Docs_In = 6 then 'June'
when Month_Docs_In = 7then 'July'
when Month_Docs_In = 8 then 'August'
when Month_Docs_In = 9 then 'September'
when Month_Docs_In = 10 then 'October'
when Month_Docs_In = 11 then 'November'
when Month_Docs_In = 12 then 'December' end as Month_Date
Into #months
FROM #ab2

--Counts docs in via month --
;WITH N(n) AS
(SELECT 0 UNION ALL SELECT n+1 FROM N WHERE n < 11)
SELECT
DATENAME(month,DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0)) [Month],
COUNT(*) AS [Count_Of_Docs_In]
into #count
FROM N LEFT JOIN #build
ON DATEADD(mm,DATEDIFF(mm,0,'20130101')+n,0) = DATEADD(mm,DATEDIFF(mm,0,[Date_Docs_In_Complete__c]),0)
group by n

--Joining Count and days table together --
select
Month,
Count_Of_Docs_In,
Days,
Days/Count_Of_Docs_In as AVG_Days
from #count
left join #months on #months.Month_Date = #count.month

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-28 : 15:17:25

Try this:


select datepart(yyyy,date_docs_in_complete__c) as year
      ,datepart(mm,date_docs_in_complete__c) as month
      ,count(*) as count_of_docs_in
      ,sum(datediff(dd,createddate,date_docs_in_complete__c)) as days
      ,avg(datediff(dd,createddate,date_docs_in_complete__c)) as avg_days
  from #build
 group by datepart(yyyy,date_docs_in_complete__c)
         ,datepart(mm,date_docs_in_complete__c)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources