get a specific time window across the night between two days

  • 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

  • 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
  • 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

  • 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

    FROM snapshots.performance_counters pc

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

    INNER JOIN Dates2 d ON CAST(pc.collection_time AS DATETIME) BETWEEN d.startdate AND d.enddate

    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')

    GROUP BY d.stardate, pc.performance_counter_name

    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
  • oh yeh CTE, did not think about it. Thanks is pretty much what i need. Thanks a lot

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

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