get a specific time window across the night between two days

Question

Post reply

get a specific time window across the night between two days

sugnu iu

SSCommitted

Points: 1594
More actions
September 30, 2013 at 5:05 pm

#299306

Hi all
I need some help me to get me in the right direction.
I have collected some sql stats into a tables across a week.
I want to see those stats at the time a nightly job run. so from
day1 at 20pm to day2 at 11am.
day2 at 20pm to day3 at 11am
day3 at 20pm to day 4 at 11am
and so on...
so of the entire week i am only intrested to see that time window.
What SQL query allow me to do that?
I can use between day1 for time more than 20pm and day7 until 11am? this way i get all other timewindows i don't want.
many thanks

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Does this example helps you?

WITH Time_Windows(StartTime, EndTime) AS(

SELECT DATEADD( HH, 20, DATEADD(DD, DATEDIFF(dd, 0, GETDATE()) - 7, 0)),

DATEADD( HH, 11, DATEADD(DD, DATEDIFF(dd, 0, GETDATE()) - 6, 0))

UNION ALL

SELECT DATEADD( HH, 20, DATEADD(DD, DATEDIFF(dd, 0, GETDATE()) - 6, 0)),

DATEADD( HH, 11, DATEADD(DD, DATEDIFF(dd, 0, GETDATE()) - 5, 0))

)

SELECT *

FROM Time_Windows

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sugnu iu SSCommitted Points: 1594 More actions · Answer 2

OK, thanks.

I came up with this, coming from my MDW database. is there any other more efficent day to do it?

when posting here i loose the formatting sorry. how did you post your code?

DECLARE dates CURSOR

FOR

SELECT DISTINCT

CAST(pc.collection_time AS DATE)

FROM snapshots.performance_counters pc

INNER JOIN core.snapshots s ON ( s.snapshot_id = pc.snapshot_id )

WHERE s.instance_name = 'server'

AND pc.performance_counter_name = '% Processor Time'

AND ISNULL(pc.performance_instance_name, N'') IN ( '0', '1', '2',

'3', '4', '5',

'6', '7' )

AND CAST(pc.collection_time AS DATE) > '2013-09-10'

ORDER BY 1 DESC

DECLARE @enddate DATE

DECLARE @enddatetime VARCHAR(30)

--datetime

OPEN dates

FETCH NEXT FROM dates INTO @enddate

WHILE @@FETCH_STATUS = 0

BEGIN

SET @enddatetime = CAST(@enddate AS VARCHAR(12)) + ' 11:29:59' --DATEADD (minute, 1 * (840), '2013-09-30 11:29:59')

SELECT pc.performance_counter_name AS [counter] ,

AVG(pc.formatted_value) AS formatted_value

FROM snapshots.performance_counters pc

INNER JOIN core.snapshots s ON ( s.snapshot_id = pc.snapshot_id )

WHERE s.instance_name = 'server'

AND pc.performance_counter_name = '% Processor Time'

AND ISNULL(pc.performance_instance_name, N'') IN ( '0', '1',

'2', '3', '4',

'5', '6', '7' )

AND CAST(pc.collection_time AS DATETIME) BETWEEN DATEADD(minute,

-1 * ( 900 ),

CAST(@enddatetime AS DATETIME))

AND CAST(@enddatetime AS DATETIME)

GROUP BY pc.performance_counter_name

FETCH NEXT FROM dates INTO @enddate

END

CLOSE dates

DEALLOCATE dates

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'm giving a shot in the dark here, based on many assumptions. Please post DDL and sample data as explained on the article linked in my signature for better help.

WITH Dates

AS (

SELECT DISTINCT DATEADD( ms, -3, DATEADD( dd, DATEDIFF( dd, 0, pc.collection_time), 0)) enddate

FROM snapshots.performance_counters pc

INNER JOIN core.snapshots s ON (s.snapshot_id = pc.snapshot_id)

WHERE s.instance_name = 'server'

AND pc.performance_counter_name = '% Processor Time'

AND ISNULL(pc.performance_instance_name, N'') IN ('0','1','2','3','4','5','6','7')

AND CAST(pc.collection_time AS DATE) > '2013-09-10'

),

Dates2(

SELECT DATEADD(minute, -900, enddate) startdate,

enddate

FROM Dates

)

SELECT d.startdate AS MyDate,

pc.performance_counter_name AS [counter],

AVG(pc.formatted_value) AS formatted_value