question

If I make use of the new SQL Server 2008 Date and Time data types would that be wrong because it doesn't downgrade gracefully to SQL Server 2005 and backwards?

Ok, I'll go first then. I am sure this will give some ideas to other how to solve different tasks. This is "Peso 1a 20100216". Matt, start to time this to give some sort of baseline.

The setup is extensive

ALTER TABLE dbo.IISAudit
ALTER COLUMN    DateStr DATETIME

ALTER TABLE dbo.IISAudit
ALTER COLUMN    TimeStr DATETIME

ALTER TABLE dbo.IISAudit
ADD 	TimeSpan AS CAST(86400 AS BIGINT) * DATEDIFF(DAY, 0, DateStr) + DATEDIFF(SECOND, 0, TimeStr) PERSISTED

ALTER TABLE dbo.IISAudit
ADD 	pageTime AS DateStr + TimeStr PERSISTED

ALTER TABLE dbo.IISAudit
ADD 	ValidExt AS	CASE SUBSTRING(csUriStem, CHARINDEX('.', csUriStem), 5)
    				WHEN '.asp' THEN 1
    				WHEN '.aspx' THEN 1
    				WHEN '.htm' THEN 1
    				WHEN '.html' THEN 1
    				WHEN '.php' THEN 1
    				ELSE 0
    			END PERSISTED

CREATE INDEX    IX_ValidExt
ON  	dbo.IISAudit
    	(
    		ValidExt
    	)
INCLUDE 	(
    		cIP,
    		TimeSpan,
    		DateStr,
    		pageTime,
    		csUriStem
    	)

CREATE UNIQUE
INDEX   	UCX_IP
ON  	dbo.ip_group_country
    	(
    		ip_start
    	)
INCLUDE 	(
    		country_code
    	)

The teardown is just ridicolous

DROP INDEX  IX_ValidExt
ON  	dbo.IISAudit

ALTER TABLE dbo.IISAudit
DROP COLUMN ValidExt

ALTER TABLE dbo.IISAudit
DROP COLUMN pageTime

ALTER TABLE dbo.IISAudit
DROP COLUMN TimeSpan

DROP INDEX  UCX_IP
ON  	dbo.ip_group_country

ALTER TABLE dbo.IISAudit
ADD 	wTemp CHAR(10)

GO

UPDATE  dbo.IISAudit
SET wTemp = CONVERT(CHAR(10), DateStr, 120)

ALTER TABLE dbo.IISAudit
ALTER COLUMN    DateStr VARCHAR(255)

UPDATE  dbo.IISAudit
SET DateStr = wTemp

UPDATE  dbo.IISAudit
SET wTemp = CONVERT(CHAR(8), TimeStr, 8)

ALTER TABLE dbo.IISAudit
ALTER COLUMN    TimeStr VARCHAR(255)

UPDATE  dbo.IISAudit
SET TimeStr = wTemp

ALTER TABLE dbo.IISAudit
DROP COLUMN wTemp

And here is the main part

SET NOCOUNT ON

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Goal
    	(
    		theURL VARCHAR(255),
    		CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
    		(
    			theURL
    		)
    	)

