February 25, 2009 at 6:25 am
I need a get a record count inserted for each 1 hour gap between now and past 24 hours.
my table is like this.
id int,
EnteredTime DateTime.
If I run this query output should give column name according to time executed the query
if I execute query today morning 8.30AM out put should be like this,
8.30AM 7.30AM 6.30 5.30 4.30 3.30....8.30PM
5 7 8 10 10 8 9 6
if I execute query today morning 11.30AM out put should be like this,
11.30AM 10.30AM 9.30 8.30 7.30 6.30....11.30PM
5 7 8 10 0 8 9 6
little confused how to get this... Appreciate ideas and help..
February 25, 2009 at 7:07 am
This way we can get the data sorted on hourly basis.
select
case when datetimecol between getdate() and DATEADD(hh, -1, getdate()) then primarycol end less1,
case when datetimecol between DATEADD(hh, -1, getdate()) and DATEADD(hh, -2, getdate()) then primarycol end less2,
case when datetimecol between DATEADD(hh, -2, getdate()) and DATEADD(hh, -3, getdate()) then primarycol end less3,
case when datetimecol between DATEADD(hh, -3, getdate()) and DATEADD(hh, -4, getdate()) then primarycol end less4,
case when datetimecol between DATEADD(hh, -4, getdate()) and DATEADD(hh, -5, getdate()) then primarycol end less5,
case when datetimecol between DATEADD(hh, -5, getdate()) and DATEADD(hh, -6, getdate()) then primarycol end less6,
case when datetimecol between DATEADD(hh, -6, getdate()) and DATEADD(hh, -7, getdate()) then primarycol end less7,
case when datetimecol between DATEADD(hh, -7, getdate()) and DATEADD(hh, -8, getdate()) then primarycol end less8,
case when datetimecol between DATEADD(hh, -8, getdate()) and DATEADD(hh, -9, getdate()) then primarycol end less9,
case when datetimecol between DATEADD(hh, -9, getdate()) and DATEADD(hh, -10, getdate()) then primarycol end less10,
case when datetimecol between DATEADD(hh, -10, getdate()) and DATEADD(hh, -11, getdate()) then primarycol end less11
from tableA
February 25, 2009 at 7:28 am
Okay this is similar to Purushotham's suggestion:
DECLARE @table TABLE (id INT IDENTITY(1,1), EnteredTime DATETIME)
DECLARE @StartDate DATETIME
-- doing this so I have a consistent date with which to work
SET @StartDate = GETDATE()
-- create test data
INSERT INTO @table (
EnteredTime
)
SELECT TOP 10000
DATEADD(minute, -ROW_NUMBER() OVER(ORDER BY A.OBJECT_ID), @StartDate)
FROM
sys.all_objects A CROSS JOIN
sys.all_objects B
-- remove some rows so that not all hours have 60 rows
DELETE FROM @table WHERE id % 3 = 2 AND DATEPART(Hour, EnteredTime) % 3 = 1
-- numbers CTE (you really want a numbers/tally table)
;WITH cteNumbers AS
(
SELECT TOP 24
ROW_NUMBER() OVER(ORDER BY OBJECT_ID) as N
FROM
sys.all_objects
),
-- hours cte so I have a start and end time
cteHours AS
(
SELECT
DATEADD(hour, -N, @StartDate) AS hour_start,
DATEADD(hour, -N + 1, @StartDate) AS hour_end
FROM
cteNumbers
)
SELECT
/* if the time is between the hour_start and hour end then add 1 else
ADD 0 */
SUM(CASE
WHEN EnteredTime >= C.hour_start AND EnteredTime < C.hour_end THEN 1
ELSE 0
END) AS ROWS,
hour_start,
hour_end
FROM
@table T CROSS JOIN
cteHours C
WHERE
EnteredTime >= DATEADD(Hour, -24, @StartDate)
GROUP BY
hour_start,
hour_end
Oh and had you posted some test data as I had then I probably would have had the first response.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply