MS SQL Server - T-SQL query to find records for the interval of every 30 minutes

Simple math on time:  DATEDIFF(MINUTE, CAST(CreatedON AS DATE), CreatedON) / 30 gives you your time bucket number.. e.g.

WITH Data
AS ( SELECT T.* ,
            DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(T.CreatedON AS DATE), T.CreatedON) / 30 * 30, CAST(CAST(T.CreatedON AS DATE) AS DATETIME)) AS Bucket
     FROM   yourTable T )
SELECT   D.Bucket ,
         COUNT(*)
FROM     Data D
GROUP BY D.Bucket;

Select allOpen in new window

Hi Ste5an, It's showing data from the year 2013. I need current date (from 00:00 to 23:30) count, also it looks like Bucket column data is not correct.I means it's not showing count of case Records between every 30 minutes interval of duration

I need current date [..]

Filter for it??

... WHERE CreatedON >= CAST(GETDATE() AS DATE)

Select allOpen in new window

Hello Ste5an,

image 1: Pls find the records that are created on DB.
image 2: Pls find the suggested above query output.

in image 2: the one record is created between 14:30 - 15:00 and  two records created between 15:00 - 15:30. Is it possible to show this time in Bucket column like
Bucket                                                           Total Case Count                            
============================================
2018-11-12 14:30:00 - 15:00:00                           1
2018-11-12 15:00:00 - 15:30:00                           2

Well, it's still hard to tell without concise and complete sample. E.g.

DECLARE @Sample TABLE (
    create_date DATETIME
);

INSERT INTO @Sample ( create_date )
VALUES ( '20181212 14:01' ) ,
       ( '20181212 14:11' ) ,
       ( '20181212 14:21' ) ,
       ( '20181212 14:31' ) ,
       ( '20181212 14:41' ) ,
       ( '20181212 14:51' ) ,
       ( '20181212 15:01' );

WITH Data
AS ( SELECT T.* ,
            DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(T.create_date AS DATE), T.create_date) / 30 * 30, CAST(CAST(T.create_date AS DATE) AS DATETIME)) AS Bucket
     FROM   @Sample T )
SELECT   D.Bucket ,
         COUNT(*)
FROM     Data D
GROUP BY D.Bucket
ORDER BY D.Bucket;

Select allOpen in new window

the above query is showing different results now in below image, the below result set is changing requirement :)The query you shared earlier is correct one, only thing is i need results like below.
CreatedON                                                                                    Total Case Count                            
============================================================
2018-11-12 14:30:00 TO 2018-11-12 15:00:00                           1
2018-11-12 15:00:00 TO 2018-11-12 15:30:00                           2

I have taken above date and time only for testing purpose. actually i have to fetch the data for current date (from 00:00 to 23:59). Can you please suggest how we can accomplish above output without hard coding of date and time values.....

1) ??

2) Formatting should be done in the front-end.

3) I already posted a sample.

... WHERE CreatedON >= CAST(GETDATE() AS DATE)

Select allOpen in new window

Is it not possible to concat like below in SQL

createdon' ' To 'cretaedon

I am not a sql experts, here i am seeking your help....

Just a reason more to do it in the frontend...

.. FORMAT(Bucket, 'g') + ' TO ' + FORMAT(DATEADD(MINUTE, 30, Bucket), 'g') ..

Select allOpen in new window

I think there is a lot of confusion here, and you've got several questions open addressing the same problem. Let's take a step back and ask some clarifying questions rather than assuming what you want.

Do you want the count associated with the start time or the end time of the time period? If there are 20 rows between 08:00 and 08:30 do you want to see those in the total for 08:00 or 08:30?
If I am reading this thread correctly, to summarize, you want a 'running total' of counts within each 1/2 hour time period. Is that correct. In other words, the count for any 30-minute bucket should also include previous counts.
Do you want ALL time slots shown, even if there was no 'activity' (see below)?
If there were 10 rows created between 8:00 and 8:30, 5 rows between 9:00 and 9:30 and 10 rows created between 12:30 and 1, are you wanting to see:

running totals:
10   12/12/2018 08:30
15   12/12/2018 09:30
25   12/12/2018 13:00
- vs -
10   12/12/2018 08:00
15   12/12/2018 09:00
25   12/12/2018 12:30

or

counts per time period:
10   12/12/2018 08:00
 5    12/12/2018 09:00
10   12/12/2018 12:30
- vs -
10   12/12/2018 08:30
 5    12/12/2018 09:30
10   12/12/2018 13:00


All time slots accounted for:
...
  0   12/12/2018 08:00
10   12/12/2018 08:30
10   12/12/2018 09:00
15   12/12/2018 09:30
15   12/12/2018 10:00
15   12/12/2018 10:30
15   12/12/2018 11:00
15   12/12/2018 11:30
15   12/12/2018 12:00
15   12/12/2018 12:30
25   12/12/2018 13:00
...

Hi Doug Bishop,

Do you want the count associated with the start time or the end time of the time period? If there are 20 rows between 08:00 and 08:30 do you want to see those in the total for 08:00 or 08:30?
<Thean S>: I want to see row count of 20 for 8:00 to 8:30

If I am reading this thread correctly, to summarize, you want a 'running total' of counts within each 1/2 hour time period. Is that correct.
<Thean S>: You're correct

Do you want ALL time slots shown, even if there was no 'activity' (see below)?
<Thean S>In output, I want to see slots only where row count exist. Ignore the slots where no activity.

I don't want to see slots where row count is 0

As i said, I want to see date time slots as mentioned below  in output using SQL query. Just I will copy SQL output data in CSV/xls, I don't want to create any SSRS reports for this.....

I will create SQL job using SQL script, job will execute every day at 23:55, the report should contain row count info with date time slots (like below) starting from 00:00 to 23:50. The report should contain only current date row count.

CreatedON                                                                                    Total Case Count                            
============================================================
2018-11-12 14:30:00 TO 2018-11-12 15:00:00                           1
2018-11-12 15:00:00 TO 2018-11-12 15:30:00                           2

I would think sta5en's solution above would work. If you are scheduling the job to run at a specific time and you know it will always be run before midnight, add the following WHERE clause to only get today's data:

WHERE	 D.Bucket >= CAST(GETDATE() AS DATE) 
AND		 D.Bucket < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)

Select allOpen in new window

Yes, Sta5en's solution is working. Is it possible to see/or create date-time slots in output as mentioned above using MS SQL (TSQL) technology?

To show time range under Bucket, use:

WITH Data
AS ( SELECT T.* ,
            DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(T.create_date AS DATE), T.create_date) / 30 * 30, CAST(CAST(T.create_date AS DATE) AS DATETIME)) AS BucketStart,
            DATEADD(MINUTE, 30 + DATEDIFF(MINUTE, CAST(T.create_date AS DATE), T.create_date) / 30 * 30, CAST(CAST(T.create_date AS DATE) AS DATETIME)) AS BucketEnd
     FROM   sys.tables T )

SELECT   CONVERT(VARCHAR(20), D.BucketStart, 120) + ' - ' + CONVERT(VARCHAR(10), BucketEnd, 108) AS Bucket,
         COUNT(*)
FROM     Data D
WHERE	 D.BucketStart >= CAST(GETDATE() AS DATE) 
AND		 D.BucketStart < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
GROUP BY D.BucketStart, BucketEnd
ORDER BY D.BucketStart;

Select allOpen in new window

Already posted that:

.. FORMAT(Bucket, 'g') + ' TO ' + FORMAT(DATEADD(MINUTE, 30, Bucket), 'g') ..

Select allOpen in new window

Sorry. This has been a hard thread to follow. I think the author has 5 questions posted and each is overlapping the others.

Thanks a lot for your help on this.