Grouping by hour and minute from TimeStamped data

  • TSQL is not my daily language and I could use a hand figutring this one out.

    I have date that I'd like to report on that shows the number of processes that start every 10 minutes for a given day of the week.

    The data consists of 6 years worth. I'd like to pull reports for a year or for all years

    Raw Data looks like :

    [Timestamp]   [Count]   [Day Of the Week]

    2021-12-14 16:10:00.0000000  30  Tue

    So I want to end up with

    Day_of_Week Start Time Counts

    Fri 12:00 5

    Fri 12:20 8

    Fri 12:10 9

    Fri 12:30 5

    for each day Mon -Sun and every 10 minute interval for each day with multiple years of data rolled up into each day and timeslice

    My Query is returning multiple values for, say Fri 12:00 with different counts!

    What am I doing wrong?

    SELECT [Day_of_Week], 
    (FORMAT([dbo].[QA-WFStartTimes].[StartTime],'hh:mm')) as [Start Time],
    YEAR([dbo].[QA-WFStartTimes].[StartTime]) as StartYear
    ,SUM([Count_Started]) As Counts
    FROM [dbo].[QA-WFStartTimes]
    WHERE StartTime > '12/31/2020'
    group by [StartTime],[Day_of_Week]
    order by Day_of_Week, StartTime

     

    • This topic was modified 2 years, 3 months ago by  MothInTheMachine. Reason: Added sample data

    Even as a mother protects with her life
    Her child, her only child,
    So with a boundless heart
    Should one cherish all living beings;

  • Without seeing any of your data or datatypes, I am really just guessing, but I would be willing to bet that it is related to your group by statement on "StartTime".  My GUESS is that StartTime is a datetime datatype.  So if you were grouping by that, each Friday on the calendar would be seen as a unique "Friday" for example, as they would each have their own unique StartTime.

    The Raw data you mentioned does not match the column names in the query you wrote and the results from the query don't appear to match what you want for an output (4 columns vs 3 columns).

    What would be helpful is some sample data to show the problem and the expected output.  But I am expecting it is related to your group by StartTime.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • You're right. I added the YEAR part mid post. The Start Time is a DateTime field.

    StartTimeCount StartedDay of Week
    12/14/2021 16:1030Tue
    12/14/2021 15:4016Tue
    12/14/2021 15:301Tue
    12/14/2021 15:104Tue

    Even as a mother protects with her life
    Her child, her only child,
    So with a boundless heart
    Should one cherish all living beings;

  • No sample data, so unable to test.  You might have to include DATENAME(WEEKDAY, CA1.[Start Time]) in the GROUP BY, but I don't think that's required.  I can't remember for sure how deterministic that function is.

    SELECT 
    DATENAME(WEEKDAY, CA1.[Start Time]) AS [Day_of_Week],
    CA1.[Start Time],
    SUM([Count_Started]) AS Counts
    FROM [dbo].[QA-WFStartTimes] QA
    CROSS APPLY (
    SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, QA.[StartTime]) / 10 * 10, 0) AS [Start Time]
    ) AS CA1
    WHERE QA.StartTime >= '20210101'
    GROUP BY CA1.[Start Time]
    ORDER BY [Day_of_Week], CA1.[Start Time]

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • It's been a looooong time since I've had to ask for sql help...lol. I've added an attachement with some sample data. Sorry about that and thank you for taking a look at this!

    Even as a mother protects with her life
    Her child, her only child,
    So with a boundless heart
    Should one cherish all living beings;

  • MothInTheMachine wrote:

    It's been a looooong time since I've had to ask for sql help...lol. I've added an attachement with some sample data. Sorry about that and thank you for taking a look at this!

    I'm not seeing an attachment anywhere but a quick look at Scott Pletcher's code above says it'll do the trick for you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply