How to get records from database by hour base.In brief,I have a table with fied name 'messages'(varchar), 'msg_time(datetime)', port_number(int). Here I am recieving more than 250 sms messages per day. Now I want to know how many messages I recieved today. The thing is that the result should be by hourbased.It means it should be like

SELECT w.FromHour,
w.ToHour,
r.PortNumber,
COUNT(*)
FROM (
SELECT Number FromHour,
1 + Number ToHour
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 0 AND 23
) w
LEFT JOIN YourTable r ON r.YourHour >= w.FromHour AND r.YourHour < w.ToHour
GROUP BY w.FromHour,
w.ToHour,
r.PortNumber

SELECT w.FromHour,
w.ToHour,
r.PortNumber,
COUNT(*)
FROM (
SELECT Number FromHour,
1 + Number ToHour
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 0 AND 23
) w
LEFT JOIN YourTable r
ON datepart(hour,r.msg_time) >= w.FromHour AND datepart(hour,r.msg_time) < w.ToHour
GROUP BY w.FromHour,
w.ToHour,
r.PortNumber

Run these two queries and post back the result here.But already now I can see that you have at least on record for first column (Port 1) that should be calculated with my query.What data type is Port_ID? INT? You wrote NUMERIC...

Run these two queries and post back the result here.But already now I can see that you have at least on record for first column (Port 1) that should be calculated with my query.What data type is Port_ID? INT? You wrote NUMERIC...

The first query takes all ports and calculates the number of records associated with that port.And it seems that the port in use is #47 and there are 4 records with that port.You only provided four ports, named 1 to 4.

The second query takes all hours and calculates the number of records associated with that hour.And it seems that the hour in use is 00-01 and there are 4 records with that hour.

That result is expected and obviously there are only four records stored for today.