Grouping by hour and minute from TimeStamped data

Question

Grouping by hour and minute from TimeStamped data

MothInTheMachine

Hall of Fame

Points: 3251
More actions
December 15, 2021 at 6:42 pm

#3964056
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, 11 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;

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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.

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 2

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;

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 4

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;

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

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)