z0nia z0nia - 1 year ago 52
SQL Question

SQL Server create report of emails

I have a table called

that contains these columns:

Created - datetime
Processed - float (boolean)
Dateprocessed - datetime
Conference - varchar
Session - varchar
FDBue - datetime
Speaker - varchar
Email - varchar
Type - varchar
ID - int

What I want is a query, report, or view that will show me what sessions a speaker is presenting at, and the due date of their presentation content, something similar to:

speaker conference session fbdue
Joe Conf1 SessionA 1/1/2010
Conf1 SessionB 1/1/2010
Conf2 SessionE 1/10/2010
Amy Conf1 SessionA 1/3/2010
Conf2 SessionB 1/3/2010

How can I achieve this in SQL Server? The
column is an identity column. The same speaker can speak at multiple sessions at the conference.

Answer Source
        when row_number() over (
                 partition by coalesce(speaker, email)
                 order by conference, session
             ) = 1
        then coalesce(speaker, email)
    end as speaker,
    conference, session, fbdue
from SpeakerSessions
order by coalesce(speaker, email), conference, session;

This problem amounts to knowing which row is the first in a series of rows. That kind of information can't be determined by looking within the row itself.

Looking around at other rows is basically what analytic/window functions are all about. row_number() is one that applies a numbering to a set of rows in the designated order. The partition by limits the numbering to each speaker and allows it to reset on the next one. The order by is also important and much match the order by of the query results (after speaker).

Ultimately the expression says this: if we're at the first row then return the name of the speaker otherwise return a null (blank). Depending on how you plan to use this you may prefer to return an empty string with else '' instead of the default null.

Many reporting tools have a "suppress repeated values" option to handle this logic for you. It would also be easy to do this in Excel with a formula that looks at the previous cell if you were pulling data into a spreadsheet.