Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 24-Mar-20 20:54pm
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?
   
it is two hours from the first occurence

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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:
(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]
   
v3
Comments
   
(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
OriginalGriff 5 days ago
   
Answer updated
OriginalGriff 5 days ago
   
Answer updated again.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100