OttawayMostClickedSP.sql

USE [custom]
GO
/****** Object:  StoredProcedure [dbo].[usp_web_GetMostClicked_SSW]    Script Date: 04/27/2012 15:35:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_web_GetMostClicked_SSW] 
@tcSiteCode char (4),
@tcClickType char (1),
@tcCount int = 10
AS
/******************************************************************************
**		FILE: usp_web_GetMostClicked_SSW.sql
**		NAME: usp_web_GetMostClicked_SSW
**		DESC:        
**		RETURN VALUES:
** 
**		CALLED BY:   
**              
**		PARAMETERS:
**		INPUT					OUTPUT
**     	---------				-----------
**      @tcSiteCode      
**      @tcClickType
**      @tcCount
**								
**		AUTH: Eric Bonito
**		DATE: 1/16/2006
*******************************************************************************
**		CHANGE HISTORY
*******************************************************************************
**		DATE:		AUTHOR:				DESCRIPTION:
**		--------	--------			---------------
**   	1/27/2006       EB
**   	6/26/2007       EB              Add support for multi site newslists
**   	1/09/2009       EB              Added check for Net Head first before returning title
**   	2/17/2009       EB              Added a larger maximum count - 10 to 20
**   	6/30/2010       EB              Added fix for PubDate in WHERE clause
**      5/01/2012		SW				Added Access Control Indentifier return value and changed capatilization
*******************************************************************************/
SET NOCOUNT ON
DECLARE @sqlString varchar(4000), @tmpCount int
IF @tcCount > 20
 BEGIN
  SELECT @tmpCount = 20
 END
ELSE
 BEGIN
  SELECT @tmpCount = @tcCount	
 END
SET ROWCOUNT @tmpCount
SELECT [cv].[customInsight] AS Url,
  CASE WHEN  LEN([wa].[Nett_Tittel]) > 1 OR [wa].[Nett_Tittel] IS NOT NULL THEN 
      [wa].[Nett_Tittel]
	ELSE 
	  [wa].[Tittel1]
	END AS Headline, [cv].[PageViews] AS Visits, [wk].[Ledetekst] AS CategoryName, [cv].[ArticleDate] AS oniArtDate, [ac].[Identifier] AS Identifier
FROM [custom].dbo.[uv_GetAllMostClicked] cv  (nolock)
    INNER JOIN [web].dbo.[Artikkler_hode] wa (nolock) ON (cv.sitecode = wa.avis AND cv.ArticleDate = wa.dato AND [cv].[ArticleCategory]=wa.kategori AND [cv].[ArticleGuid] = [wa].[Lopenr])
	INNER JOIN [web].dbo.[Kategorier] wk (nolock) ON (cv.sitecode=wk.avis AND [cv].[ArticleCategory] = [wk].[Kategori])
	INNER JOIN [web].dbo.[Artikkler_status] aks (nolock) ON (cv.sitecode = aks.avis AND cv.ArticleDate = aks.dato AND [cv].[ArticleCategory]=aks.kategori AND [cv].[ArticleGuid] = aks.[Lopenr])
	INNER JOIN [webextras].dbo.[AccessControl] ac (nolock) ON (cv.sitecode = ac.site)
	WHERE [cv].[Display] = 1 AND [cv].[SiteCode] = @tcSiteCode AND [cv].[ClickType] = @tcClickType AND [aks].[Status] = 9 
GROUP BY [cv].[PageViews], [cv].[customInsight],[wa].[Nett_Tittel],[wa].[Tittel1], [wk].[Ledetekst], [cv].[ArticleDate], [ac].[Identifier] 
ORDER BY [cv].[PageViews] DESC
RETURN