INSERT  #Goal
    (
    	theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

CREATE TABLE    #Stats
    	(
    		theIP VARCHAR(15),
    		visitID INT,
    		pageDate DATETIME,
    		pageTime DATETIME,
    		theURL VARCHAR(255),
    		isGoal TINYINT,
    		Country_Code VARCHAR(2),
    		theFirst INT,
    		Bounce INT,
    		MaxPages INT,
    		theTime INT
    	)

CREATE INDEX IX_Stats ON #Stats (theIP, visitID, pageTime)

INSERT  	#Stats
    	(
    		theIP,
    		visitID,
    		pageDate,
    		pageTime,
    		theURL,
    		isGoal,
    		Bounce
    	)
SELECT  	i.cIP AS theIP,
    	i.TimeSpan / @SessionTimeout AS VisitID,
    	i.DateStr AS pageDate,
    	i.pageTime,
    	i.csUriStem AS theURL,
    	CASE
    		WHEN g.theURL IS NULL THEN 0
    		ELSE 1
    	END AS isGoal,
    	0 AS Bounce
FROM    	dbo.IISAudit AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
WHERE   	i.ValidExt = 1

DROP TABLE  #Goal

DECLARE curYak CURSOR FOR
    	SELECT		l.theIP,
    			l.visitID AS fromID,
    			r.visitID AS toID
    	FROM		(
    				SELECT		theIP,
    						visitID,
    						MAX(pageTime) AS pageTime
    				FROM		#Stats
    				GROUP BY	theIP,
    						visitID
    			) AS l
    	INNER JOIN	(
    				SELECT		theIP,
    						visitID,
    						MIN(pageTime) AS pageTime
    				FROM		#Stats
    				GROUP BY	theIP,
    						visitID
    			) AS r ON r.theIP = l.theIP
    				AND r.visitID = l.visitID + 1
    	WHERE		DATEDIFF(SECOND, l.pageTime, r.pageTime) <= @SessionTimeout
    	ORDER BY	l.visitID

DECLARE @IP VARCHAR(15),
    @fromID INT,
    @toID INT

OPEN    curYak

FETCH   NEXT
FROM    curYak
INTO    @IP,
    @fromID,
    @toID

WHILE @@FETCH_STATUS = 0
    BEGIN
    	UPDATE	#Stats
    	SET	visitID = @toID
    	WHERE	theIP = @IP
    		AND visitID = @fromID

    	FETCH	NEXT
    	FROM	curYak
    	INTO	@IP,
    		@fromID,
    		@toID
    END

CLOSE   	curYak
DEALLOCATE  curYak

CREATE TABLE    #Lookup
    	(
    		theIP VARCHAR(15),
    		Country_Code VARCHAR(2)
    	)

INSERT  	#Lookup
    	(
    		theIP
    	)
SELECT  	theIP
FROM    	#Stats
GROUP BY    theIP

UPDATE  	l
SET 	l.Country_Code = igc.Country_Code
FROM    	#Lookup AS l
CROSS APPLY (
    		SELECT TOP(1)	w.Country_Code
    		FROM		dbo.Ip_Group_Country AS w
    		WHERE		w.ip_start <= CAST(16777216 AS BIGINT) * PARSENAME(l.theIP, 4) + 65536 * PARSENAME(l.theIP, 3) + 256 * PARSENAME(l.theIP, 2) + PARSENAME(l.theIP, 1)
    		ORDER BY	w.ip_start DESC
    	) AS igc

UPDATE  	s
SET 	s.Country_Code = l.Country_Code
FROM    	#Stats AS s
INNER JOIN  #Lookup AS l ON l.theIP = s.theIP	

DROP TABLE  #Lookup

UPDATE  f
SET theFirst = a,
    MaxPages = t,
    theTime = DATEDIFF(SECOND, theMin, theMax)
FROM    (
    	SELECT	theFirst,
    		ROW_NUMBER() OVER (PARTITION BY theIP, VisitID ORDER BY PageTime) AS a,
    		MaxPages,
    		COUNT(theURL) OVER (PARTITION BY theIP, visitID) AS t,
    		theTime,
    		MIN(pageTime) OVER (PARTITION BY theIP, visitID) AS theMin,
    		MAX(pageTime) OVER (PARTITION BY theIP, visitID) AS theMax
    	FROM	#Stats
    ) AS f

UPDATE  	s
SET 	s.Bounce = 1
FROM    	#Stats AS s
INNER JOIN  (
    		SELECT		theIP,
    				visitID,
    				MAX(CASE theFirst WHEN 1 THEN theURL ELSE NULL END) AS theURL
    		FROM		#Stats
    		WHERE		theFirst IN (1, MaxPages)
    		GROUP BY	theIP,
    				visitID
    		HAVING		MIN(theURL) = MAX(theURL)
    	) AS d ON d.theIP = s.theIP
    		AND d.visitID = s.visitid
WHERE   	s.theURL = d.theURL

-- 1
SELECT  	Country_Code,
    	PageDate AS DayOnly,
    	COUNT(DISTINCT visitID) AS VisitCount,
    	COUNT(theURL) AS [PageCount],
    	MAX(MaxPages) AS MaxPagesPerVisit,
    	CAST(AVG(1.0E * MaxPages) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
    	MAX(theTime) AS MaxTimeOnSite,
    	AVG(theTime) AS AvgTimeOnSite,
    	SUM(isGoal) AS GoalCount,
    	CAST(100.0E * SUM(Bounce) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM    	#Stats
GROUP BY    Country_Code,
    	PageDate
ORDER BY    PageDate,
    	Country_Code

-- 2
SELECT  	Country_Code,
    	COUNT(DISTINCT visitID) AS VisitCount,
    	COUNT(theURL) AS [PageCount],
    	MAX(MaxPages) AS MaxPagesPerVisit,
    	CAST(AVG(1.0E * MaxPages) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
    	MAX(theTime) AS MaxTimeOnSite,
    	AVG(theTime) AS AvgTimeOnSite,
    	SUM(isGoal) AS GoalCount,
    	CAST(100.0E * SUM(Bounce) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM    	#Stats
GROUP BY    Country_Code
ORDER BY    Country_Code

-- 3
SELECT  	theURL AS uriStem,
    	pageDate AS PageDay,
    	COUNT(*) AS [PageCount],
    	CAST(100.0E * SUM(CASE WHEN theFirst = 1 THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS EntryRate,
    	CAST(100.0E * SUM(CASE WHEN theFirst = MaxPages THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS ExitRate,
    	CAST(100.0E * SUM(Bounce) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM    	#Stats
GROUP BY    theURL,
    	PageDate
ORDER BY    PageDate,
    	theURL

DROP TABLE  #Stats

/*
    Phil1a --runs in 3.5 secs on my machine not including set up and tear down. 
    Sorry, didn't spot the code for the goal stuff but as it seemed to be all
    zeros in Reggie's results I just put a zero in. i'll add it for the next try
    DECLARE @timer TABLE
  (
   Time_ID INT,
   whatWasIt VARCHAR(30),
   whenWasIt DATETIME DEFAULT GETDATE()
  )*/

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary
CREATE TABLE #CountrySummary
  (
   CountryCode [varchar](2),
   DayOnly [datetime],
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary
CREATE TABLE #CountryOverallSummary
  (
   CountryCode [varchar](2),
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

----this is the main table used for processing the relevant log entries
IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session
CREATE TABLE #session
  (
   Surrogate INT IDENTITY(1, 1),
   TheDateTime DATETIME,
   URIStem VARCHAR(20),
   TheIP VARCHAR(15),
   session INT DEFAULT 0,
   firstdot INT,
   Seconddot INT,
   Thirddot INT,
   BigIP BIGINT,
   CountryCode VARCHAR(2),
   IsLastHit INT DEFAULT 0,
   IsFirstHit INT
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries
CREATE TABLE #Countries
  (
   surrogate_id INT IDENTITY(1, 1),
   Previous_IP BIGINT DEFAULT 0,
   IP_start BIGINT CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED,
   countryCode VARCHAR(2)
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate
CREATE TABLE #aggregate
  (
   [Pagecount] INT,
   [day] DATETIME,
   TheIP VARCHAR(15),
   Session INT,
   LastHit DATETIME,
   FirstHit DATETIME,
   CountryCode VARCHAR(2)
  )

-- INSERT  INTO @timer (WhatWasIt) SELECT  'start'

DECLARE @SessionTimeout INT, --how many seconds to assume new session
  @Session INT, --session number
  @LastDateTime DATETIME, --the date/time of the previous hit
  @ThePreviousIP VARCHAR(15),--the IP of the previous hit
  @firstdot INT, --the place in the IP string of the first dot
  @Seconddot INT,--the place in the IP string of the Second dot
  @Thirddot INT,--the place in the IP string of the third dot
  @bigIP BIGINT,--the int rendering of an IP address
  @IsFirstHit INT


SELECT  @SessionTimeout = 900, @Session = 0,
		@LastDateTime = '1 jan 2000', @ThePreviousIP = ''
--firstly, we get the relevant parts of the log table in a lightweight 
--temporary table
INSERT  INTO #session
		(TheDateTime, TheIP, URIStem)
		SELECT  TheDateTime, TheIP, csuriStem
		FROM    (SELECT CONVERT(DATETIME, datestr + ' ' + Timestr, 120)
						AS TheDateTime,
						LEFT(cip, 15) AS TheIP, csuriStem
				 FROM   iisaudit
				 WHERE  RIGHT(csuriStem, 5) IN ('.aspx',
						'.html') OR RIGHT(csuriStem, 4) IN (
						'.asp', '.htm', '.php')
				) f
		ORDER BY TheIP, TheDateTime
--and work out which seesion everything belongs to. This code relies 
--on a Quirky update

UPDATE  #session
SET     @Session = session = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
								  THEN @Session + 1
								  ELSE @Session
							 END,
		@isFirstHit = isFirstHit = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
										THEN 1
										ELSE 0
								   END,
		@LastDateTime = TheDateTime,
		@ThePreviousIP = TheIP,
		@firstdot = firstdot = CHARINDEX('.', TheIP, 1),
		@Seconddot = Seconddot = CHARINDEX('.', TheIP,
										   @firstdot + 1),
		@Thirddot = Thirddot = CHARINDEX('.', TheIP,
										 @SecondDot + 1),
		@BigIP = BigIP = CONVERT(BIGINT, 16777216) * (LEFT(TheIP, @FirstDot - 1)) + CONVERT(BIGINT, 65536) * (SUBSTRING(TheIP, @FirstDot + 1, @SecondDot - @FirstDot - 1)) + CONVERT(BIGINT, 256) * (SUBSTRING(TheIP, @SecondDot + 1, @ThirdDot - @SecondDot - 1)) + CONVERT(BIGINT, SUBSTRING(TheIP, @ThirdDot + 1, 20))

-- To lookup the geolocation data for an IP A.B.C.D, you need to find the row in [dbo].[ip_group_country] 
-- that has the highest [ip_start] value that is less than or equal to:
-- (A * 16777216) + (B * 65536) + (C * 256) + D
-- INSERT  INTO @timer (WhatWasIt) SELECT  'start loading countries'
--now we stock a country table in order to work out where the IP is from

INSERT  INTO #Countries
		(IP_Start, CountryCode)
		SELECT  ip_Start, Country_Code
		FROM    ip_group_country
		ORDER BY ip_start
--now we chamelessly do a quirky update to get the
-- bigint version of the previous IP address
-- INSERT  INTO @timer (WhatWasIt) SELECT  'update countries'

SELECT  @BigIP = 0
UPDATE  #countries
SET     @BigIP = Previous_IP = @BigIP,
		@BigIP = IP_Start

--create index IPStartPreviousIndex on #countries(Previous_IP,IP_Start)--
--create index BigIP_CoverTheDateTime on #Session(BigIP,TheDateTime)

--we only want to do this once as it is costly so we put
--it in a temporary table
-- INSERT  INTO @timer (WhatWasIt)  SELECT  'do the aggregate'

INSERT  INTO #aggregate
		SELECT  COUNT(*),
				DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0)
				AS day, TheIP, session, MAX(TheDateTime),
				MIN(TheDateTime), c.countryCode
		FROM    #session s INNER JOIN #countries c
				  ON BigIP BETWEEN Previous_IP AND IP_Start
		GROUP BY TheIP, session,
				DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0),
				c.countryCode

-- INSERT  INTO @timer (WhatWasIt) SELECT  'find last hits'
UPDATE  #session
SET     islasthit = 1
FROM    #session s INNER JOIN #aggregate
		  ON lastHit = theDateTime
-- INSERT  INTO @timer (WhatWasIt) SELECT  'report aggregates'
--and now we create the result tables.

INSERT  INTO [#CountrySummary]
		([CountryCode], [DayOnly], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], goalcount,
		 [BounceRate])
		SELECT  countryCode, day, COUNT(*), SUM([PageCount]),
				MAX([PageCount]), AVG([PageCount] * 1.0),
				MAX(DATEDIFF(s, [FirstHit], [LastHit])),
				AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
				SUM(CASE WHEN [PageCount] = 1 THEN 1
						 ELSE 0
					END) * 100.0 / (COUNT(*) * 1.0)
		FROM    #aggregate
		GROUP BY [CountryCode], day

INSERT  INTO [#CountryOverallSummary]
		([CountryCode], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], [GoalCount],
		 [BounceRate])
		SELECT  countryCode, COUNT(*), SUM([PageCount]),
				MAX([PageCount]), AVG([PageCount] * 1.0),
				MAX(DATEDIFF(s, [FirstHit], [LastHit])),
				AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
				SUM(CASE WHEN [PageCount] = 1 THEN 1
						 ELSE 0
					END) * 100.0 / (COUNT(*) * 1.0)
		FROM    #aggregate
		GROUP BY [CountryCode] 

Select * from [#CountryOverallSummary]

Select * from [#CountrySummary]

SELECT  uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0) AS PageDay,
		COUNT(*) AS pagecount,
		CONVERT([decimal](12, 2), SUM(isFirstHit) * 100.0 / COUNT(*))
		AS [EntryRate],
		CONVERT([decimal](12, 2), SUM(isLastHit) * 100.0 / COUNT(*))
		AS [ExitRate],
		CONVERT([decimal](12, 2), SUM(CASE WHEN isFirstHit + isLastHit = 2
										   THEN 1
										   ELSE 0
									  END) * 100.0 / COUNT(*))
		AS [BounceRate]
FROM    #session
GROUP BY uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0)
ORDER BY PageDay, uristem

/*-- INSERT  INTO @timer  (WhatWasIt)  SELECT  'end'
SELECT  * FROM    @timer*/

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate

/*Phil1b --runs in 2.5 secs on my machine not including set up and tear down. 
Again forgot the code for the goal stuff. It is a lot more accurate this time, and
where I've checked it against pesos and Reggie's code, mine is more accurate than 
either.

DECLARE @timer    TABLE
  (
   Time_ID INT IDENTITY(1, 1),
   whatWasIt VARCHAR(30),
   whenWasIt DATETIME DEFAULT GETDATE()
  ) */

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary
CREATE TABLE #CountrySummary
  (
   CountryCode [varchar](2),
   DayOnly [datetime],
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary
CREATE TABLE #CountryOverallSummary
  (
   CountryCode [varchar](2),
   VisitCount [int],
   [PageCount] [int],
   MaxPagesPerVisit [int],
   AvgPagesPerVisit [decimal](12, 2),
   MaxTimeOnSite [int],
   AvgTimeOnSite [int],
   GoalCount [int],
   BounceRate [decimal](12, 2)
  )

----this is the main table used for processing the relevant log entries
IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session
/*CREATE TABLE #session
  (
   Surrogate INT IDENTITY(1, 1),
   TheDay int,
   TheDateTime DATETIME,
   URIStem VARCHAR(20),
   TheIP VARCHAR(15),
   session INT DEFAULT 0,
   firstdot INT,
   Seconddot INT,
   Thirddot INT,
   BigIP BIGINT,
   IsLastHit INT DEFAULT 0,
   IsFirstHit INT
  )*/

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries
CREATE TABLE #Countries
  (
   surrogate_id INT IDENTITY(1, 1),
   Next_IP BIGINT DEFAULT 0,
   IP_start BIGINT,
   countryCode VARCHAR(2),
   CONSTRAINT idxClusterd PRIMARY KEY (IP_Start DESC)
  )


IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate
CREATE TABLE #aggregate
  (
   [Pagecount] INT,
   [day] INT,
   TheIP VARCHAR(15),
   bigIP BIGINT,
   Session INT,
   LastHit DATETIME,
   FirstHit DATETIME,
   LastSessionID INT,
   CountryCode VARCHAR(2)
  )

-- INSERT  INTO @timer (WhatWasIt) SELECT  'start'

DECLARE @SessionTimeout INT, --how many seconds to assume new session
  @Session INT, --session number
  @LastDateTime DATETIME, --the date/time of the previous hit
  @ThePreviousIP VARCHAR(15),--the IP of the previous hit
  @firstdot INT, --the place in the IP string of the first dot
  @Seconddot INT,--the place in the IP string of the Second dot
  @Thirddot INT,--the place in the IP string of the third dot
  @bigIP BIGINT,--the int rendering of an IP address
  @IsFirstHit INT,
  @NextCountry VARCHAR(2)


SELECT  @SessionTimeout = 900, @Session = 0,
		@LastDateTime = '1 jan 2000', @ThePreviousIP = ''
--firstly, we get the relevant parts of the log table in a lightweight 
--temporary table
SELECT  IDENTITY( INT,1, 1 ) AS Session_ID,
		CONVERT(INT, 0) AS TheDay, TheDateTime,
		CONVERT (VARCHAR(20), csURIStem) AS URIStem,
		CONVERT(VARCHAR(15), TheIP) AS TheIP, 0 AS session,
		0 AS firstdot, 0 AS Seconddot, 0 AS Thirddot,
		CONVERT(BIGINT, 0) AS BigIP, 0 AS IsLastHit,
		0 AS IsFirstHit,
		CONVERT(VARCHAR(2), ' ') AS countrycode
INTO    #session
FROM    (SELECT CONVERT(DATETIME, datestr + ' ' + Timestr, 120)
				AS TheDateTime, LEFT(cip, 15) AS TheIP,
				csuriStem
		 FROM   iisaudit
		 WHERE  RIGHT(csuriStem, 5) IN ('.aspx', '.html') OR RIGHT(csuriStem, 4) IN (
				'.asp', '.htm', '.php')
		) f
ORDER BY TheIP, TheDateTime
--and work out which seesion everything belongs to. This code relies 
--on a Quirky update

UPDATE  #session
SET     @Session = session = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
								  THEN @Session + 1
								  ELSE @Session
							 END,
		TheDay = DATEDIFF(d, 0, [TheDateTime]),
		@isFirstHit = isFirstHit = CASE WHEN TheIP <> @ThePreviousIP OR DATEDIFF(Second, @LastDateTime, TheDateTime) > @SessionTimeout
										THEN 1
										ELSE 0
								   END,
		@LastDateTime = TheDateTime,
		@ThePreviousIP = TheIP,
		@firstdot = firstdot = CHARINDEX('.', TheIP, 1),
		@Seconddot = Seconddot = CHARINDEX('.', TheIP,
										   @firstdot + 1),
		@Thirddot = Thirddot = CHARINDEX('.', TheIP,
										 @SecondDot + 1),
		@BigIP = BigIP = CONVERT(BIGINT, 16777216) * (LEFT(TheIP, @FirstDot - 1)) + CONVERT(BIGINT, 65536) * (SUBSTRING(TheIP, @FirstDot + 1, @SecondDot - @FirstDot - 1)) + CONVERT(BIGINT, 256) * (SUBSTRING(TheIP, @SecondDot + 1, @ThirdDot - @SecondDot - 1)) + CONVERT(BIGINT, SUBSTRING(TheIP, @ThirdDot + 1, 20))

-- To lookup the geolocation data for an IP A.B.C.D, you need to find the row in [dbo].[ip_group_country] 
-- that has the highest [ip_start] value that is less than or equal to:
-- (A * 16777216) + (B * 65536) + (C * 256) + D
-- INSERT  INTO @timer (WhatWasIt) SELECT  'start loading countries'
--now we stock a country table in order to work out where the IP is from

INSERT  INTO #Countries
		(IP_Start, CountryCode)
		SELECT  ip_Start, Country_Code
		FROM    ip_group_country

--now we chamelessly do a quirky update to get the
-- bigint version of the previous IP address
-- INSERT  INTO @timer (WhatWasIt) SELECT  'update countries'

SELECT  @BigIP = 0, @NextCountry = ''
UPDATE  #countries
SET     @BigIP = Next_IP = @BigIP - 1,
		@BigIP = IP_Start

--we only want to do this once as it is costly so we put
--it in a temporary table
-- INSERT  INTO @timer (WhatWasIt)  SELECT  'do the aggregate'
INSERT  INTO #aggregate
		SELECT  COUNT(*), Theday, MAX(TheIP), MAX(BigIP),
				session, MAX(TheDateTime), MIN(TheDateTime),
				MAX(Session_ID), 0--, countryCode
		FROM    #session
		GROUP BY session, TheDay
--, countryCode
--now do the countrycode
UPDATE  #aggregate
SET     countrycode = c.countrycode
FROM    #aggregate s INNER JOIN #countries c
		  ON BigIP BETWEEN IP_Start AND Next_IP

-- INSERT  INTO @timer (WhatWasIt) SELECT  'find last hits'
UPDATE  #session
SET     islasthit = 1
FROM    #session s INNER JOIN #aggregate
		  ON session_ID = LastSessionID
-- INSERT  INTO @timer (WhatWasIt) SELECT  'report aggregates'
--and now we create the result tables.

/*INSERT  INTO [#CountrySummary]
		([CountryCode], [DayOnly], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], goalcount,
		 [BounceRate])*/
SELECT  countryCode, DATEADD(d, day, 0), COUNT(*),
		SUM([PageCount]), MAX([PageCount]),
		AVG([PageCount] * 1.0),
		MAX(DATEDIFF(s, [FirstHit], [LastHit])),
		AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
		SUM(CASE WHEN [PageCount] = 1 THEN 1
				 ELSE 0
			END) * 100.0 / (COUNT(*) * 1.0)
FROM    #aggregate
GROUP BY [CountryCode], day
ORDER BY countrycode, day

/*INSERT  INTO [#CountryOverallSummary]
		([CountryCode], [VisitCount], [PageCount],
		 [MaxPagesPerVisit], [AvgPagesPerVisit],
		 [MaxTimeOnSite], [AvgTimeOnSite], [GoalCount],
		 [BounceRate])*/
SELECT  countryCode, COUNT(*), SUM([PageCount]),
		MAX([PageCount]), AVG([PageCount] * 1.0),
		MAX(DATEDIFF(s, [FirstHit], [LastHit])),
		AVG(DATEDIFF(s, [FirstHit], [LastHit])), 0,
		SUM(CASE WHEN [PageCount] = 1 THEN 1
				 ELSE 0
			END) * 100.0 / (COUNT(*) * 1.0)
FROM    #aggregate
GROUP BY [CountryCode]
ORDER BY countrycode


--Select * from [#CountrySummary]

SELECT  uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0) AS PageDay,
		COUNT(*) AS pagecount,
		CONVERT([decimal](12, 2), SUM(isFirstHit) * 100.0 / COUNT(*))
		AS [EntryRate],
		CONVERT([decimal](12, 2), SUM(isLastHit) * 100.0 / COUNT(*))
		AS [ExitRate],
		CONVERT([decimal](12, 2), SUM(CASE WHEN isFirstHit + isLastHit = 2
										   THEN 1
										   ELSE 0
									  END) * 100.0 / COUNT(*))
		AS [BounceRate]
FROM    #session
GROUP BY uriStem,
		DATEADD(d, DATEDIFF(d, 0, [TheDateTime]), 0)
ORDER BY PageDay, uristem


-- INSERT  INTO @timer  (WhatWasIt)  SELECT  'end'
/*SELECT  t.WhatWasIt, DATEDIFF(ms, t.WhenWasIt, y.WhenWasIt)
FROM    @timer t INNER JOIN @timer y
		  ON y.time_ID = t.time_ID + 1
UNION ALL
SELECT  'total',
		DATEDIFF(ms,
				 (SELECT WhenWasIt FROM @timer WHERE WhatWasIt
						  = 'start'
				 ),
				 (SELECT WhenWasIt FROM @timer WHERE WhatWasIt
						  = 'end'
				 )) 
    */

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountrySummary%' ) 
  DROP TABLE #CountrySummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#CountryOverallSummary%' ) 
  DROP TABLE #CountryOverallSummary

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#session%' ) 
  DROP TABLE #session

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#countries%' ) 
  DROP TABLE #countries

IF EXISTS ( SELECT  *
			FROM    tempDB.information_schema.tables
			WHERE   Table_name LIKE '#aggregate%' ) 
  DROP TABLE #aggregate

Ok, well this isn't an entry, it's just me doing my bit to persuade the people who don't believe there is a place for the CLR in SQL Server that maybe they should re-consider. I used Peso's PERSISTED computed column with the extensions, and another one to pre-cache the ip_start lookup value. This runs in 314ms on my box.

Setup:

ALTER TABLE [dbo].[IISAudit] ADD [id] [int] IDENTITY (1, 1) NOT NULL
GO

CREATE ASSEMBLY [SqlClassLibrary]
AUTHORIZATION [dbo]
FROM
0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030009B2814B0000000000000000E00002210B0108000026000000060000000000006E45000000200000006000000000400000200000000200000400000000000000040000000000000000A0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001C4500004F000000006000008003000000000000000000000000000000000000008000000C000000844400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000074250000002000000026000000020000000000000000000000000000200000602E7273726300000080030000006000000004000000280000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002C0000000000000000000000000000400000420000000000000000000000000000000050450000000000004800000002000500E82F00009C140000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300400460000000100001102280E00000A0000020E047D07000004020203250A7D01000004067D02000004020204250B7D03000004077D0400000402177D0500000402052D03162B01177D06000004002A00001330030036000000020000110002257B0500000417587D050000040516FE010A062D100002257B0600000417587D060000040002037D0200000402047D040000042A000013300200360000000200001102280E00000A000002037D09000004020F02280F00000A7D0A00000402177D0B0000040516FE010A062D090002177D0C00000400002A00001330030028000000020000110002257B0B00000417587D0B0000040316FE010A062D100002257B0C00000417587D0C000004002A133002003F00000003000011000374030000020A027C0A000004067B0A000004281000000A0B0716FE010D092D0500070C2B16027B09000004067B090000046F1100000A0B070C2B00082A00133004008B0000000400001102280E00000A000002037B080000047D0F00000402037C01000004280F00000A7D1000000402177D110000040202037B05000004250A7D12000004067D130000040202037B02000004037B01000004281200000A0B1201281300000A69250A7D14000004067D1500000402037B060000047D1600000402037B05000004172E03162B01177D17000004002A0013300400B5000000050000110002257B1100000417587D1100000402257B12000004037B05000004587D12000004037B05000004027B13000004FE0216FE010B072D0E0002037B050000047D1300000400037B02000004037B01000004281200000A0C1202281300000A690A02257B1400000406587D1400000406027B15000004FE0216FE010B072D090002067D150000040002257B16000004037B06000004587D1600000402257B17000004037B05000004172E03162B0117587D170000042A000000133002003F00000006000011000374040000020A027C10000004067B10000004281000000A0B0716FE010D092D0500070C2B16027B0F000004067B0F0000046F1100000A0B070C2B00082A00133004007A0000000400001102280E00000A000002037B080000047D1800000402177D190000040202037B05000004250A7D1A000004067D1B0000040202037B02000004037B01000004281200000A0B1201281300000A69250A7D1C000004067D1D00000402037B060000047D1E00000402037B05000004172E03162B01177D1F000004002A000013300400B5000000050000110002257B1900000417587D1900000402257B1A000004037B05000004587D1A000004037B05000004027B1B000004FE0216FE010B072D0E0002037B050000047D1B00000400037B02000004037B01000004281200000A0C1202281300000A690A02257B1C00000406587D1C00000406027B1D000004FE0216FE010B072D090002067D1D0000040002257B1E000004037B06000004587D1E00000402257B1F000004037B05000004172E03162B0117587D1F0000042A000000133002001E00000007000011000374050000020A027B18000004067B180000046F1100000A0B2B00072A00001B300900F909000008000011007201000070731500000A0A00066F1600000A00731700000A0B723100007006731800000A0C00086F1900000A0D002B10000709166F1A00000A6F1B00000A0000096F1C00000A131F111F2DE400DE120914FE01131F111F2D07096F1D00000A00DC0000DE120814FE01131F111F2D07086F1D00000A00DC0020A00F0000731E00000A1304727900007006731800000A0C00086F1900000A0D002B1800110409166F1F00000A09176F1A00000A6F2000000A0000096F1C00000A131F111F2DDC00DE120914FE01131F111F2D07096F1D00000A00DC0000DE120814FE01131F111F2D07086F1D00000A00DC00732100000A1305732200000A1306732300000A1307732400000A130872F401007006731800000A0C00086F1900000A0D0038220100000009186F1F00000A130909176F1A00000A130A09166F2500000A130B07110A6F2600000A130C16130D11061109120E6F2700000A16FE01131F111F2D5E00110E7C02000004026C282800000A110B282900000A16FE01131F111F2D1200110E110B110A110C6F0200000600002B2A00110B110A110C11097301000006130E11061109110E6F2A00000A001105110E6F2B00000A0017130D00002B2A00110B110A110C11097301000006130E11061109110E6F2C00000A001105110E6F2B00000A0017130D00110A120B721B030070282D00000A282E00000A130F1107110F12106F2F00000A16FE01131F111F2D0E001110110D6F0400000600002B2500110A110B110D730300000613101107110F11106F3000000A00110811106F3100000A000000096F1C00000A131F111F3ACFFEFFFF00DE120914FE01131F111F2D07096F1D00000A00DC0000DE120814FE01131F111F2D07086F1D00000A00DC00733200000A1311733300000A1312733400000A1313733500000A13140011056F3600000A1320384E0100001220283700000A130E00110E1104110E7B070000046F3800000A7D08000004110E7B04000004110E7C01000004721B030070282D00000A282E00000A1315110E7B08000004110E7C01000004721B030070282D00000A282E00000A13161114110E7B0800000412176F3900000A16FE01131F111F2D0E001117110E6F0A00000600002B2600110E730900000613171114110E7B0800000411176F3A00000A00111211176F3B00000A00001113111612186F3C00000A16FE01131F111F2D0E001118110E6F0700000600002B2100110E730600000613181113111611186F3D00000A00111111186F3E00000A00001107111512106F2F00000A16FE01131F111F2D40001110257B0D00000417587D0D000004110E7B03000004110E7B04000004283F00000A16FE01131F111F2D11001110257B0E00000417587D0E00000400002B0C007229030070734000000A7A001220284100000A131F111F3AA2FEFFFFDE0F1220FE160B00001B6F1D00000A00DC0011086F4200000A0011116F4300000A0011126F4400000A001F0A8D1F000001132111211672310300701F16186A734500000AA211211772490300701A734600000AA211211872590300701E734600000AA2112119726F0300701E734600000AA211211A72830300701E734600000AA211211B72A50300701B1F0C18734700000AA211211C72C70300701E734600000AA211211D72E30300701E734600000AA211211E72FF0300701E734600000AA211211F0972130400701B1F0C18734700000AA21121734800000A1319284900000A11196F4A00000A000011116F4B00000A1322380D0100001222284C00000A13180011191611187B0F0000046F4D00000A0011191711187B100000046F4E00000A0011187B12000004131A11187B11000004131B111918111B6F4F00000A00111919111A6F4F00000A0011191A11187B130000046F4F00000A0011191B111A285000000A17735100000A285200000A111B285000000A285300000A1817285400000A6F5500000A0011191C11187B150000046F4F00000A0011191D11187B14000004111B5B6F4F00000A0011191E11187B160000046F4F00000A0011191F0911187B17000004285000000A20E803000016161617735600000A285200000A111B285000000A285300000A1817285400000A6F5500000A00284900000A11196F5700000A00001222285800000A131F111F3AE3FEFFFFDE0F1222FE160C00001B6F1D00000A00DC00284900000A6F5900000A001F098D1F000001132111211672310300701F16186A734500000AA211211772590300701E734600000AA2112118726F0300701E734600000AA211211972830300701E734600000AA211211A72A50300701B1F0C18734700000AA211211B72C70300701E734600000AA211211C72E30300701E734600000AA211211D72FF0300701E734600000AA211211E72130400701B1F0C18734700000AA21121734800000A131C284900000A111C6F4A00000A000011126F5A00000A132338FC0000001223285B00000A131D00111C16111D7B180000046F4D00000A00111D7B1A000004131A111D7B19000004131B111C17111B6F4F00000A00111C18111A6F4F00000A00111C19111D7B1B0000046F4F00000A00111C1A111A285000000A17735100000A285200000A111B285000000A285300000A1817285400000A6F5500000A00111C1B111D7B1D0000046F4F00000A00111C1C111D7B1C000004111B5B6F4F00000A00111C1D111D7B1E0000046F4F00000A00111C1E111D7B1F000004285000000A20E803000016161617735600000A285200000A111B285000000A285300000A1817285400000A6F5500000A00284900000A111C6F5700000A00001223285C00000A131F111F3AF4FEFFFFDE0F1223FE160D00001B6F1D00000A00DC00284900000A6F5900000A001C8D1F000001132111211672290400701F1620FF0000006A734500000AA211211772390400701A734600000AA2112118726F0300701E734600000AA211211972490400701B1F0C18734700000AA211211A725D0400701B1F0C18734700000AA211211B72130400701B1F0C18734700000AA21121734800000A131E284900000A111E6F4A00000A000011086F5D00000A132438FD0000001224285E00000A131000111E1611107B090000046F4D00000A00111E1711107B0A0000046F4E00000A0011107B0B000004131A111E18111A6F4F00000A00111E1911107B0C000004285000000A20E803000016161617735600000A285200000A111A285000000A285300000A1817285400000A6F5500000A00111E1A11107B0D000004285000000A20E803000016161617735600000A285200000A111A285000000A285300000A1817285400000A6F5500000A00111E1B11107B0E000004285000000A20E803000016161617735600000A285200000A111A285000000A285300000A1817285400000A6F5500000A00284900000A111E6F5700000A00001224285F00000A131F111F3AF3FEFFFFDE0F1224FE160E00001B6F1D00000A00DC00284900000A6F5900000A0000DE120614FE01131F111F2D07066F1D00000A00DC002A000000410C0100020000002E0000002200000050000000120000000000000002000000260000004000000066000000120000000000000002000000990000002A000000C30000001200000000000000020000009100000048000000D90000001200000000000000020000001C0100003A0100005602000012000000000000000200000014010000580100006C020000120000000000000002000000A5020000650100000A0400000F0000000000000002000000FB040000240100001F0600000F0000000000000002000000F306000013010000060800000F0000000000000002000000B308000014010000C70900000F00000000000000020000000C000000D9090000E509000012000000000000001E02280E00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000006C060000237E0000D80600006406000023537472696E6773000000003C0D00007004000023555300AC110000100000002347554944000000BC110000E002000023426C6F620000000000000002000001571702080900000000FA0133001600000100000026000000060000001F0000000D00000013000000030000005F0000000C000000080000000E000000010000000200000000000A0001000000000006006900620006007000620006007C0062000600CB01B9010600E201B9010600FF01B90106001E02B90106003702B90106005002B90106006B02B90106008602B9010600BE029F020600D202B9010600FE02EB023B001203000006004103210306006103210306009803620006009F0362000A00EF03D4030A001B0405040A003C042904060069044E040A00700405040A007B0405040A00970429040600B30462000600C7044E045F003105000006006B0562000A008305D4030A008F05C8030A009905D4030A00A705D4030A00B205D4030600F305620006001F0662000600240662000000000001000000000001000100000110001E0000000500010001000001100024000000050009000300000110002D00000005000F000600000110003C0000000500180009000100100048000000050020000C00060085000A00060093000A000600A0000E000600AA000E000600B30011000600BD0011000600C70014000600CD000E000600E7000E000600F0000A000600B30011000600F40011000600FF0011000600090111000600CD000E000600F0000A000600230111000600B300110006002E01110006003B01110006004B0111000600BD0011000600090111000600CD000E000600230111000600B300110006002E01110006003B01110006004B0111000600BD0011000600090111005020000000008618D90017000100A420000000008600DF0020000500E820000000008618D900280008002C21000000008600150130000B00602100000000E601190135000C00AC21000000008618D9003A000D00442200000000860015013A000E00082300000000E601190135000F005423000000008618D9003A001000DC2300000000860015013A001100A02400000000E601190135001200CC24000000009600590140001300E02F000000008618D90045001400000001006501000002006E01000003007801000004008301000001006501000002008901000003007801000001008E01000002009701000003009B01000001009B0100000100A40100000100A80100000100A80100000100A40100000100A80100000100A80100000100A40100000100AA010300090004000900050009002100D90049002900D90049003100D90049003900D90049004100D90049004900D90049005100D90049005900D90049006100D90030006900D90049007100D9004E008100D90054008900D90045000900D900450019008F036300190019016800910019016E001900A8037B009900B7038400A100D9004500A900D9004900B100490445000C00D9004500C100D900AE00C1008904B500D100A404BA000C001501BF00D100AE04C500D900BF0445001400D9005400D100D404D00014001501D5001C00D90045002400D90045002C00D90045003400D9004500D100DD04FB000C00E90401012400F20407011900FE04100119000905160124001805D5001C001501BF0024001501D500190021051E0191002A0523012C00F20407012C001501D50034001501BF003C00D90045004400D90045004C00D90045005400D90045001C003C0547015C004A055701140056055C015400F204070154001501D50044001501BF004C00F20407014C001501D5003C001501BF0091005F056301F100D90049005C007505C50034007E0545003C007E05450044007E054500F900D9006901F900D9007201F900D9007A010901D90084011101BA058B011901C30591013C003C05470164004A0557010901D4059F010901DE05A5010901EA05AC012101FB05B2012101D900540021010706B90121011306B90129013506C50109013B06D2012101D900DA0119014606910164007505C50019015506450044003C0547016C004A0557016C007505C50034003C05470174004A05570174007505C5002E002B0074022E000B0074022E0013008E022E001B008E022E0023008E022E004B008E022E006B00BE022E003B008E022E00330094022E005B00AC022E006300B5028001A300A30059005F00730088008E0095009D00F101A800C900DD00E400EC00F4002901300137013F0150019801E301EA010480000001000000780E449D0000000000007F03000002000000000000000000000001005900000000000200000000000000000000000100C8030000000000000000003C4D6F64756C653E0053716C436C6173734C6962726172792E646C6C00566973697400506167655374617400436F756E7472794461795374617400436F756E747279537461740053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A6563740049436F6D70617261626C65004461746554696D650046697273745061676554696D65004C6173745061676554696D6500466972737450616765004C617374506167650050616765436F756E7400476F616C436F756E7400495042696700436F756E747279436F6465002E63746F72004164645061676500506167654E616D650044617900456E747279436F756E740045786974436F756E7400426F756E6365436F756E740041646400436F6D70617265546F005669736974436F756E74004D617850616765436F756E7400546F74616C54696D654F6E53697465004D617854696D654F6E53697465004949534C6F675061727365007061676554696D650066697273745061676500706167654973476F616C006970426967007061676500706167654E616D650064617900776173456E747279006F626A00760053657373696F6E54696D656F75740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C436C6173734C696272617279006765745F4461746500537472696E670054696D655370616E006F705F5375627472616374696F6E006765745F546F74616C5365636F6E64730053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053797374656D2E436F6C6C656374696F6E732E47656E65726963004C69737460310053716C436F6D6D616E640053716C4461746152656164657200457865637574655265616465720044624461746152656164657200476574537472696E6700526561640049446973706F7361626C6500446973706F73650044696374696F6E617279603200476574496E743634004765744461746554696D6500436F6E7461696E730054727947657456616C7565004164645365636F6E6473006F705F477265617465725468616E007365745F4974656D00546F537472696E6700436F6E63617400456E756D657261746F7200476574456E756D657261746F72006765745F43757272656E74006765745F4974656D006F705F457175616C69747900457863657074696F6E004D6F76654E65787400536F72740053716C4D657461446174610053716C4462547970650053716C446174615265636F72640053716C436F6E746578740053716C50697065006765745F506970650053656E64526573756C7473537461727400536574537472696E67005365744461746554696D6500536574496E74333200446563696D616C006F705F496D706C69636974006F705F4D756C7469706C79006F705F4469766973696F6E004D617468004D6964706F696E74526F756E64696E6700526F756E6400536574446563696D616C0053656E64526573756C7473526F770053656E64526573756C7473456E6400002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000047530045004C0045004300540020005B00630073005500720069005300740065006D005D002000660072006F006D0020005B00230067006F0061006C00550052004C0073005D00008179530045004C0045004300540020005B00490050004200690067005D002C0020002800530045004C00450043005400200054004F0050002000310020005B0063006F0075006E007400720079005F0063006F00640065005D002000460052004F004D0020005B00640062006F005D002E005B00690070005F00670072006F00750070005F0063006F0075006E007400720079005D0020005700480045005200450020005B00690070005F00730074006100720074005D0020003C0020005B00490050004200690067005D0020004F00520044004500520020004200590020005B00690070005F00730074006100720074005D002000640065007300630029002000460052004F004D0020005B00640062006F005D002E005B00490049005300410075006400690074005D0020005700480045005200450020005B00560061006C006900640045007800740032005D0020003D00200031002000470052004F005500500020004200590020005B00490050004200690067005D00008125530045004C00450043005400200043004F004E00560045005200540028005B006400610074006500740069006D0065005D002C0020006400610074006500730074007200290020002B00200043004F004E00560045005200540028005B006400610074006500740069006D0065005D002C002000740069006D00650073007400720029002C0020005B00630073005500720069005300740065006D005D002C0020005B00490050004200690067005D002000460052004F004D0020005B00640062006F005D002E005B00490049005300410075006400690074005D0020005700480045005200450020005B00560061006C006900640045007800740032005D0020003D002000310020004F00520044004500520020004200590020005B00690064005D00000D790079004D004D00640064000007570054004600001743006F0075006E0074007200790043006F0064006500000F4400610079004F006E006C00790000155600690073006900740043006F0075006E0074000013500061006700650043006F0075006E00740000214D0061007800500061006700650073005000650072005600690073006900740000214100760067005000610067006500730050006500720056006900730069007400001B4D0061007800540069006D0065004F006E005300690074006500001B410076006700540069006D0065004F006E005300690074006500001347006F0061006C0043006F0075006E007400001542006F0075006E00630065005200610074006500000F7500720069005300740065006D00000F5000610067006500440061007900001345006E0074007200790052006100740065000011450078006900740052006100740065000000E2AF83D8F07A4645B3B3735C905DDD3E0008B77A5C561934E0890306110D02060E02060802060A08200401110D0E020A07200301110D0E02072003010E110D020420010102042001081C052001011208040001010803200001042001010E05200101113D0420010108050702110D0E03070102042000110D05200108110D042001080E070704120C080802080002114D110D110D0320000D05070208114D0607030802114D070704121008080205070212140804010000000515125D010E062002010E125504200012650420010E080520010113000320000206151271020A0E0420010A0807200201130013010615125D01120807151271020A120807151271020E120C0615125D01120C052001110D08052001021300082002021300101301052001110D0D07000202110D110D0420010E0E0500020E0E0E0615125D0112100615125D01121407151271020E121007151271020E121408200015117501130006151175011208042000130006200113011300050002020E0E082003010E1180810A072002010E118081092004010E1180810505062001011D127D05000012808D062001011280850615117501121005200201080E0620020108110D052002010808060001118091080B00021180911180911180910C0003118091118091081180990720020108118091082005010808080205061511750112140615117501120C80810725125515125D010E12611265151271020A0E15125D011208151271020A1208151271020E120C15125D01120C0A0E110D020212080E120C15125D01121015125D011214151271020E1210151271020E12140E0E1214121012808508081280851214128085021511750112081D127D15117501121015117501121415117501120C190100145068696C466163746F724368616C6C656E676534000005010000000017010012436F7079726967687420C2A920203230313000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000000009B2814B00000000020000007B000000A0440000A026000052534453308BD673F80BA9408151F398821A7E1D0E000000433A5C436F64655C41746C616E7469735C5468726F77617761795C5068696C466163746F724368616C6C656E6765345C5068696C466163746F724368616C6C656E6765345C6F626A5C44656275675C53716C436C6173734C6962726172792E70646200004445000000000000000000005E45000000200000000000000000000000000000000000000000000050450000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000280300000000000000000000280334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100449D780E00000100449D780E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00488020000010053007400720069006E006700460069006C00650049006E0066006F000000640200000100300030003000300030003400620030000000540015000100460069006C0065004400650073006300720069007000740069006F006E00000000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650034000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700300034002E00340030003200360030000000000048001400010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C006100730073004C006900620072006100720079002E0064006C006C0000004C0015000100500072006F0064007500630074004E0061006D006500000000005000680069006C0046006100630074006F0072004300680061006C006C0065006E006700650034000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700300034002E00340030003200360030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700300034002E0034003000320036003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000703500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
ALTER ASSEMBLY [SqlClassLibrary]
WITH VISIBILITY = ON
GO

CREATE PROCEDURE [dbo].[IISLogParse]
 (@SessionTimeout int )
AS EXTERNAL NAME [SqlClassLibrary].[StoredProcedures].[IISLogParse]
GO

create function [dbo].[fn_CHARINDEX](@ch char(1), @s varchar(8000))
returns int with schemabinding as
begin
DECLARE @i int
set @i = 1
while @i <= len(@s) begin
if substring(@s,@i,1) = @ch
return @i
set @i = @i + 1
end
return NULL
end

GO

CREATE FUNCTION [dbo].[getIpBig](@ip [varchar](24))
  RETURNS [bigint]
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @part1 [int], @part2 [int], @part3 [int], @part4 [int]
    DECLARE @ipBig [bigint]
    DECLARE @countryCode [varchar](2)

    SELECT @part1 = CONVERT([int], SUBSTRING(@ip, 1, dbo.fn_CHARINDEX('.', @ip) - 1))
    SET @ip = SUBSTRING(@ip, dbo.fn_CHARINDEX('.', @ip) + 1, 100)
    SELECT @part2 = CONVERT([int], SUBSTRING(@ip, 1, dbo.fn_CHARINDEX('.', @ip) - 1))
    SET @ip = SUBSTRING(@ip, dbo.fn_CHARINDEX('.', @ip) + 1, 100)
    SELECT @part3 = CONVERT([int], SUBSTRING(@ip, 1, dbo.fn_CHARINDEX('.', @ip) - 1))
    SET @ip = SUBSTRING(@ip, dbo.fn_CHARINDEX('.', @ip) + 1, 100)
    SELECT @part4 = CONVERT([int], @ip)

    SET @ipBig = @part1
    SET @ipBig = @ipBig * 256
    SET @ipBig = @ipBig + @part2
    SET @ipBig = @ipBig * 256
    SET @ipBig = @ipBig + @part3
    SET @ipBig = @ipBig * 256
    SET @ipBig = @ipBig + @part4

    --SELECT TOP 1 @countryCode = [country_code] FROM [dbo].[ip_group_country] WHERE [ip_start] < @ipBig ORDER BY [ip_start] DESC

    RETURN @ipBig
END
GO

ALTER TABLE [dbo].[IISAudit] ADD [IPBig] AS [dbo].[getIpBig]([cIp]) PERSISTED
GO
ALTER TABLE [dbo].[IISAudit] ADD [ValidExt2] AS case substring([csUriStem],charindex('.',[csUriStem]),5) when '.asp' then (1) when '.aspx' then (1) when '.htm' then (1) when '.html' then (1) when '.php' then (1) else (0) end PERSISTED
GO

CREATE INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit](
  [ValidExt2] ASC, [id] ASC
)
INCLUDE ([csUriStem], [IPBig], [DateStr], [TimeStr])
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = ON)
ON [PRIMARY];
GO

CREATE CLUSTERED INDEX [ix_ipgroupcountry_1]
ON [dbo].[ip_group_country](
  [ip_start] ASC
)
WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION = NONE, ONLINE = ON)
ON [PRIMARY];
GO

Main code:

CREATE TABLE #goalURLs (csUriStem [varchar](255))
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage29.aspx')
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage9.aspx')

EXEC [dbo].[IISLogParse] 900

DROP TABLE #goalURLs

Teardown:

DROP INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit]
GO

-- reorder the rows back to how they were
CREATE CLUSTERED INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit] (id)
GO

DROP INDEX [ix_iisaudit_1]
ON [dbo].[IISAudit]
GO

DROP INDEX [ix_ipgroupcountry_1] ON [dbo].[ip_group_country]
GO

ALTER TABLE [dbo].[IISAudit] DROP COLUMN [IPBig]
GO
ALTER TABLE [dbo].[IISAudit] DROP COLUMN [ValidExt2]
GO
DROP FUNCTION [dbo].[getIpBig]
GO
DROP FUNCTION [dbo].[fn_CHARINDEX]
GO

DROP PROCEDURE [dbo].[IISLogParse]
GO
DROP ASSEMBLY [SqlClassLibrary]
GO
ALTER TABLE [dbo].[IISAudit] DROP COLUMN [id]
GO

Ok, this is Peso 2a dated 2010-02-22, and it is quite simplified from earlier version. This simplified code runs in less than 2 seconds on my laptop.

This is the setup

CREATE UNIQUE NONCLUSTERED INDEX UX_IpGroupCountry ON dbo.ip_group_country
(
    ip_start ASC
)
INCLUDE (
        country_code
    )

This is the teardown

DROP INDEX UX_IpGroupCountry ON dbo.ip_group_country

And this is the code

SET NOCOUNT ON

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Goal
        (
            theURL VARCHAR(255) --COLLATE Finnish_Swedish_CI_AS,
            CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
            (
                theURL
            )
        )

INSERT  #Goal
    (
        theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

CREATE TABLE    #Stats
        (
            RowID INT IDENTITY(1, 1),
            CONSTRAINT PK_Stats PRIMARY KEY CLUSTERED
            (
                RowID
            ),      
            cIP VARCHAR(15) NOT NULL,
            DateStr CHAR(10) NOT NULL,
            PageTime DATETIME NOT NULL,
            isGoal TINYINT NOT NULL,
            theURL VARCHAR(255) NOT NULL,
            CountryCode CHAR(2) NOT NULL,
            theVisit INT,
            isBounce TINYINT,
            isEntry TINYINT,
            isExit TINYINT,
            [PageCount] INT,
            theTime INT
        )

INSERT      #Stats
        (
            cIP,
            DateStr,
            PageTime,
            isGoal,
            theURL,
            CountryCode
        )
SELECT      i.cIP,
        i.DateStr,
        CAST(i.DateStr + ' ' + i.TimeStr AS DATETIME) AS PageTime,
        CASE
            WHEN g.theURL IS NULL THEN 0
            ELSE 1
        END AS isGoal,
        i.csUriStem AS theURL,
        igc.CountryCode
FROM        dbo.IISAudit AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
CROSS APPLY (
            SELECT TOP(1)   w.Country_Code
            FROM        dbo.Ip_Group_Country AS w
            WHERE       w.ip_start <= CAST(16777216 AS BIGINT) * PARSENAME(i.cIP, 4) + 65536 * PARSENAME(i.cIP, 3) + 256 * PARSENAME(i.cIP, 2) + PARSENAME(i.cIP, 1)
            ORDER BY    w.ip_start DESC
        ) AS igc(CountryCode)
WHERE       PARSENAME(i.csUriStem, 1) IN ('asp', 'aspx', 'htm', 'html', 'php')
ORDER BY    i.cIP,
        i.Seq_No

DROP TABLE  #Goal

DECLARE @IP VARCHAR(15),
    @PageTime DATETIME,
    @Visit INT = 0

UPDATE  #Stats
SET @Visit = theVisit = CASE
                    WHEN cIP <> @IP THEN @Visit + 1
                    WHEN DATEDIFF(SECOND, @PageTime, PageTime) < @SessionTimeout THEN @Visit
                    ELSE @Visit + 1
                END,
    @IP = cIP,
    @PageTime = PageTime

UPDATE      s
SET     s.isBounce =    CASE 
                    WHEN q.minID = s.RowID THEN q.isBounce
                END,
        s.isEntry = CASE
                    WHEN q.minID = s.RowID THEN 1
                END,
        s.isExit =  CASE
                    WHEN q.maxID = s.RowID THEN 1
                END,
        s.[PageCount] = CASE
                    WHEN q.minID = s.RowID THEN q.[PageCount]
                END,
        s.theTime = CASE
                    WHEN q.minID = s.RowID THEN q.theTime
                END
FROM        #Stats AS s
INNER JOIN  (
            SELECT      w.theVisit,
                    MIN(w.minID) AS minID,
                    MAX(w.maxID) AS maxID,
                    CASE MAX(CASE WHEN s.RowID = w.minID THEN s.theURL ELSE NULL END)
                        WHEN MAX(CASE WHEN s.RowID = w.maxID THEN s.theURL ELSE NULL END) THEN 1
                        ELSE 0
                    END AS isBounce,
                    COUNT(*) AS [PageCount],
                    MAX(w.theTime) AS theTime
            FROM        (
                        SELECT      theVisit,
                                MIN(RowID) AS minID,
                                MAX(RowID) AS maxID,
                                DATEDIFF(SECOND, MIN(PageTime), MAX(PageTime)) AS theTime
                        FROM        #Stats
                        GROUP BY    theVisit
                    ) AS w
            INNER JOIN  #Stats AS s ON s.theVisit = w.theVisit
            GROUP BY    w.theVisit
        ) AS q ON q.theVisit = s.theVisit

-- 1
SELECT      CountryCode,
        CAST(DateStr AS DATETIME) AS DayOnly,
        COUNT(DISTINCT theVisit) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(theTime) AS MaxTimeOnSite,
        SUM(theTime) / COUNT(DISTINCT theVisit) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    CountryCode,
        DateStr
ORDER BY    DateStr,
        CountryCode

-- 2
SELECT      CountryCode,
        COUNT(DISTINCT theVisit) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(theTime) AS MaxTimeOnSite,
        SUM(theTime) / COUNT(DISTINCT theVisit) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT theVisit) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    CountryCode
ORDER BY    CountryCode

-- 3
SELECT      theURL AS uriStem,
        CAST(DateStr AS DATETIME) AS PageDay,
        COUNT(*) AS [PageCount],
        CAST(100.0E * COALESCE(SUM(isEntry), 0) / COUNT(*) AS DECIMAL(12, 2)) AS EntryRate,
        CAST(100.0E * COALESCE(SUM(isExit), 0) / COUNT(*) AS DECIMAL(12, 2)) AS ExitRate,
        CAST(100.0E * COALESCE(SUM(isBounce), 0) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Stats
GROUP BY    theURL,
        DateStr
ORDER BY    DateStr,
        theURL

DROP TABLE  #Stats

Version 1a Performance seem ok , doesnt quite match with reggies output data.

The setup

CREATE TABLE #goalURLs (csUriStem [varchar](255))
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage29.aspx')
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage9.aspx')
go
Create unique index idxCtryIp on ip_group_country (Ip_start) include (Country_name)
go
Create index idxIpVisitTime on IISAudit (Cip,DateStr,TimeStr)
go
Drop Function CountForUnique
go
Create Function CountForUnique(@Ip char(50),
                               @Date as char(10),
                               @Time as char(8),
                               @SinceXHours as integer)

returns table
as
return(
with cteDateTime
as
(
select dateadd(hh,0-@SinceXHours,convert(datetime,@Date +' '+@Time+'.000' )) as SinceDTime
),
cteSplit
as
(
Select CONVERT(char(10), SinceDTime,120) as DateFrom,
       CONVERT(char(10), SinceDTime,108) as TimeFrom
 from  cteDateTime
),
ctePreRet
as
(
select COUNT(*) as Count
 from  cteSplit ,IISAudit IIS
where  (IIS.DateStr > DateFrom or (IIS.DateStr=DateFrom and IIS.TimeStr >= TimeFrom))
 and   (IIS.DateStr < @Date    or (IIS.DateStr=@Date    and IIS.TimeStr <  @Time))
 and   IIS.cIp = @Ip
)
Select Case when Ctepreret.Count =0 then 1 else 0 end as CntUnique

from ctePreRet
)

go


Drop function SplitIp
go

create function SplitIp(@IpAddress char(20))
returns table
as
return(
with cteNum
as
(
Select 1 as n union all select 2 union all
Select 3 union all select 4 union all
Select 5 as n union all select 6 union all
Select 7 union all select 8 union all
Select 9 as n union all select 10 union all

Select 11 as n union all select 12 union all
Select 13 union all select 14 union all
Select 15 as n union all select 16 union all
Select 17 union all select 18 union all
Select 19 as n union all select 20 
),
ctePosn
as
(
select 1 as posn union 
Select len(@Ipaddress)+1 union
Select cteNum.n
  from cteNum
  where SUBSTRING(@Ipaddress,cteNum.n,1)='.'
),
cteCutPosn
as
(
Select min([1]) as Pos1,min([2]) as Pos2,min([3]) as Pos3,min([4]) as Pos4,min([5]) as Pos5
 from  (
    Select Posn,Row_Number() over(order by posn) as Rown from ctePosn ) as inp
    pivot(
        min(posn)
        for Rown in ([1],[2],[3],[4],[5])
    ) as pvt
)
Select cast(SUBSTRING(@Ipaddress,Pos1,Pos2-Pos1)     as bigint) * 16777216 +
       cast(SUBSTRING(@Ipaddress,Pos2+1,Pos3-1-Pos2) as integer) *  65536 + 
       cast(SUBSTRING(@Ipaddress,Pos3+1,Pos4-1-Pos3) as integer) * 256 +
       cast(SUBSTRING(@Ipaddress,Pos4+1,Pos5-1-Pos4) as integer) as IpNum
  from cteCutPosn
)
go

drop Function isValidExt
go
create Function isValidExt(@UriStem varchar(255))
returns table
as
return
(

with ctePos

as
(
   Select right(@UriStem,5) as rght
)
,
cteExt
as
(
Select case when left(rght,1) = '.' then
          substring(rght,2,5) else
          rght end as ext
 from  ctePos
)
Select 1 as ValidExt
  from cteExt
  join (Select 'asp' as tExt union all
        Select 'aspx' union all
        Select 'htm' union all
        select 'html' union all
        select 'php') as tExt on cteExt.ext = tExt.tExt
)

go

And the execution code

Drop TAble #VisitLog
go
Drop Table #VisitSummary
go
Create TAble #VisitLog
(
IpAddress char(20),
DateStr  char(10),
TimeStr  Char(10),
DateTime dateTime,
IpVisit   integer,
GoalCount integer,
Uri       varchar(255),
LogId     integer 
)
go
Create unique clustered index visitlogidx on #visitlog(IpAddress,DateTime,LogId)
go
insert into #VisitLog(IpAddress,DateStr,TimeStr,DateTime,ipVisit,GoalCount,Uri,LogId)
select IIS.cIp,DateStr,Timestr,
       convert(datetime,DateStr +' '+TimeStr+'.000' ) as SinceDTime,0,
       sum(case when #goalURLs.csUriStem is not null then  1 else 0 end),
       IIS.csUriStem,
       ROW_NUMBER() over (partition by IIS.cIp order by DateStr,Timestr)
from IISAudit IIS
cross apply dbo.isValidExt(csUriStem)
left join #goalURLs
    on #goalURLs.csUriStem = IIS.csUriStem

group by cIp,DateStr,Timestr,convert(datetime,DateStr +' '+TimeStr+'.000' ) ,IIS.csUriStem
go
Declare @SessionTimeOut integer
Declare @VisitCount integer
Declare @SessionTimesOutWhen datetime
Declare @IpOn                varchar(20)
Declare @VisitView           integer
Select @IpOn = '0.0'
select @VisitCount =0
Declare @LVisit integer
Select @SessionTimeOut = 900

update #visitlog set ipvisit =0
update #VisitLog
   set @VisitCount = case when @IpOn <> #VisitLog.IpAddress then 0 
                          when #VisitLog.DateTime  > @SessionTimesOutWhen then @VisitCount+1
                          else @VisitCount  end,
       @SessionTimesOutWhen = case when @VisitCount =0  or #VisitLog.DateTime  > @SessionTimesOutWhen
                              then DATEADD(s,@SessionTimeOut,#VisitLog.DateTime) else @SessionTimesOutWhen end,
       @VisitCount = case when @VisitCount =0 then 1 else @VisitCount end,

       IpVisit = @VisitCount,
       @IpOn=  #VisitLog.IpAddress
 from  #VisitLog
 OPTION (MAXDOP 1)
go

Create index uri on #visitlog(uri,DateTime)
go
Create Table #VisitSummary
(
IpAddress char(20) not null,
SessionStart datetime not null,
SessionEnd Datetime not null,
VisitCount integer not null,
Pages      integer not null,
GoalCount  integer not null,
SessionEntryPage varchar(255) not null,
SessionExitPage varchar(255) not null
)
go
Create unique Clustered index idxVisitSummary on #VisitSummary(IpAddress,SessionStart)
go

with ctePageSum
as
(
 select ipAddress,DateTime,IpVisit,GoalCount,
        Uri,
        LogId,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId) as StartRowN,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId desc) as EndRowN
  from #VisitLog
)
insert into #VisitSummary(IpAddress,SessionStart,SessionEnd,VisitCount,Pages,GoalCount,SessionEntryPage,SessionExitPage)
Select ipAddress,
       min(case when StartRowN =1 then DateTime end),
       min(case when EndRowN =1 then DateTime end),
       IpVisit,
       count(*),
       SUM(GoalCount),
       min(case when StartRowN =1 then Uri end),
       min(case when EndRowN =1 then Uri end)
  from ctePageSum        
 group by ipAddress,IpVisit
go
with cteResults
as
(
select Ctry.country_code , 
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PagesCount,
       max(#VisitSummary.Pages) as MaxPagesPerCount,
       Avg(#VisitSummary.Pages) as AvgPagesPerCount,
       max(datediff(minute,SessionStart,SessionEnd)) as MaxTimeOnSite,
       avg(datediff(minute,SessionStart,SessionEnd)) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
 cross apply dbo.SplitIp(#VisitSummary.IpAddress) nIp
 cross apply ( Select top(1) country_code
                 from ip_group_country 
                where ip_start  < = nIp.IpNum
                order by ip_start desc)  as Ctry

group by Ctry.country_code
)
Select Country_Code,visitcount,pagescount,maxPagesPercount,AvgPagesPerCount,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5))) end as BounceRate
 from  cteResults
order by 1
go
with cteResults
as
(
select Ctry.country_code , 
       cast (DATEDIFF(DAY,0,SessionStart) AS datetime) as Day,
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PagesCount,
       max(#VisitSummary.Pages) as MaxPagesPerCount,
       Avg(#VisitSummary.Pages) as AvgPagesPerCount,
       max(datediff(minute,SessionStart,SessionEnd)) as MaxTimeOnSite,
       avg(datediff(minute,SessionStart,SessionEnd)) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
 cross apply dbo.SplitIp(#VisitSummary.IpAddress) nIp
 cross apply ( Select top(1) country_code
                 from ip_group_country 
                where ip_start  < = nIp.IpNum
                order by ip_start desc)  as Ctry

group by Ctry.country_code,DATEDIFF(DAY,0,SessionStart)
)
Select Country_Code,day,visitcount,pagescount,maxPagesPercount,AvgPagesPerCount,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5))) end as BounceRate
 from  cteResults
order by 1,2
go

with cteEntry
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionEntryPage,
       count(*) as EntryCount,
       SUM(case when SessionExitPage = SessionEntryPage then 1 else 0 end) as BounceCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionEntryPage
),
cteExit
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionExitPage,
       count(*) as ExitCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionExitPage
),
cteVisit
as
( 
select DATEDIFF(DAY,0,DateTime) as VisitDate,
       uri,
       count(*) as VisitCount
  from #VisitLog
 group by DATEDIFF(DAY,0,DateTime),Uri
),
ctePageSumm
as
(
Select VisitDate,
       uri,
       cast(isNull(EntryCount,0) as decimal(10,5)) as EntryCount,
       cast(isNull(ExitCount,0)  as decimal(10,5)) as Exitcount,
       cast(isNull(VisitCount,0) as decimal(10,5)) as VisitCount,
       cast(ISNULL(BounceCount,0) as decimal(10,5)) as BounceCount
  from cteVisit 
  left join cteEntry
    on cteEntry.SessDate  = cteVisit.VisitDate
   and cteEntry.SessionEntryPage = cteVisit.uri
  left join cteExit
    on cteExit.SessDate  = cteVisit.VisitDate
   and cteExit.SessionExitPage = cteVisit.uri
)
Select CAST(VisitDate AS datetime),
       uri,
       VisitCount AS PageCount,
       EntryCount,
       case when EntryCount =0 then 0 else 100.0 / (VisitCount / EntryCount  ) end  as EntryRate,
       case when ExitCount =0 then 0 else 100.0 / (VisitCount / ExitCount  ) end    as ExitRate,
       case when BounceCount =0 then 0 else 100.0 / (VisitCount / BounceCount  ) end as BounceRate
 from ctePageSumm
order by 1,2

Dave Ver 1.b - I think the results are now spot on ,bold claim i know

The Setup

Create unique clustered index idxCtryIp on ip_group_country (Ip_start) 
go
Create index idxIpVisitTime on IISAudit (Cip,DateStr,TimeStr)
go
create function SplitIp(@IpAddress char(20))
returns table
as
return(
with cteposn1
as(
select CHARINDEX('.',@ipaddress) as pos1
),
cteposn2
as
(
select pos1,CHARINDEX('.',@ipaddress,pos1+1) as pos2
 from cteposn1
),
cteposn3
as
(
select pos1,pos2,CHARINDEX('.',@ipaddress,pos2+1) as pos3
 from cteposn2
),
cteposn4
as
(
select pos1,pos2,pos3
  from cteposn3
)

select cast(LEFT(@ipaddress,pos1-1) as bigint) * 16777216 +
       cast(SUBSTRING(@ipaddress,pos1+1,(pos2-pos1)-1) as integer) *  65536 +
       cast(SUBSTRING(@ipaddress,pos2+1,(pos3-pos2)-1) as integer) * 256 +
       cast(SUBSTRING(@ipaddress,pos3+1,(LEN(@ipaddress)-pos3))  as integer) as IpNum   
  from cteposn4
)

go
create Function isValidExt(@UriStem varchar(255))
returns table
as
return
(

with ctePos

as
(
   Select right(@UriStem,4) as rght
)
,
cteExt
as
(
Select case when left(rght,1) = '.' then
          substring(rght,2,4) else
          rght end as ext
 from ctePos
)
Select 1 as ValidExt
  from cteExt
  join (Select 'asp' as tExt union all
        Select 'aspx' union all
        Select 'htm' union all
        select 'html' union all
        select 'php') as tExt on cteExt.ext = tExt.tExt
)

go

The Teardown

drop index ip_group_country.idxCtryIp
drop index idxIpVisitTime.IISAudit
Drop function SplitIp
drop Function isValidExt

And the execution code

set nocount on 

CREATE TABLE #goalURLs (csUriStem [varchar](255))
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage29.aspx')
INSERT INTO [#goalURLs] ([csUriStem])
VALUES ('ASPNETPage9.aspx')




Create TAble #VisitLog
(
IpAddress char(20),
DateStr  char(10),
TimeStr  Char(10),
DateTime dateTime,
IpVisit   integer,
GoalCount integer,
Uri       varchar(255),
LogId     integer 
)
Create unique clustered index visitlogidx on #visitlog(IpAddress,DateTime,LogId)
insert into #VisitLog(IpAddress,DateStr,TimeStr,DateTime,ipVisit,GoalCount,Uri,LogId)
select IIS.cIp,DateStr,Timestr,
       convert(datetime,DateStr +' '+TimeStr+'.000' ) as SinceDTime,0,
       case when #goalURLs.csUriStem is not null then  1 else 0 end,
       IIS.csUriStem,
       ROW_NUMBER() over (partition by IIS.cIp order by DateStr,Timestr)
from IISAudit IIS
cross apply dbo.isValidExt(csUriStem)
left join #goalURLs
    on #goalURLs.csUriStem = IIS.csUriStem

Declare @SessionTimeOut integer
Declare @VisitCount integer
Declare @SessionTimesOutWhen datetime
Declare @IpOn                varchar(20)
Declare @VisitView           integer
Select @IpOn = '0.0'
select @VisitCount =0
Declare @LVisit integer
Select @SessionTimeOut = 900


update #VisitLog
   set @VisitCount = case when @IpOn <> #VisitLog.IpAddress then 0 
                          when #VisitLog.DateTime  > @SessionTimesOutWhen then @VisitCount+1
                          else @VisitCount  end,
       @SessionTimesOutWhen =  DATEADD(s,@SessionTimeOut,#VisitLog.DateTime),
       @VisitCount = case when @VisitCount =0 then 1 else @VisitCount end,

       IpVisit = @VisitCount,
       @IpOn=  #VisitLog.IpAddress
 from  #VisitLog
 OPTION (MAXDOP 1)

Create index uri on #visitlog(uri,DateTime)

Create Table #VisitSummary
(
IpAddress char(20) not null,
CountryCode char(2) not null,
SessionStart datetime not null,
SessionEnd Datetime not null,
VisitCount integer not null,
Pages      decimal(6,2) not null,
GoalCount  integer not null,
SessionEntryPage varchar(255) not null,
SessionExitPage varchar(255) not null
)
Create unique Clustered index idxVisitSummary on #VisitSummary(IpAddress,SessionStart)

;with ctePageSum
as
(
 select ipAddress,DateTime,IpVisit,GoalCount,
        Uri,
        LogId,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId) as StartRowN,
        ROW_NUMBER() over (partition by ipAddress,IpVisit order by LogId desc) as EndRowN
  from #VisitLog
)
insert into #VisitSummary(IpAddress,SessionStart,SessionEnd,VisitCount,Pages,GoalCount,SessionEntryPage,SessionExitPage,CountryCode)
Select ipAddress,
       min(case when StartRowN =1 then DateTime end),
       min(case when EndRowN =1 then DateTime end),
       IpVisit,
       count(*),
       SUM(GoalCount),
       min(case when StartRowN =1 then Uri end),
       min(case when EndRowN =1 then Uri end),
       ctry.country_code

  from ctePageSum   
  cross apply dbo.SplitIp(ctePageSum.IpAddress) nIp
  cross apply ( Select top(1) country_code
                 from ip_group_country 
                where ip_start  < = nIp.IpNum
                order by ip_start desc)  as Ctry

 group by ipAddress,IpVisit, nIp.IpNum,Ctry.country_code;



;with cteResults
as
(
select countrycode , 
       cast (DATEDIFF(DAY,0,SessionStart) AS datetime) as DayOnly,
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PageCount,
       max(#VisitSummary.Pages) as MaxPagesPerVisit,
       round(Avg(#VisitSummary.Pages),2) as AvgPagesPerVisit,
       max(datediff(SECOND,SessionStart,SessionEnd)) as MaxTimeOnSite,
       round(avg(datediff(SECOND,SessionStart,SessionEnd)),2) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
group by countrycode,DATEDIFF(DAY,0,SessionStart)
)
Select CountryCode,dayOnly,visitcount,pagecount,maxPagesPerVisit,AvgPagesPerVisit,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       round(case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5))) end ,2) as BounceRate
 from  cteResults
order by 2,1

;with cteResults
as
(
select countrycode , 
       count(*)   as VisitCount,
       sum(#VisitSummary.Pages) as PageCount,
       max(#VisitSummary.Pages) as MaxPagesPerVisit,
       round(Avg(#VisitSummary.Pages),2) as AvgPagesPerCount,
       max(datediff(second,SessionStart,SessionEnd)) as MaxTimeOnSite,
       avg(datediff(second,SessionStart,SessionEnd)) as AvgTimeOnSite,
       SUM(#VisitSummary.GoalCount) as GoalCount,
       sum(case when #VisitSummary.Pages = 1 then 1 else 0 end) BounceCount
  from #VisitSummary
group by countrycode
)
Select CountryCode,visitcount,pagecount,maxPagesPerVisit,AvgPagesPerCount,
       MaxTimeOnSite,AvgTimeOnSite,GoalCount,
       round(case when BounceCount =0 then 0 else 100.0/(VisitCount/cast(BounceCount as decimal(15,5)))end ,2) as BounceRate

 from  cteResults
order by 1


;with cteEntry
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionEntryPage,
       count(*) as EntryCount,
       SUM(case when SessionExitPage = SessionEntryPage then 1 else 0 end) as BounceCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionEntryPage
),
cteExit
as
(
select DATEDIFF(DAY,0,SessionStart) as SessDate,
       SessionExitPage,
       count(*) as ExitCount
  from #VisitSummary
 group by DATEDIFF(DAY,0,SessionStart),
       SessionExitPage
),
cteVisit
as
( 
select DATEDIFF(DAY,0,DateTime) as VisitDate,
       uri,
       count(*) as VisitCount
  from #VisitLog
 group by DATEDIFF(DAY,0,DateTime),Uri
),
ctePageSumm
as
(
Select VisitDate,
       uri,
       cast(isNull(EntryCount,0) as decimal(10,5)) as EntryCount,
       cast(isNull(ExitCount,0)  as decimal(10,5)) as Exitcount,
       cast(isNull(VisitCount,0) as decimal(10,5)) as VisitCount,
       cast(ISNULL(BounceCount,0) as decimal(10,5)) as BounceCount
  from cteVisit 
  left join cteEntry
    on cteEntry.SessDate  = cteVisit.VisitDate
   and cteEntry.SessionEntryPage = cteVisit.uri
  left join cteExit
    on cteExit.SessDate  = cteVisit.VisitDate
   and cteExit.SessionExitPage = cteVisit.uri
)
Select uri   as UriStem,
       CAST(VisitDate AS datetime) as PageDay,
       VisitCount AS PageCount,
       EntryCount,
       round(case when EntryCount =0 then 0 else 100.0 / (VisitCount / EntryCount  ) end,2)  as EntryRate,
       round(case when ExitCount =0 then 0 else 100.0 / (VisitCount / ExitCount  ) end ,2)   as ExitRate,
       round(case when BounceCount =0 then 0 else 100.0 / (VisitCount / BounceCount  ) end,2) as BounceRate
 from ctePageSumm
order by 1,2

Drop TAble #goalURLs
Drop TAble #VisitLog
Drop Table #VisitSummary

Peso 3a, almost there. In this solution there are no "quirkyness" at all. Resultset 1 and 2 are perfect match, but resultset #3 still need some work.

SET NOCOUNT ON

CREATE TABLE    #Goal
        (
            theURL VARCHAR(255) COLLATE FINNISH_SWEDISH_CI_AS,
            CONSTRAINT PK_Goal PRIMARY KEY CLUSTERED
            (
                theURL
            )
        )

INSERT  #Goal
    (
        theURL
    )
VALUES  ('ASPNETPage29.aspx'),
    ('ASPNETPage9.aspx')

DECLARE @SessionTimeout INT = 900

CREATE TABLE    #Source
        (
            cIP VARCHAR(15) NOT NULL,
            PageTime DATETIME NOT NULL,
            recID INT NOT NULL,
            CONSTRAINT PK_Source PRIMARY KEY CLUSTERED
            (
                cIP,
                PageTime,
                recID
            ),
            csUriStem VARCHAR(255) NOT NULL,
            IsGoal INT NOT NULL,
            RowID INT
        )

INSERT      #Source
        (
            cIP,
            PageTime,
            recID,
            csUriStem,
            IsGoal
        )
SELECT      i.cIP,
        i.DateStr + ' ' + i.TimeStr AS PageTime,
        ROW_NUMBER() OVER (PARTITION BY i.cIP ORDER BY i.DateStr, i.TimeStr) AS recID,
        i.csUriStem,
        CASE
            WHEN g.theURL IS NULL THEN 0
            ELSE 1
        END AS IsGoal
FROM        dbo.IISAudit AS i
LEFT JOIN   #Goal AS g ON g.theURL = i.csUriStem
WHERE       PARSENAME(i.csUriStem, 1) IN ('asp', 'aspx', 'htm', 'html', 'php')
        --AND cIP = '82.75.9.215'

DROP TABLE  #Goal

CREATE TABLE    #Lower
        (
            cIP VARCHAR(15) NOT NULL,
            StartTime DATETIME NOT NULL,
            recID INT NOT NULL,
            CONSTRAINT PK_Lower PRIMARY KEY CLUSTERED
            (
                cIP,
                StartTime,
                recID
            ),
            csUriStem VARCHAR(255) NOT NULL
        )

INSERT      #Lower
        (
            cIP,
            StartTime,
            recID,
            csUriStem
        )
SELECT      s.cIP,
        s.PageTime AS StartTime,
        ROW_NUMBER() OVER (PARTITION BY s.cIP ORDER BY s.PageTime) AS recID,
        s.csUriStem
FROM        #Source AS s
LEFT HASH JOIN  #Source AS t ON t.cIP = s.cIP
            AND t.recID = s.recID - 1
WHERE       DATEADD(SECOND, @SessionTimeout, t.PageTime) < s.PageTime
        OR t.cIP IS NULL

CREATE TABLE    #Upper
        (
            cIP VARCHAR(15) NOT NULL,
            EndTime DATETIME NOT NULL,
            recID INT NOT NULL,
            CONSTRAINT PK_Upper PRIMARY KEY CLUSTERED
            (
                cIP,
                EndTime,
                recID
            ),
            csUriStem VARCHAR(255) NOT NULL
        )

INSERT      #Upper
        (
            cIP,
            EndTime,
            recID,
            csUriStem
        )
SELECT      s.cIP,
        DATEADD(SECOND, @SessionTimeout, s.PageTime) AS EndTime,
        ROW_NUMBER() OVER (PARTITION BY s.cIP ORDER BY s.PageTime) AS recID,
        s.csUriStem
FROM        #Source AS s
LEFT HASH JOIN  #Source AS t ON t.cIP = s.cIP
            AND t.recID = s.recID + 1
WHERE       DATEADD(SECOND, @SessionTimeout, s.PageTime) < t.PageTime
        OR t.cIP IS NULL

CREATE TABLE    #Result
        (
            RowID INT IDENTITY(1, 1) NOT NULL,
            cIP VARCHAR(15) NOT NULL,
            StartTime DATETIME NOT NULL,
            EndTime DATETIME NOT NULL,
            CONSTRAINT PK_Result PRIMARY KEY CLUSTERED
            (
                cIP,
                StartTime,
                EndTime
            ),
            DayOnly DATETIME NOT NULL,
            TimeOnSite INT NOT NULL,
            firstURL VARCHAR(255) NOT NULL,
            lastURL VARCHAR(255) NOT NULL,
            IsGoal INT,
            [PageCount] INT,
            CountryCode CHAR(2)
        )

INSERT      #Result
        (
            cIP,
            StartTime,
            EndTime,
            DayOnly,
            TimeOnSite,
            firstURL,
            lastURL
        )
SELECT      l.cIP,
        l.StartTime,
        u.EndTime,
        DATEDIFF(DAY, 0, l.StartTime) AS DayOnly,
        DATEDIFF(SECOND, l.StartTime, u.EndTime) - 900 AS TimeOnSite,
        l.csUriStem AS firstURL,
        u.csUriStem AS lastURL
FROM        #Lower AS l
INNER HASH JOIN #Upper AS u ON u.cIP = l.cIP
WHERE       l.recID = u.recID
ORDER BY    l.cIP,
        l.recID

DROP TABLE  #Lower,
        #Upper

UPDATE      s
SET     s.RowID = r.RowID
FROM        #Result AS r
INNER JOIN  #Source AS s ON s.PageTime BETWEEN r.StartTime AND r.EndTime
WHERE       s.cIP = r.cIP

UPDATE      r
SET     r.IsGoal = w.IsGoal,
        r.[PageCount] = w.[PageCount]
FROM        #Result AS r
INNER JOIN  (
            SELECT      RowID,
                    SUM(IsGoal) AS IsGoal,
                    COUNT(*) AS [PageCount]
            FROM        #Source
            GROUP BY    RowID
        ) AS w ON w.RowID = r.RowID

CREATE TABLE    #Lookup
        (
            cIP VARCHAR(15) NOT NULL,
            IP BIGINT,
            CountryCode VARCHAR(2)
        )

INSERT      #Lookup
        (
            cIP
        )
SELECT      cIP
FROM        #Result
GROUP BY    cIP

UPDATE  #Lookup
SET IP = CAST(16777216 AS BIGINT) * PARSENAME(cIP, 4) + 65536 * PARSENAME(cIP, 3) + 256 * PARSENAME(cIP, 2) + PARSENAME(cIP, 1)

UPDATE  l
SET l.CountryCode = (SELECT TOP(1) x.Country_Code FROM ip_group_country AS x WHERE x.ip_start <= l.IP ORDER BY x.ip_start DESC)
FROM    #Lookup AS l

UPDATE      r
SET     r.CountryCode = l.CountryCode
FROM        #Result AS r
INNER JOIN  #Lookup AS l ON l.cIP = r.cIP

DROP TABLE  #Lookup

-- 1
SELECT      CountryCode,
        DayOnly,
        COUNT(*) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(*) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(TimeOnSite) AS MaxTimeOnSite,
        SUM(TimeOnSite) / COUNT(*) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Result
GROUP BY    CountryCode,
        DayOnly
ORDER BY    DayOnly,
        CountryCode

-- 2
SELECT      CountryCode,
        COUNT(*) AS VisitCount,
        SUM([PageCount]) AS [PageCount],
        MAX([PageCount]) AS MaxPagesPerVisit,
        CAST(1.0E * SUM([PageCount]) / COUNT(*) AS DECIMAL(12, 2)) AS AvgPagesPerVisit,
        MAX(TimeOnSite) AS MaxTimeOnSite,
        SUM(TimeOnSite) / COUNT(*) AS AvgTimeOnSite,
        SUM(isGoal) AS GoalCount,
        CAST(100.0E * SUM(CASE WHEN [PageCount] = 1 THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(12, 2)) AS BounceRate
FROM        #Result
GROUP BY    CountryCode
ORDER BY    CountryCode

-- 3
SELECT      uriStem,
        PageDay,
        SUM([PageCount]) AS [PageCount],
        CAST(100.0E * SUM(IsEntry) / SUM([PageCount]) AS DECIMAL(12, 2)) AS EntryRate,
        CAST(100.0E * SUM(IsExit) / SUM([PageCount]) AS DECIMAL(12, 2)) AS ExitRate,
        CAST(100.0E * SUM(IsBounce) / SUM([PageCount]) AS DECIMAL(12, 2)) AS BounceRate
FROM        (
            SELECT  firstURL AS uriStem,
                DayOnly AS PageDay,
                1 AS IsEntry,
                0 AS IsExit,
                CASE firstURL
                    WHEN lastURL THEN 1
                    ELSE 0
                END AS IsBounce,
                0 AS [PageCount]
            FROM    #Result

            UNION ALL

            SELECT  lastURL AS uriStem,
                DayOnly AS PageDay,
                0 AS IsEntry,
                1 AS IsExit,
                0 AS IsBounce,
                0 AS [PageCount]
            FROM    #Result

            UNION ALL

            SELECT  csUriStem AS uriStem,
                DATEDIFF(DAY, 0, PageTime) AS PageDay,
                0 AS IsEntry,
                0 AS IsExit,
                0 AS IsBounce,
                1 AS [PageCount]
            FROM    #Source
        ) AS d
GROUP BY    PageDay,
        uriStem
ORDER BY    PageDay,
        uriStem

DROP TABLE  #Result,
        #Source