How to fetch the 2 hours data. (Summarize datetime)

Question

0.00/5 (No votes)

See more:

Hello, I would like to ask your assistance on this, will there anyway to summarize the
2 hour records on specific date? Below is the output when i tried to run my below modified query.

LoadingDatetime | Iphone
2020-03-25 11:00:00 |16
2020-03-25 11:01:00 |3
2020-03-25 11:02:00 |4
2020-03-25 11:03:00 |5
2020-03-25 11:04:00 |6
2020-03-25 11:05:00 |7
2020-03-25 11:06:00 |8
2020-03-25 11:07:00 |9
2020-03-25 11:08:00 |0
2020-03-25 11:09:00|9
2020-03-25 11:10:00|0
and so on till 12:59

What I would want to achieve as an output was to capture the 2 hour records run on a specific date

LoadingDatetime | Iphone
2020-03-25 11:00:00 |56
2020-03-25 01:00:00 |10
2020-03-25 03:00:00 |23
2020-03-25 05:00:00 |13

What I have tried:

SELECT 
[LoadingDateTime]
,(select count(Id) from dbo.Products WHERE [LoadingDateTime] >=  DateAdd(hour, -2, DateAdd(hour, DateDiff(hour, '1900-01-01', getdate()), 0)) 
And [LoadingDateTime] < DateAdd(hour, DateDiff(hour, '1900-01-01', getdate()), 0)) LoadCount
,COUNT(convert(int,[Apple_Iphone])) Apple_Iphone
,COUNT(convert(int,[Peripheral_Devices])) Peripheral_Devices
,COUNT(convert(int,[Desktops])) Desktops
,COUNT(convert(int,[Laptops])) Laptops
,COUNT(convert(int, [Desktops]) + convert(int,[Laptops])) IT_Devices



FROM dbo.Products 
WHERE [LoadingDateTime] >=  DateAdd(hour, -2, DateAdd(hour, DateDiff(hour, '1900-01-01', getdate()), 0)) And [LoadingDateTime] < DateAdd(hour, DateDiff(hour, '1900-01-01', getdate()), 0)

GROUP BY [LoadingDateTime]
order by [LoadingDateTime]

Posted 24-Mar-20 19:37pm

sophia kylie taylor

Updated 24-Mar-20 20:54pm

Add a Solution

Comments

Jörgen Andersson 25-Mar-20 2:48am

Is this always going to be the same two hours per day. Or is it two hours from the first occurrence?

sophia kylie taylor 25-Mar-20 5:00am

it is two hours from the first occurence

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-03-24T20:55:00

Instead of GROUP BY the LoadingDateTime, you need to use a separate select to add a TimeGroup column to the data, which merges the data into two hour groups. You can then GROUP BY that column, and use the SUM aggregate function to combine them.

GROUPing by the raw DATETIME value will never give you what you want.

Start by getting the SELECT right and then SELECT from that with the GROUP BY.

Quote:
SQL
(select DateAdd(second,7200, '1900-01-01 00:00:00' + convert(int,(7200 * floor(datediff(HOUR, [LoadingDateTime], '1900-01-01 00:00:00' )/2)))) ReportTimestamp
could you help validate this script? I have been getting an error in this line when running the query

Well yes - what did you expect? '1900-01-01 00:00:00' isn't an integer, so you can't add an integer to it.

Think about what you are trying to do: Group data into two hour "slots".
So start by removing the minutes and seconds from the current DATETIME value, to "group" then into one hour slots.
When you have that working, think about how that can be expanded to two hour slots.
One step at a time here!

[edit]
I'll make it easier - I figured it might have a use, so I knocked up a user defined function to remove the minutes and seconds, and submitted it as a tip: Removing the minutes and seconds from a DATETIME[^]
Two hour windows are pretty trivial when you have that working
[/edit]