upgrading SP - 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)

upgrading SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-01-24 : 18:42:03

Hi,

I have the following SPROC which is working fine, however I would like to extend a little bit of functionality to it. I want to bring back and extra column that displays a value that shows the average number of views per day. (MapViewCount / totalDays)

TotalDays can be determined by either getting the MIN(viewDate) from tblMapViews_Log and subtracting it from getDate()

or it can be grabbed from [tblMapDetails.dateCreated] with a JOIN on [mapID]

I'm not exactly sure how to form this together... can anyone lend a hand on this one?

very much appreciated!!

thanks again!
mike123

CREATE PROCEDURE dbo.select_TopMaps

AS SET NOCOUNT ON

SELECT TOP 200 mapID, COUNT(mapID) as MapViewCount FROM tblMapViews_Log

GROUP BY mapID
ORDER BY MapViewCount desc

GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 19:48:46

try this


SELECT TOP 200 mapID, COUNT(mapID) as MapViewCount,
      COUNT(mapID) / min(datediff(day, viewDate, getdate())) as MapViewAverage
FROM  tblMapViews_Log
GROUP BY mapID
ORDER BY MapViewCount desc

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-01-24 : 21:18:28

Hi Khtan

I'm getting the following error, not sure how I would resolve it.

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

any idea?

Thanks very much !! :)
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 21:21:38

yes. viewdate = today. How do you want to handle this scenario ? display avg as 0 ?


SELECT TOP 200 mapID, COUNT(mapID) as MapViewCount,
      case when min(datediff(day, viewDate, getdate())) <> 0 
           then COUNT(mapID) / min(datediff(day, viewDate, getdate())) 
           else 0
           end  as MapViewAverage
FROM  tblMapViews_Log
GROUP BY mapID
ORDER BY MapViewCount desc

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-01-25 : 03:28:52

perfect thanks!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources