Pageview – Quick Report

SELECT (
		SELECT [projectname]
		FROM [tblRIMicroSite_Forms]
		WHERE CHARINDEX([tblRIMicroSite_Forms].[url], pv.[url]) > 0
		) AS Project
	,dbo.fnFormatDate([datetime], 'MM-DD-YYYY HH:NN AMPM', NULL) AS [DateTime]
	,[ipaddress] AS 'IP Address'
	,[ipint] AS 'IP Int'
	,[IPLocation] AS 'IP Location'
	,[mobile] AS 'Mobile'
	,(
		CASE
			WHEN (
					SELECT url
					FROM [tblRIMicroSite_Forms]
					WHERE CHARINDEX([tblRIMicroSite_Forms].[url], pv.[url]) > 0
					) IS NULL
				THEN pv.url
			ELSE (
					SELECT url
					FROM [tblRIMicroSite_Forms]
					WHERE CHARINDEX([tblRIMicroSite_Forms].[url], pv.[url]) > 0
					)
			END
		) AS URL
FROM [pageview] pv
ORDER BY [datetime]

SELECT projectname
	,submissiondate
	,firstname
	,initial
	,lastname
	,city
	,STATE
	,zipcode
	,phone
	,email
	,dob
	,interest
	,question
	,[mobile]
FROM tblRIMicroSite_Results
INNER JOIN tblRIMicroSite_Forms ON tblRIMicroSite_Results.form = tblRIMicroSite_Forms.id
LEFT OUTER JOIN pageview ON pageview.id = tblRIMicroSite_Results.pageview
WHERE form = 3
ORDER BY submissiondate