get average closing day - 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

SQL Server 2005 Forums

Transact-SQL (2005)

get average closing day

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-17 : 09:53:53

Hi

I have a table that I need to retrieve statistic from. The table have 2 datetime field, one start date and one end date and I would like to get statistics about how the avarege number of days between start date and end date like this...

less than one day 5 items
2-5 days 20 items
more that one week 4 items.

is this possible to achieve? and if so, how do I do it?

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 09:58:15

please post your table structure, sample data and the result that you want

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-17 : 10:22:42

Table structure...

ID, pk
ModelName, nvarchar(50)
StartDate, datetime
CloseDate, datetime
activeornot, int (0 or 1)

If a model is closed the activeornot is set to 1, and I need to find out the averrage number of days for activeornot=0 based on startdate and closedate.

like this...

1 day = 5 rows
1-3 days 39 rows

and so on....

Hope this helps

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 11:13:15

try this


select [1 day]      = case when days  = 1 then count(*) end,
       [1 - 3 days] = case when days <= 3 then count(*) end,
       [4 - 6 days] = case when days <= 6 then count(*) end
from
(
    select days = datediff(day, StartDate, CloseDate)
    from   table
    where  activeornot = 1
) d

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-17 : 12:35:47

Hi

I run this...

SELECT     CASE WHEN days = 1 THEN COUNT(*) END AS [1 day], CASE WHEN days <= 3 THEN COUNT(*) END AS [1 - 3 days], CASE WHEN days <= 6 THEN COUNT(*) 
                      END AS [4 - 6 days]
FROM         (SELECT     DATEDIFF(day, DateReported, DateCleared) AS days
                       FROM          dbo.tbl_Error_Reports
                       WHERE      (CaseActive = 1)) AS d

But get this error...

column d.days is invalid in the selected list beacuse it is not contained in either an aggregate funtion or the goup by clause.

What do I need to change?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 21:03:16

[code]
select [1 day] = count(case when days = 1 then 1 end),
[1 - 3 days] = count(case when days <= 3 then 1 end),
[4 - 6 days] = count(case when days <= 6 then 1 end)
from
(
select days = datediff(day, StartDate, CloseDate)
from table
where activeornot = 1
) d
[/code]

magmo
Aged Yak Warrior

558 Posts

Posted - 2007-06-17 : 23:29:27

Hi again

works like a charm, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources