Query that returns record that arn't duplicated in another table

[Lukelrc]

Hi.

I have a table (websitehits) which holds statistics about websites.
This table has a date field (datecounted). What I need is to create a
query which returns a list of dates between two date ranges (say a
year ago from today and a year from now) which only shows dates that
haven't been used in the websitehits table for a given website.

For example if my table contains something like:

Website Datecounted Hits
Site1 01/01/04 6000
Site1 01/02/04 6500
Site1 01/03/04 6250
Site2 01/03/04 1000
Site2 01/04/04 1200
Site2 01/05/04 1500

So if query for ‘site1' then I'd get a list of all dates between
30/11/03 to 30/11/05 with the exception of the dates 01/01/04,
01/02/04 and 01/03/04.

So far I've tried to do this using another table named calendar which
contains a very long list of dates and to use this to produce the list
– but I'm not getting very far.

By the way I'm using sql server, an I need this query to generate a
list for an asp page - so I need to pass the website name as a
parameter so I guess I need to make this query as a stored procedure.

Does anyone know how this can be done?

[Hugo Kornelis]

On 30 Nov 2004 03:18:02 -0800, Lukelrc wrote:

Hi.

I have a table (websitehits) which holds statistics about websites.
This table has a date field (datecounted). What I need is to create a
query which returns a list of dates between two date ranges (say a
year ago from today and a year from now) which only shows dates that
haven't been used in the websitehits table for a given website.

For example if my table contains something like:

Website Datecounted Hits
Site1 01/01/04 6000
Site1 01/02/04 6500
Site1 01/03/04 6250
Site2 01/03/04 1000
Site2 01/04/04 1200
Site2 01/05/04 1500

So if query for ‘site1' then I'd get a list of all dates between
30/11/03 to 30/11/05 with the exception of the dates 01/01/04,
01/02/04 and 01/03/04.

So far I've tried to do this using another table named calendar which
contains a very long list of dates and to use this to produce the list
– but I'm not getting very far.

By the way I'm using sql server, an I need this query to generate a
list for an asp page - so I need to pass the website name as a
parameter so I guess I need to make this query as a stored procedure.

Does anyone know how this can be done?

Hi Lukelrc,

Two possibilities (both untested, so beware of typos)

SELECT Calendar.dt
FROM Calendar
WHERE NOT EXISTS
(SELECT *
FROM websitehits
WHERE websitehits.Website = @Website
AND websitehits.Datecounted = Calendar.dt)

SELECT Calendar.dt
FROM Calendar
LEFT JOIN websitehits
ON websitehits.Website = @Website
AND websitehits.Datecounted = Calendar.dt
WHERE websitehits.dt IS NULL

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)