CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Somebody
Somebody

Reputation: 2789

How to count records by day from specific hour in a range of date using SQL

I have a datetime field and I want to count all records by day from hour 8:00 to 9:00 in a range of date (i.e: from: 01/01/2012 to: 01/03/2012).

for instance, if I have the below data:

2012-01-01 08:26
2012-01-01 08:40
2012-01-01 09:26
2012-01-01 10:26
2012-01-02 08:06
2012-01-02 09:26
2012-01-02 09:40
2012-01-03 08:30
2012-01-03 10:26

the result should look like this:

2012-01-01 2
2012-01-02 1
2012-01-03 1

EDIT: Here is what I got using @gbn solution (it works for me)

SELECT    COUNT(*),  convert(varchar(10),ScanDate,101)
FROM    tblSingleBox (nolock)
WHERE    DATEPART(Hour, scandate) = 8
    and (scandate>='01/03/2012' and scandate<'01/05/2012')
GROUP BY    convert(varchar(10),ScanDate,101) 
order by convert(varchar(10),ScanDate,101)

Upvotes: 1

Views: 5509

Answers (3)

John Franklin
John Franklin

Reputation: 111

SQL Server 2005

IF OBJECT_ID('tempdb.dbo.#DATES') IS NOT NULL
    DROP TABLE #DATES

CREATE TABLE #DATES
    (   LogDate smalldatetime   )

INSERT INTO #DATES
    (   LogDate )
            SELECT  '2012-01-01 08:26'
UNION SELECT '2012-01-01 08:40'
UNION SELECT '2012-01-01 09:26'
UNION SELECT '2012-01-01 10:26'
UNION SELECT '2012-01-02 08:06'
UNION SELECT '2012-01-02 09:26'
UNION SELECT '2012-01-02 09:40'
UNION SELECT '2012-01-03 08:30'
UNION SELECT '2012-01-03 10:26'
UNION SELECT '2012-01-03 15:26'

DECLARE @nStartHour smallint
DECLARE @nEndHour smallint

SET @nStartHour = 15
SET @nEndHour = 16

SELECT  CONVERT(varchar, LogDate, 101), COUNT(*)
FROM        #DATES
WHERE       DATEPART(hour,LogDate) BETWEEN @nStartHour AND (@nEndHour - 1)
GROUP BY CONVERT(varchar, LogDate, 101)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

In this case the simplest code and the fastest code will likely vary a lot, and possibly be dependent on the size of your data set.

I suspect the fastest would be to join on a calendar and filter. In this case I'll make a calendar before I start, but I suggest a permanent one...

CREATE TABLE #calendar (
  date AS DATETIME
)
INSERT INTO #calendar SELECT '2011-12-31' UNION ALL SELECT '2012-01-01'
            UNION ALL SELECT '2012-01-02' UNION ALL SELECT '2012-01-03'
            UNION ALL SELECT '2012-01-04' UNION ALL SELECT '2012-01-05'

SELECT
  calendar.date,
  COUNT(*)
FROM
  yourTable
INNER JOIN
  #calendar    AS calendar
    ON  yourTable.DateTimeField >= calendar.date + @minTime
    AND yourTable.DateTimeField <  calendar.date + @maxTime
WHERE
      calendar.date >= @startDate
  AND calendar.date <= @endDate
GROUP BY
  calendar.date

This should yield the most index friendly query, and so for any reasonable size of data be very efficient.

NOTE:

The calendar.date + @Time sections will vary. You just said MS SQL Server, so it would be more like...

    ON  yourTable.DateTimeField >= DATEADD(hour, @minHour, calendar.date)
    AND yourTable.DateTimeField <  DATEADD(hour, @maxHour, calendar.date)

Upvotes: 2

gbn
gbn

Reputation: 432672

SQL Server 2005 and earlier

SELECT
   COUNT(*),
   DATEADD(dd, DATEDIFF(dd, 0, SomeDateTime), 0)
   -- SQL Server 2008: CAST(SomeDateTime AS date)
FROM
   SomeTable
WHERE
   DATEPART(Hour, SomeDateTime) = 8
GROUP BY
   DATEADD(dd, DATEDIFF(dd, 0, SomeDateTime), 0);
   -- SQL Server 2008: CAST(SomeDateTime AS date);

MySQL:

SELECT
   COUNT(*),
   DATE(SomeDateTime)
FROM
   SomeTable
WHERE
   HOUR(SomeDateTime) = 8
GROUP BY
   DATE(SomeDateTime);

Upvotes: 4

Related Questions