Returning records for this week

Question

Returning records for this week

Brendan Collins

SSC Eights!

Points: 822
More actions
September 6, 2004 at 3:28 am

#59319

Hi
I have a page which is used to display the number of hits on various pages
and is filtered by: today, this week, this month and this year.
I have managed to get the SQL for each of them except 'this week'. Anyone
know how I could modify the below SQL, which returns all hits today, so that
it will return results for this week. For example today is Monday, so it
should only return results for today, but tomorrow it should display results
for both today and tomorrow, and so on.
I know the DateFirst sets which day of the week that the week starts with,
so have set it below to Monday, but after this I am a bit stuck.
Set DateFirst 1
SELECT TBLLOG_PAGE, COUNT(TBLLOG_PAGE) AS PAGECOUNT
FROM TBLLOG
WHERE Day(TBLLOG_DATE) = Day(GetDate())
GROUP BY TBLLOG_PAGE
Any help or pointers is much appreciated.
Thanks in advance
Brendan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brendan Collins SSC Eights! Points: 822 More actions · Answer 1

Hi all

I have managed to come up with the below SQL code to return the correct values. If anyone can see a way of optimising the code, then that is more than welcome.

Thanks

Brendan

Set DateFirst 1

Declare @DateNum int

Set @DateNum = Datepart(dw,GetDate())

PRINT @DateNum

SELECT TBLLOG_PAGE, COUNT(TBLLOG_PAGE) AS PAGECOUNT

FROM TBLLOG

WHERE TBLLOG_DATE >= (GetDate()-@DateNum)

GROUP BY TBLLOG_PAGE

Brendan Collins SSC Eights! Points: 822 More actions · Answer 2

Hi

Have just found out that my code returns incorrect results if the First day of the week matches today, in that it also returns yesterdays records.

Am looking into a solution.

Thanks

Brendan

adrian_rf SSC Veteran Points: 275 More actions · Answer 3

SET DATEFIRST 1

DECLARE @dtToday datetime, @dtToday_midnight datetime, @dtFOW datetime

SET @dtToday = GetDate() --test '2004-09-13'

SET @dtToday_midnight = CAST(DATEPART(yyyy, @dtToday) AS VARCHAR(4)) + '-' + CAST(DATEPART(mm, @dtToday) AS VARCHAR(2)) + '-' + CAST(DATEPART(dd, @dtToday) AS VARCHAR(2))

SET @dtFOW = DATEADD(dd, -(DATEPART(dw, @dtToday_midnight)-1), @dtToday_midnight)

--SELECT ...

--WHERE TBLLOG_DATE >= @dtFOW

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

Hey Adrian

A faster way to do:

SET @dtToday_midnight = CAST(DATEPART(yyyy, @dtToday) AS VARCHAR(4)) + '-' + CAST(DATEPART(mm, @dtToday) AS VARCHAR(2)) + '-' + CAST(DATEPART(dd, @dtToday) AS VARCHAR(2))

is

SET @dtToday_midnight = CAST(CONVERT(char(8), GetDate(), 112) as datetime)

Convert 112 into a char/varchar type will get you YYYYMMDD only.

Julian Kuiters
juliankuiters.id.au

Brendan Collins SSC Eights! Points: 822 More actions · Answer 5

Thanks for all the help. The code works perfectly!

Brendan