sqlsql-serverdatabasebetween

SQL Query Where Date = Today Minus 7 Days


I have a SQL table of hits to my website called ExternalHits. I track the URL as URLx and the date the page was accessed as Datex. I run this query every week to get the count of total hits from the week before, and every week I have to manually change the "between" dates. Is there some way I can change my query so that the "between" dates are something like TODAY AND TODAY-7? Ijust want to not have to manually change the dates every week.

    SELECT URLX, COUNT(URLx) AS Count
    FROM ExternalHits
    WHERE datex BETWEEN '02/27/2017' AND '03/05/2017'    
    GROUP BY URLx
    ORDER BY Count DESC; 

Solution

  • declare @lastweek datetime
    declare @now datetime
    set @now = getdate()
    set @lastweek = dateadd(day,-7,@now)
    
    SELECT URLX, COUNT(URLx) AS Count
    FROM ExternalHits
    WHERE datex BETWEEN @lastweek AND @now
    GROUP BY URLx
    ORDER BY Count DESC;