Showing how many page hits for past 40 days 2

[3dColor]

I would like to chart the number of times a page has been view for the past 40 days.

Whenever the page gets a hit, a timestamp is recorded for that page.

The following query returns all the hits in the past 40 days (statDuration):

SELECT		*
FROM		Stats
WHERE		ListingFk = #URL.ad# AND DATE(timeStampStats) > #statDuration#
ORDER BY 	timeStampStats DESC

This query does a great job at returning all the hits for the past 40 days, but I have two problems:

1. Some days a page is viewed more than one time, how do I extract the total times it was view for that day?

2. Some days a page might not be viewed at all, how do I provide a zero for my chart if that day does not exist and I want to show each of the past 40 days?

 

[3dColor]

Rudy, I also tried your above suggestion, but that provided the same results as before.

 

[r937]

i'm terribly sorry, i overlooked the fact that a timestamp includes a time portion

Tony saw that a lot earlier than i did, eh

so we just truncate the time off and bob's your uncle

instead of

LEFT OUTER
      join Stats
        on Stats.timeStampStats 
         = i_40.thedate

use

LEFT OUTER
      join Stats
        on [b]date(Stats.timeStampStats)[/b]
         = i_40.thedate

r937.com | rudy.ca

 

[3dColor]

Thanks Rudy.

I tried your latest revision and here is the output:

Rudy:
02-03, 2
02-02, 2
02-01, 2
01-31, 0
01-30, 4
01-29, 0
01-28, 2
01-27, 2
01-26, 0
01-25, 10

First thing I noticed is that it does not cover all the dates (doesn't extend to 1-13). Only ten which is probably a function of the Integers table having only 10 rows.

Then I went to the database to do some counting and here is what is actually in the DB, which does not match yours or Tony's:

Rudy:
02-03, 1
02-02, 1
02-01, 1
01-31, 0
01-30, 2
01-29, 0
01-28, 1
01-27, 1
01-26, 0
01-25, 5
01-24, 1
01-23, 5
01-22, 3
01-21, 6
01-20, 0
01-19, 0
01-18, 0
01-17, 0
01-16, 0
01-15, 3
01-14, 27
01-13, 1

Ideas?

 

[r937]

First thing I noticed is that it does not cover all the dates (doesn't extend to 1-13). Only ten which is probably a function of the Integers table having only 10 rows.

yep, your choice is either to generate the 40 numbers the way i showed with the cross join, or load up the integers table with 40 numbers


as for your counts, it looks like double-counting somehow, which might be indicative of extra integers in the integers table -- you sure you didn't load 0 through 9 more than once?

r937.com | rudy.ca

 

[3dColor]

Rudy, you hit the nail on the head, I did load the Integer table twice - good catch.

All working fine now.

Both yours (Rudy) and Tony's queries work just fine now.

Thanks again for the help!

 

[r937]

thanks for the feedback

next time i tell anybody about the integers table, i shall be sure to do it like this --

create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);




r937.com | rudy.ca

 

[3dColor]

Rudy, when you first mentioned the "Integers Table" i wanted to learn more so i placed that phrase into Google and I found quite a few forum posts from you on the subject of my question - so it sounds like many others have had the same question.

Thanks again.