WITH cteUniquePages
(
CorrelationID,
Title,
URL,
HitDate,
TotalVisitsOnDate,
DontUseThisDate)
AS
(
    SELECT DISTINCT
    CorrelationID,
    Title,
    URL,
    HitDate,
    COUNT(HitDate) OVER (PARTITION BY URL, HitDate) 'TotalVisitsOnDate',
    CAST(LogTime AS date) 'DontUseThisDate'
    FROM
        (SELECT
        CorrelationId,
        UserLogin,
        LogTime,
        Title,
        CONCAT(
            (CASE
                WHEN WebUrl <> '' THEN CONCAT(ServerUrl,'/') ELSE ServerUrl END),
            WebUrl,DocumentPath) 'URL',
        CONCAT(
            CASE
                WHEN (LEN(DATEPART(day,LogTime)))=1 THEN CONCAT('0',DATEPART(day,LogTime)) END,
            CASE
                WHEN (LEN(DATEPART(day,LogTime)))=2 THEN (DATEPART(day,LogTime)) END,'-',
            CASE
                WHEN (LEN(DATEPART(month,LogTime)))=1 THEN CONCAT('0',DATEPART(month,LogTime)) END,
            CASE
                WHEN (LEN(DATEPART(month,LogTime)))=2 THEN (DATEPART(month,LogTime)) END,
            '-', DATEPART(year,LogTime)) 'HitDate'
        FROM WSS_Logging.dbo.RequestUsage
        WHERE UserLogin <> 'nt authorityiusr'
        AND UserLogin <> 'i:0#.w|pfnetzz_sharepoint13'
        AND DocumentPath LIKE '%.aspx'
        AND DocumentPath NOT LIKE '%/_layouts/%'
        AND UserLogin <> 'PFNETE01BrownS'
        AND UserLogin <> 'i:0#.w|pfnetsharepointtestacc1'
        GROUP BY UserLogin, WebUrl, DocumentPath, LogTime, Title, ServerUrl,CorrelationId) as a
),
    cteVisitsAllTime
    (
        CorrelationID,
        LogTime,
        Title,
        URL,
        TotalVisits
    )
    AS
    (
        SELECT DISTINCT
        CorrelationID,
        LogTime,
        Title,
        URL,
        COUNT(URL) OVER (PARTITION BY URL) 'TotalVisits'
        FROM(
            SELECT
            CorrelationId,
            Title,
            CONCAT(
                (CASE
                    WHEN WebUrl <> '' THEN CONCAT(ServerUrl,'/') ELSE ServerUrl END),
                WebUrl,DocumentPath) 'URL',
                LogTime
            FROM WSS_Logging.dbo.RequestUsage
            WHERE UserLogin <> 'nt authorityiusr'
            AND UserLogin <> 'i:0#.w|pfnetzz_sharepoint13'
            AND DocumentPath LIKE '%.aspx'
            AND DocumentPath NOT LIKE '%/_layouts/%'
            AND UserLogin <> 'PFNETE01BrownS'
            AND UserLogin <> 'i:0#.w|pfnetsharepointtestacc1') as a
    ),
    cteVisitsLast7Days
    (
        CorrelationID,
        Title,
        URL,
        TotalVisits
    )
    AS
    (
        SELECT
        CorrelationID,
        Title,
        URL,
        COUNT(URL) OVER (PARTITION BY URL) 'TotalVisits'
        FROM(
            SELECT
            CorrelationId,
            Title,
            CONCAT(
                (CASE
                    WHEN WebUrl <> '' THEN CONCAT(ServerUrl,'/') ELSE ServerUrl END),
                WebUrl,DocumentPath) 'URL',
                LogTime
            FROM WSS_Logging.dbo.RequestUsage
            WHERE UserLogin <> 'nt authorityiusr'
            AND UserLogin <> 'i:0#.w|pfnetzz_sharepoint13'
            AND DocumentPath LIKE '%.aspx'
            AND DocumentPath NOT LIKE '%/_layouts/%'
            AND UserLogin <> 'PFNETE01BrownS'
            AND UserLogin <> 'i:0#.w|pfnetsharepointtestacc1') as a
            WHERE LogTime >= DATEADD(day,-7, GETDATE())
    ),
    cteVisitsLast30Days
    (
        CorrelationID,
        Title,
        URL,
        TotalVisits
    )
    AS
    (
        SELECT
        CorrelationID,
        Title,
        URL,
        COUNT(URL) OVER (PARTITION BY URL) 'TotalVisits'
        FROM(
            SELECT
            CorrelationId,
            Title,
            CONCAT(
                (CASE
                    WHEN WebUrl <> '' THEN CONCAT(ServerUrl,'/') ELSE ServerUrl END),
                WebUrl,DocumentPath) 'URL',
                LogTime
            FROM WSS_Logging.dbo.RequestUsage
            WHERE UserLogin <> 'nt authorityiusr'
            AND UserLogin <> 'i:0#.w|pfnetzz_sharepoint13'
            AND DocumentPath LIKE '%.aspx'
            AND DocumentPath NOT LIKE '%/_layouts/%'
            AND UserLogin <> 'PFNETE01BrownS'
            AND UserLogin <> 'i:0#.w|pfnetsharepointtestacc1') as a
            WHERE LogTime >= DATEADD(day,-30, GETDATE())
    ),
    cteVisitsLastYear
    (
        CorrelationID,
        Title,
        URL,
        TotalVisits
    )
    AS
    (
        SELECT
        CorrelationID,
        Title,
        URL,
        COUNT(URL) OVER (PARTITION BY URL) 'TotalVisits'
        FROM(
            SELECT
            CorrelationId,
            Title,
            CONCAT(
                (CASE
                    WHEN WebUrl <> '' THEN CONCAT(ServerUrl,'/') ELSE ServerUrl END),
                WebUrl,DocumentPath) 'URL',
                LogTime
            FROM WSS_Logging.dbo.RequestUsage
            WHERE UserLogin <> 'nt authorityiusr'
            AND UserLogin <> 'i:0#.w|pfnetzz_sharepoint13'
            AND DocumentPath LIKE '%.aspx'
            AND DocumentPath NOT LIKE '%/_layouts/%'
            AND UserLogin <> 'PFNETE01BrownS'
            AND UserLogin <> 'i:0#.w|pfnetsharepointtestacc1') as a
            WHERE LogTime >= DATEADD(day,-365, GETDATE())
    )
SELECT DISTINCT
cteUniquePages.Title,
cteUniquePages.URL,
cteUniquePages.HitDate,
cteUniquePages.TotalVisitsOnDate,
cteVisitsAllTime.TotalVisits 'All Time Visits',
cteVisitsLast7Days.TotalVisits 'Visits Last 7 Days',
cteVisitsLast30Days.TotalVisits 'Visits Last 30 Days',
cteVisitsLastYear.TotalVisits 'Visits Last Year',
cteUniquePages.DontUseThisDate
FROM cteUniquePages
LEFT JOIN cteVisitsAllTime ON cteVisitsAllTime.CorrelationID = cteUniquePages.CorrelationID
LEFT JOIN cteVisitsLast7Days ON cteVisitsLast7Days.CorrelationID = cteUniquePages.CorrelationID
LEFT JOIN cteVisitsLast30Days ON cteVisitsLast30Days.CorrelationID = cteUniquePages.CorrelationID
LEFT JOIN cteVisitsLastYear ON cteVisitsLastYear.CorrelationID = cteUniquePages.CorrelationID
ORDER BY DontUseThisDate DESC, cteUniquePages.URL