SQL Server Performance

Max Date of Max QTime

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Apr 2, 2010.

  1. sqlderby Member

    Hi
    I need to know can I find the time of maximum queue time from a table data. This data group by half hour time period.
    HalfHourDateTime DateTime DNIS QTime
    2010-04-01 02:30:00 2010-04-01 02:51:42 15403 10
    2010-04-01 02:30:00 2010-04-01 02:52:14 15403 16
    The next T-SQL is grouping the data of this table group on HalfHourDateTime and DNIS. we can select max Qtime with 'MAX' but how select DateTime for this max QTime within same T-SQL.
    Thanks for help

    sqlderby, Apr 2, 2010
    #2

  2. Adriaan New Member

    Use a correlated subquery in the WHERE clause to find the maximum grouping value, and just use MAX in the main query:
    SELECT MAX(t1.QTime)
    FROM mytable t1
    WHERE t1.HalfHourDateTime = (SELECT MAX(t2.HalfHourDateTime) FROM mytable t2)
    [EDIT - That's not a correlated subquery, but just a plain old subquery.]
    Adriaan, Apr 2, 2010
    #3

  3. sqlderby Member

    No...I am looking for date and time (from datetime column) for Max(Qtime) rather Max(Qtime) and to be with same T-SQL.
    Thanks
    sqlderby, Apr 7, 2010
    #4

  4. FrankKalis Moderator

    Not sure i understand you correctly, but it could be something like:
    SELECT *
    FROM table T1
    JOIN (SELECT DNIS, MAX(QTime) AS QTime FROM Table GROUP BY DNIS) X ON T1.DNIS = X.DNIS AND T1.QTime = X.QTime
    FrankKalis, Apr 7, 2010
    #5

  5. Madhivanan Moderator

    or
    select ,HalfHourDateTime,[DateTime],DNIS,QTime from
    (
    select *,ROW_NUMBER() over (partition by QNIS order by QTime desc) as sno from table
    ) as t
    where sno=1
    Madhivanan, Apr 8, 2010
    #6

  6. FrankKalis Moderator

    [quote user="Madhivanan"]
    or
    select ,HalfHourDateTime,[DateTime],DNIS,QTime from
    (
    select *,ROW_NUMBER() over (partition by QNIS order by QTime desc) as sno from table
    ) as t
    where sno=1
    [/quote]
    You'll find that the classic aggregate MAX() approach outperforms the ROW_NUMBER() approach by far when working on large tables. Can't remember exactly, but we once tested this against a +200 million row table, and the difference was REALLY huge.
    FrankKalis, Apr 8, 2010
    #7

  7. Madhivanan Moderator

    [quote user="FrankKalis"]
    [quote user="Madhivanan"]
    or
    select ,HalfHourDateTime,[DateTime],DNIS,QTime from
    (
    select *,ROW_NUMBER() over (partition by QNIS order by QTime desc) as sno from table
    ) as t
    where sno=1
    [/quote]
    You'll find that the classic aggregate MAX() approach outperforms the ROW_NUMBER() approach by far when working on large tables. Can't remember exactly, but we once tested this against a +200 million row table, and the difference was REALLY huge.
    [/quote]
    Thanks
    I will test this if I have enough time [:)]
    Madhivanan, Apr 8, 2010
    #8

  8. Madhivanan Moderator

    Madhivanan, Apr 8, 2010
    #9

  9. sqlderby Member

    Hi All
    Here it is the T-SQL....
    SELECT @Hnd.RHalfHour, @Abnd.Variable3,
    COUNT(@Abnd.RCKey) as 'TrueAbandons', SUM(@Abnd.LocalQTime) as 'LocalQTime',
    MAX(@Abnd.LocalQTime) as 'MaxLQTimeAbdn',
    FROM
    @Hnd
    RIGHT OUTER JOIN
    @Abnd
    ON @Hnd.NewRouterCallKey = @Abnd.NewRouterCallKey
    WHERE @Hnd.NewRouterCallKey is null
    GROUP BY @Abnd.Variable3 , @Abnd.RHalfHour
    Now, need to include in same T-SQL the date for 'MaxLQTimeAbdn'...I hope that this will explain the problem...as main draw back is it is group by half hourly datetime column rather individual datetime value otherwise no problem...OR any other way is appreciated...
    Thanks in Advance
    sqlderby, Apr 15, 2010
    #10

  10. sqlderby Member

    Any one help here...
    sqlderby, Apr 16, 2010
    #11

  11. Adriaan New Member

    Not sure what SUM(@Abnd.LocalQTime) is supposed to represent ...
    Anyway, you can use the technique that Frank posted on the 7th, with the aggregate query as a derived table.
    Adriaan, Apr 16, 2010
    #12

  12. sqlderby Member

    It is sum of QTime for a day... similarly to Max value of Qtime in a day. I need to display date and time of Max Value of QTime in the same day with same T-SQL... still confuse...what could be the best way in term of performance point of view.
    Thanks in Advance
    sqlderby, Apr 18, 2010
    #13

  13. Adriaan New Member

    Like I said, use the technique that Frank Kalis posted on April 7.
    Adriaan, Apr 19, 2010
    #14

  14. venkatesanj@hcl.in New Member

    Hope the below query will be useful,
    DROP
    TABLE venkat_table CREATE
    TABLE venkat_table(id INT,date DATETIME ,HalfHourdate DATETIME)INSERT
    INTO venkat_table VALUES (1,'3/10/2010','1/20/2010')INSERT
    INTO venkat_table VALUES (2,'3/10/2009','2/20/2009')INSERT
    INTO venkat_table VALUES (1,'6/10/2010','3/20/2010')INSERT
    INTO venkat_table VALUES (4,'8/10/2010','4/20/2010')INSERT
    INTO venkat_table VALUES (5,'10/10/2010','4/20/2010')SELECT * FROM venkat_table SELECT * FROM (SELECT
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY HalfHourdate ) AS R,ID
    ,HalfHourdate,date AS HALFHOUR FROM venkat_table)
    T WHERE T.R=1
    Cheers,
    Venkatesan Prabu .J
    http://venkattechnicalblog.blogspot.com/
    venkatesanj@hcl.in, Apr 20, 2010
    #15

Share This Page