Complex JOIN example (involving FULL, LEFT OUTER)

Total article views: 173 | Views in the last 30 days: 10

 Rate this |   Join the discussion |  Briefcase

A table Users contains a Create date column.
A table AccessLog contains StartTime column.
Both are date time fields.
Users table contains one row per user and
AccesLog contains as many rows as the number of
times the user Logged into the system.
The requirement is:
to produce a single table that has two different COUNTs... one column will have the count of the number of users created during some particular week in a year [Creations], and another column will have the count of the number of users whose last login occurred during that week [LastLogins].

Assuming the report is for a year, the output will contain
52 rows (one for each week of year) and two other columns
Creations, and LastLogins

Wk Creations LastLogins
-- --------- ----------
1 25 35
2 NULL NULL
3 32 89
4 NULL 25
5 36 NULL
. ... ...
. ... ...
. ... ...

As a supporting tool, you have an intermediate table
SequentialNumbers, which just has one column 'Number'
containing numbers from 1 to 1000. Experienced users
will know such a table is used for other purposes like
for handling comma separated strings passed into
Stored Procedures.

Total article views: 173 | Views in the last 30 days: 10