How to get avg over multiple days

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)

How to get avg over multiple days

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-07-13 : 12:47:40

Have following query:


declare @DateTo_yyyy_mm_dd as char(12)
set @DateTo_yyyy_mm_dd = cast(convert(varchar(8), current_timestamp, 112) as datetime)

declare @DateFrom_yyyy_mm_dd as char(12)
set @DateFrom_yyyy_mm_dd = cast(convert(varchar(8), current_timestamp - @DaysBack, 112) as datetime)


select   s1.SiteCode as SiteCode,
         SUM(CASE WHEN (t1.DateOut >= @00_00_00 and t1.DateOut <= @00_59_59) THEN t1.NetWeight ELSE 0 END) AS qty_0000_0059,
         SUM(CASE WHEN (t1.DateOut >= @01_00_00 and t1.DateOut <= @01_59_59) THEN t1.NetWeight ELSE 0 END) AS qty_0100_0159,
         SUM(CASE WHEN (t1.DateOut >= @02_00_00 and t1.DateOut <= @02_59_59) THEN t1.NetWeight ELSE 0 END) AS qty_0200_0259,
         SUM(CASE WHEN (t1.DateOut >= @03_00_00 and t1.DateOut <= @03_59_59) THEN t1.NetWeight ELSE 0 END) AS qty_0300_0359
 from    [wrmi-sql].AutoLoad.dbo."transaction" as t1
         right join [wrmi-sql].AutoLoad.dbo.site as s1 on s1.ekSiteId = t1.fkSiteId   
where    (@SiteCode IS null or s1.SiteCode = @SiteCode or s1.SiteCode like replace(@SiteCode,'*','%')) and
         datepart(dw, t1.TicketDate) not in (1, 7) and
         cast(convert(varchar(12), t1.TicketDate, 112) as datetime) >= @DateFrom_yyyy_mm_dd and
         cast(convert(varchar(12), t1.TicketDate, 112) as datetime) <  @DateTo_yyyy_mm_dd  
group by s1.SiteCode 
order by s1.SiteCode 
end

1. Instead of getting the SUM, I need the AVERAGE for the date span, I guess I need to find out how many days are included in my selection?
2. Also I need to exclude Sat and Sun (I hope my code is correct)

Thank you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-13 : 13:47:00

for average just change to:

AVG(CASE WHEN (t1.DateOut >= @00_00_00 and t1.DateOut <= @00_59_59) THEN t1.NetWeight ELSE NULL END) AS qty_0000_0059

for excluding weekends perhaps in your WHERE clause:
AND datename(weekday, t1.TicketDate) not in ('Saturday','Sunday')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources