Complex JOIN example (involving FULL, LEFT OUTER)

,

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.

select 
    Number, 
    ucount, 
    acount
from 
    SequentialNumbers LEFT OUTER JOIN
    (
        select isNull( aa.a1, bb.b1 ) as wk, aa.a2 as ucount, bb.b2 as acount
        from
        (
	select datepart(ww,createDate) a1, count(*) a2 from users
	where datepart(yy,createDate) = '2005'
	group by datepart(ww,createDate)
	) aa 
	FULL OUTER JOIN 
	(
	select datepart(ww,StartTime) b1,count(datepart(ww,StartTime)) b2 from AccessLog
	where StartTime in(
		select max(StartTime) from AccessLog
		where datepart(yy,StartTime) = '2005'
		group by UserID)
	group by  datepart(ww,StartTime)
	) bb
    	on (aa.a1 = bb.b1)
    ) cc on (Number = cc.wk)
where Number <= 52
order by 1

AG/JK

Rate

5 (1)

Share

Share

Rate

5 (1)