Count and grouped by in t-sql

Question

I have a table that looks something like this

CREATE TABLE MyTable(
    [RecordID] [bigint] IDENTITY(1,1) NOT NULL,
    [PortName] [nvarchar](50) NULL,
    [ReceivedEvent] [datetime] NULL,
    [SentEvent] [datetime] NULL,
);

The data could then be

   RecordID | PortName | ReceivedEvent       | SentEvent
   1        | Port1    | 2009-10-20 10:20:00 | NULL
   2        | Port2    | NULL                | 2009-10-20 10:10:00
   3        | Port2    | NULL                | 2009-10-20 10:02:00
   4        | Port2    | NULL                | 2009-10-20 11:00:00

I now need two question over this table:

1) I need to get a count of number of rows grouped by port and date (not time). The date is however a parameter in the question (In this case it could be "2009-10-20"). I also want the last "activity" in each hour

   NumberOfRows | PortName | Hour | LastActivityInHour 
   2            | Port2    | 10   | 2009-10-20 10:10:00
   1            | Port2    | 11   | 2009-10-20 11:00:00
   1            | Port1    | 10   | 2009-10-20 10:20:00

2) I also need one question doing the same thing but grouped by day. And day is as I said a incoming parameter. In this case it could be "2009-10-20"

   NumberOfRows | PortName | LastActivityInDay 
   3            | Port2    | 2009-10-20 11:00:00
   1            | Port1    | 2009-10-20 10:20:00

Answer 1

Since your data model is not properly normalized, this is uglier than it would need to be.

SELECT
  COUNT(t.RecordID) NumberOfRows,
  t.PortName,
  t.EventHour,
  MAX(t.EventDateTime) LastActivityInHour
FROM
  (
    SELECT
      RecordID,
      PortName,
      COALESCE(ReceivedEvent, SentEvent) EventDateTime,
      DATEPART(hh, COALESCE(ReceivedEvent, SentEvent)) EventHour,
      DATEADD(dd, 0, DATEDIFF(dd, 0, COALESCE(ReceivedEvent, SentEvent))) EventDate
    FROM
      MyTable
  ) t
WHERE
  t.EventDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @TheDateInQuestion))
GROUP BY
  t.PortName,
  t.EventHour
ORDER BY
  t.PortName,
  t.EventHour

The SQL to do the by-date grouping is very similar, I'll leave deriving it as an exercise for you. ;-)

I'm sure that there are other ways to get the same result. Making a view out of what is the inner query here will help clean up the query as a whole.