Q:

SQL Server Compiling and Calculating Data from Multiple Tables

I'm trying to find the hour of day in which a subscriber in my database opens the most emails.
I have a table with a bunch of subscriber IDs, I have another table with email opening records mapped to a table that stores sent emails which then maps to specific subscriber IDs.
So my basic desire is to get a table output with each row being a specific subscriber (subscriber ID) and the hour that they opened the most emails in. So I need to first go through each subscriber ID, then I guess create a temp table with each row being a different hour associated with the number of emails they opened in that hour. Then I need to do a select Max to get the max row from that temp table, then I need to place that into my output table, and repeat for the next subscriber.
All I care about is the hour of day. I don't care about month, or year or time. I just want to know what hour of day people mostly open emails.
I've been trying and trying, using CTEs and while loops but to no avail. Any help would be greatly appreciated.
Here's the (minimized) structure:
Table: Subscribers
| subscriber_id | last_name | first_name |
|   9999999999  |   Smith   |   John     |
Table: SentEmail
| message_id | subscriber_id |
| 9028340    | 9999999999    |
Table: OpenEmails
| open_id | message_id | dtopened                  |
| 9923489 | 9028340    | '2011-11-22 15:53:02.157' |
My goal, an output table like this (last column is unnecessary) with each subscriber_id being unique in the list:
| subscriber_id | OpenHour | NumOpens |
| 999999999     | 10       |   32     |

A:

I made some assumptions since there wasn't a structure included.
Here is the structure I used.
CREATE TABLE Subscriber (
    Id int not null identity(1,1),
    SubscriberId varchar(50)
    )
CREATE TABLE EmailOpened (
    OpenDate DateTime,
    EmailId int
    )
CREATE TABLE Emails (
    EmailId int not null identity(1,1),
    SubscriberId varchar(50),
    EmailText varchar(max)
)
GO

Here is the query I ended up with.
WITH OpenedByHour AS (
    SELECT 
        SubscriberId,
        DATEPART(YEAR, OpenDate) AS OpenYear,
        DATEPART(DAYOFYEAR, OpenDate) AS OpenDOY,
        DATEPART(HOUR, OpenDate) AS OpenHour,
        COUNT(1) AS OpenCount
    FROM Emails
    JOIN EmailOpened
        ON Emails.EmailId = EmailOpened.EmailId
    GROUP BY 
        SubscriberId,
        DATEPART(YEAR, OpenDate),
        DATEPART(DAYOFYEAR, OpenDate),
        DATEPART(HOUR, OpenDate)
    ),
    MaxOpenedByHour AS (
    SELECT
        SubscriberId,
        OpenYear,
        OpenDOY,
        OpenHour,
        OpenCount,
        Row_Number() Over (Partition By SubscriberId
                Order By OpenCount Desc) AS MaxRow
    FROM OpenedByHour
    )
SELECT SubscriberId,
    OpenYear,
    OpenDOY,
    OpenHour,
    OpenCount
FROM MaxOpenedByHour
WHERE MaxRow = 1

