query help - 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)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-28 : 13:28:36

i have a query

select site,avg(datediff(n,entrydate,exitdate)) as avgduration from dataanal group by site

how can i separate this by weekday and weekend

so it would return

site , weekend average, weekday average

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-28 : 13:38:14

do a case statement

Select site, case when Datepart(dw, entrydate) IN (1,7) then avg(datediff(n,entrydate,exitdate)) end as avgduration_weekend,
case when Datepart(dw, entrydate) IN (2,3,4,5,6) then avg(datediff(n,entrydate,exitdate)) end as avgduration_weekday from dataanal group by site

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-28 : 14:59:55

quote:
Originally posted by DP978

do a case statement

Select site, case when Datepart(dw, entrydate) IN (1,7) then avg(datediff(n,entrydate,exitdate)) end as avgduration_weekend,
case when Datepart(dw, entrydate) IN (2,3,4,5,6) then avg(datediff(n,entrydate,exitdate)) end as avgduration_weekday from dataanal group by site

Hmmm need to be careful about this..Datepart operates base don the value of DATEFIRST. The default is 7 (Sunday).

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-28 : 15:54:11

Well you could go DateName = 'Saturday' or 'Sunday' then if you prefer...

I was just going by my setting and used 7 and 1. You are probably correct to be careful though.

He could just check how his system sets a sunday and adjust.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-28 : 16:01:38

Yeah thats what I meant..OP might need to change the values as per his settings..

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-28 : 16:04:36

Ya, thanks for the word of caution tho, I would never have thought of that.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-29 : 04:03:47

Select site, case when Datepart(dw, entrydate) IN (1,7) then avg(datediff(n,entrydate,exitdate)) end as avgduration_weekend,
case when Datepart(dw, entrydate) IN (2,3,4,5,6) then avg(datediff(n,entrydate,exitdate)) end as avgduration_weekday from dataanal group by site

gives me

Msg 8120, Level 16, State 1, Line 2
Column 'dataanal.entrydate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'dataanal.entrydate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

also how can i check what my default setting is for sunday?

Sachin.Nand

2937 Posts

Posted - 2010-01-29 : 04:13:09

select site,avg(avgduration_weekend),avg(avgduration_weekday)from
(
Select site, case when Datepart(dw, entrydate) IN (1,7) then (datediff(n,entrydate,exitdate)) end as avgduration_weekend,
case when Datepart(dw, entrydate) IN (2,3,4,5,6) then (datediff(n,entrydate,exitdate)) end as avgduration_weekday from dataanal
)T
group by site

U can use select @@datefirst to check default setting.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources