none
Automating Report

    Question

  • I need to isolate all of the records for a given day(date) and break it down for each hour.
    So basically, I need a count of all records that have value "X" that occurred each hour during a given day(date).

    Management is basically look for something like this:

    Between the hour of 00:00:00 to 00:59:59   22 records saved day 15-09-2017

    Between the hour of 01:00:00 to 01:59:59 15 records saved day 15-09-2017

    so on and so forth this should loop for every hour for the given date
    • Edited by Sahana G M Thursday, December 07, 2017 9:38 AM
    Thursday, December 07, 2017 9:36 AM

All replies

  • Hello,

    Please post table design as DDL, some sample data as DML Statement and the expected result; see POSTING TIPS - Code, Images, Hyperlinks, Details


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, December 07, 2017 9:39 AM
  • See the logic here

    You can either use the function as is or reuse the logic in your module

    https://visakhm.blogspot.in/2010/02/aggregating-data-over-time-slots.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 07, 2017 9:47 AM
  • Hi Sahana,

    In your scenario, you can create a Time calendar table for each hour of the day. 

    Please refer to following script, see if it works for you:

    create table #test
    (
    Id int,
    DateVal datetime
    )
    
    insert into #test values
    (1,'2017-09-15 00:05:47'),
    (1,'2017-09-15 02:15:47'),
    (1,'2017-09-15 02:45:47'),
    (1,'2017-09-15 03:05:47'),
    (1,'2017-09-15 04:05:47')
    
    Declare @date datetime
    select @date='2017-09-15 00:00:00'
    
    ;with cte as(
    select @date as StartTime,DATEADD(SECOND,-1,DATEADD(HOUR,1,@date)) as EndTime
    union all
    select DATEADD(HOUR,1,StartTime) as StartTime,DATEADD(HOUR,1,EndTime) as EndTime
    from cte
    where StartTime<DATEADD(HOUR,23,@date)
    )
    
    select c.StartTime,c.EndTime,COUNT(t.DateVal) as Num
    from cte c
    join #test t on t.DateVal between c.StartTime and c.EndTime
    group by c.StartTime,c.EndTime
    
    

    If it doesn't satisfy your requirement, please share us your table structure and some sample data with create and insert statement like above. So that we can make a test and provide a corresponding solution.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 08, 2017 1:54 AM
  • Hi Xi Jin,

    Thank you for the query, it meets my requirement.

    Thanks,

    Sahana

    Friday, December 08, 2017 5:29 AM
  • Hi Sahana,

    You're welcome. :)

    And I'm glad to hear that it works for you. Could you please kindly mark the corresponding reply? It will help others with the relevant issue find the answer more easily.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 08, 2017 5:35 AM