Average Days Open - tricky - SQL Server Forums

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

Average Days Open - tricky

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-06-18 : 06:18:18

Hi everyone,
Any help on this tricky problem would be greatly appreciated , we have a table called enablerscollector that has data in the format below

Type(char100),status(char100), CreateDate(char100),CloseDate(char100)
Obs Approved 2014-06-17 16:35:27 2014-06-17 00:00:00
Obs Approved 2014-06-15 10:00:00 2014-06-16 00:00:00
Kaiz Approved 2014-06-15 11:00:00 2014-06-16 00:00:00
Obs Recorded 2014-06-15 10:00:00 2014-06-15 00:00:00
Obs For Approval 2014-06-12 11:00:00 0001-01-01 00:00:00

I want to calculate the average number of calendar days that a Type 'Obs' has been open for , on a rolling 12months

Point to note is that if something is still open it shows closedate as 0001-01-01 00:00:00

If something is still open beyond 12months it should still be added to the average until it is closed

Hope this is not too difficult for all you genius`s out there :) any any help is greatly greatly appreciated as always

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-18 : 07:45:40

Try this:

With Obs
As
(
Select DateDiff(d, t.[CreateDate], t.[CloseDate]) as DaysOpen
From MyTable t
Where t.[Type] = 'Obs'
And t.[ClosedDate] Is Null
Or t.[ClosedDate] = ''
Or t.[ClosedDate] = '0001-01-01 00:00:00'
And t.[CreateDate] between '2014-01-01 00:00:00' and '2014-12-31 23:59:59' --change dates here
)
Select
AVG(DaysOpen)
From Obs

**********************************************************************
Or simply:

Declare @StartDate as DateTime = '2014-01-01 00:00:00' -- change date here
Declare @EndDate as DateTime = '2014-12-31 23:59:59' -- change date here

Select Avg(DateDiff(d, t.[CreateDate], t.[CloseDate])) as AVGDaysOpen
From MyTable t
Where t.[Type] =' Obs'
And t.[ClosedDate] Is Null
OR t.[ClosedDate] = ''
Or t.[ClosedDate] = '0001-01-01 00:00:00'
And Convert(Date,i.[CreateDate]) = @StartDate
And Convert(Date,i.[ClosedDate]) = @EndDate
Go

********************************************************************
Using CASE to breakdown each month

Declare @StartDate as DateTime = '2014-01-01 00:00:00' -- change date here
Declare @EndDate as DateTime = '2014-12-31 23:59:59' -- change date here
Declare @Month as int = Month(t.[CreateDate])

Select
January = Case
When t.[Type] =' Obs'
And t.[ClosedDate] Is Null
Or t.[ClosedDate] = ''
Or t.[ClosedDate] = '0001-01-01 00:00:00'
And @Month = 1
Then Avg(DateDiff(d, t.[CreateDate], t.[CloseDate]))
End,
February = Case
When t.[Type] =' Obs'
And t.[ClosedDate] Is Null
Or t.[ClosedDate] = ''
Or t.[ClosedDate] = '0001-01-01 00:00:00'
And @Month = 2
Then Avg(DateDiff(d, t.[CreateDate], t.[CloseDate]))
End,
March = Case
When t.[Type] =' Obs'
And t.[ClosedDate] Is Null
Or t.[ClosedDate] = ''
Or t.[ClosedDate] = '0001-01-01 00:00:00'
And @Month = 3
Then Avg(DateDiff(d, t.[CreateDate], t.[CloseDate]))
End
From MyTable t

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources