Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Complex JOIN example (involving FULL, LEFT OUTER)

By Jambu Krishnamurthy,

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
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: 555 | Views in the last 30 days: 3
Related Articles

I need to count number of columns in group

I need to count number of columns in group


how to count number of rows in a table column

how to count number of rows in a table column


Count number of columns in a CSV file

Is it possible to Count number of columns in a CSV file??


Counting non-blank columns

How can I count the number of rows that have non-blank columns across a table?


Create table with number of columns in a parameter

Possible to create a table with a @param containing the number od columns to create?


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones