Average days - 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

Author

Topic

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 08:18:44

I have a date like may 15, 2009 may 20, 2009 and May 24 2009 i want to get the average days of that. how can i do that into sql statement? is it posibble? thanks! Please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 08:32:33

[code]
declare @dates table
(
[date] datetime
)

insert into @dates
select '2009-05-15' union all
select '2009-05-20' union all
select '2009-05-24'

select dateadd(day, 0, avg(datediff(day, 0, [date])))
from @dates[/code]

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 08:43:58

i did this query but i got an error message "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

I have other filters in the query where should i place them? i got all the dates in one column.

SELECT c.Mydate
FROM mmc.[MyTable] c
LEFT JOIN mmc.ContractChangeStatusTransaction ccst
ON c.ContractChangeId = ccst.ContractChangeId

WHERE
ccst.ChangeStatusCd = 008003
AND (ccst.Updatedttm = dateadd(day, 0, avg(datediff(day, 0, ccst.Updatedttm))))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 08:46:29

quote:
Originally posted by Limuh

i did this query but i got an error message "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

I have other filters in the query where should i place them?

SELECT DISTINCT c.MyNbr
FROM mmc.[MyTable] c
LEFT JOIN mmc.ContractChangeStatusTransaction ccst
ON c.ContractChangeId = ccst.ContractChangeId

WHERE
ccst.ChangeStatusCd = 008003
AND (ccst.Updatedttm = dateadd(day, 0, avg(datediff(day, 0, ccst.Updatedttm))))

what are you trying to do with your query here ?

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 08:50:59

Please dis regard the query i wrote, i want to get the average days of the updatedate column but i have other filters to include.
The below query works but i want to display the Average Days not the average date. Please help.

declare @dates table
(
[date] datetime
)

insert into @dates
select '2009-05-15' union all
select '2009-05-20' union all
select '2009-05-24'

select dateadd(day, 0, avg(datediff(day, 0, [date])))
from @dates

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 09:09:59

the average days with reference to what date ?

what is the avg days for the 3 days you gave in your 1st post ?

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 09:15:09

yes the average days of the first post, but i get all those date to one column in the table. I want my query be mot specific on the date, What i want is to get all dates in the column of the table and get the average days,

Example
Table A
name Indicator date Status
Ane Y 2009-05-15 Draft
jane 2009-05-20 Draft
perry N 2009-05-24 In - Review
perry 2009-05-29 Draft

All i want is to get the draft statuses and i want to get the average days of all that draft statuses.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 09:17:27

quote:
Originally posted by Limuh

yes the average days of the first post, but i get all those date to one column in the table.

Yes. But you said you want Average Days not date.
so for these date 2009-05-15, 2009-05-20, 2009-05-24,
what is the "days" for 2009-05-15 ?
what is the "days" for 2009-05-20 ?
what is the "days" for 2009-05-24 ?
and what is the average days for these 3 date ?

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 09:24:00

yes the average days of the first post, but i get all those date to one column in the table. I want my query be mot specific on the date, What i want is to get all dates in the column of the table and get the average days,

Example
Table A
name---- Indicator-----date--------Status
Ane------- Y -------2009-05-15-----Draft
jane----------------2009-05-20-----Draft
perry----- N -------2009-05-24-----In - Review
perry---------------2009-05-29-----Draft

All i want is to get the draft statuses and i want to get the average days of all that draft statuses.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 09:29:49

so what is the expected result looks like ?

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 09:44:52

sorry this is to clear out the confusion date today (getdate() - update date from table) = value

example 2009-05-15 - todays date = 12 days
2009-05-20 - todays date = 7 days
2009-05-29 - todays date = 2 days

sum 12 + 7 + 2 = 21/3 = 7 if there is a decimal number roud it off to the nearest whole number. 7.5 up = 8 7.4 down = 7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 09:49:00

[code]SELECT AVG(DATEDIFF(dd,date,getdate()))
FROM TableA
WHERE Status='Draft'
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 09:49:53

[code]
declare @dates table
(
[date] datetime
)

insert into @dates
select '2009-05-15' union all
select '2009-05-20' union all
select '2009-05-17'

select round(avg(datediff(day, [date], getdate()) * 1.0), 0)
from @dates[/code]

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 21:00:22

thank you for the reply.. But what if the status is change to In-review then back again to draft. i only want to get the draft status excluding the previous date value.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 21:19:21

quote:
Originally posted by Limuh

thank you for the reply.. But what if the status is change to In-review then back again to draft. i only want to get the draft status excluding the previous date value.

example ?

maybe provide more sample data and also the corresponding required result from the sample data

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 21:27:36

Example
example is when data A = draft then it was change to in-review then back again to draft. i just need to get the date of the current date of the draft not the first one.

i want the MAX date of that specific DRAFT status..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 21:32:59

quote:
Originally posted by Limuh

Example
example is when data A = draft then it was change to in-review then back again to draft. i just need to get the date of the current date of the draft not the first one.

Can you post your sample table DDL (create table script),
sample data (insert into .. . select . . . )
and also the expected result ? (use the [ code ] tag)


col1   col2    col3
xxxx   xxxx    xxxx
xxxx   xxxx    xxxx

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 21:46:33

name---- Indicator-----date--------Status
Ane------- Y -------2009-05-15-----Draft
jane----------------2009-05-20-----Draft
perry---------------2009-05-24-----Draft --> not this one
perry---------------2009-05-29-----Draft --> this only the records i want to pull

RESULT:

2009-05-15
2009-05-20
2009-05-29

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 21:50:33

[code]
select *
from
(
select *, seq_no = row_number() over (partition by name order by date desc)
from yourtable
where Status = 'Draft'
) d
where d.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 22:13:03

below is what i did i dont know how to make this query the same as you did above..Please help

SELECT ROUND(AVG(DATEDIFF(day, ccst.UpdateDttm, getdate()) * 1.0),0) as [Average Days]
FROM mmc.ContractChangeStatusTransaction ccst
JOIN mmc.[ContractChange] cc
ON cc.ContractChangeId = ccst.ContractChangeId
JOIN mmc.[Contract] c
ON cc.ContractNbr = c.ContractNbr
JOIN mmc.UserAccessDetail uad
ON ald.UserAccessDetailId = uad.UserAccessDetailId
WHERE
ccst.ContractChangeStatusCd = 'Draft'
AND ccst.CurrentStatusInd = 'Y'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 22:18:30

[code]
SELECT ROUND(AVG(DATEDIFF(day, ccst.UpdateDttm, getdate()) * 1.0),0) as [Average Days]
FROM (
SELECT UpdateDttm,
seq_no = ROW_NUMBER() OVER (ORDER BY UpdateDttm DESC)
FROM mmc.ContractChangeStatusTransaction
WHERE ccst.ContractChangeStatusCd = 'Draft'
AND ccst.CurrentStatusInd = 'Y'
) ccst
JOIN mmc.[ContractChange] cc ON cc.ContractChangeId = ccst.ContractChangeId
JOIN mmc.[Contract] c ON cc.ContractNbr = c.ContractNbr
JOIN mmc.UserAccessDetail uad ON ald.UserAccessDetailId = uad.UserAccessDetailId
WHERE ccst.seq_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources