CODEWITHSUNDEEP
sqlsql-server
FMarcel
FMarcel

Reputation: 18

SQL count hour for each date of a datetime column

I have a subquery which returns just one column with a datetime type:

LOG_DATE
---------------------
2014-06-30 08:15:00
2014-07-01 08:00:00
2014-07-01 08:45:00
2014-07-01 10:00:00
2014-07-01 13:00:00
2014-07-01 14:00:00
2014-07-01 14:30:00
2014-07-01 14:55:00
2014-07-02 08:10:00
2014-07-02 09:00:00
2014-07-02 09:10:00
2014-07-02 09:25:00
2014-07-02 09:40:00
2014-07-02 10:10:00

I'm trying to write a script using this subquery which should return:

DATE        |  HOUR      |  TOTAL
----------------------------------
2014-06-30  |  08:00:00  |  1
2014-07-01  |  08:00:00  |  2
2014-07-01  |  10:00:00  |  1
2014-07-01  |  13:00:00  |  1
2014-07-01  |  14:00:00  |  3
2014-07-02  |  08:00:00  |  1
2014-07-02  |  09:00:00  |  4
2014-07-02  |  10:00:00  |  1

Can someone help me how to get it?

Upvotes: 0

Views: 767

Answers (2)

Dave C
Dave C

Reputation: 7392

This should get you started:

SELECT CONVERT(VARCHAR(10), LOG_DATE, 120), CONVERT(VARCHAR(2), LOG_DATE, 108)+':00:00', COUNT(*)
FROM TABLE1
GROUP BY CONVERT(VARCHAR(10), LOG_DATE, 120), CONVERT(VARCHAR(2), LOG_DATE, 108)+':00:00'
ORDER BY CONVERT(VARCHAR(10), LOG_DATE, 120), CONVERT(VARCHAR(2), LOG_DATE, 108)+':00:00'

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Try this:

SELECT CONVERT(DATE,LOG_DATE) [DATE],
       DATEPART(HOUR,LOG_DATE) [HOUR],
       COUNT(*) TOTAL
FROM YourTable
GROUP BY CONVERT(DATE,LOG_DATE),
         DATEPART(HOUR,LOG_DATE)
ORDER BY CONVERT(DATE,LOG_DATE),
         DATEPART(HOUR,LOG_DATE)

The results are:

╔════════════╦══════╦═══════╗
║    DATE    ║ HOUR ║ TOTAL ║
╠════════════╬══════╬═══════╣
║ 2014-06-30 ║    8 ║     1 ║
║ 2014-07-01 ║    8 ║     2 ║
║ 2014-07-01 ║   10 ║     1 ║
║ 2014-07-01 ║   13 ║     1 ║
║ 2014-07-01 ║   14 ║     3 ║
║ 2014-07-02 ║    8 ║     1 ║
║ 2014-07-02 ║    9 ║     4 ║
║ 2014-07-02 ║   10 ║     1 ║
╚════════════╩══════╩═══════╝

And here is a sqlfiddle with a demo of it.

Upvotes: 1

Related Questions