SQL Server Performance

how to create dynamic column aliases

Discussion in 'General Developer Questions' started by lsamp, Jul 19, 2004.

  1. lsamp New Member

    I am trying to create a query that computes procedure run time statistics for the last 7 days. i have no problems createing the query, but how can i have the column aliases to bear the current date?

    Ideal results:
    property process 7/12/04 7/13/04 7/14/04 7/15/04 7/16/04 7/17/04 7/18/04

    declare @start smalldatetime
    select @start = convert(varchar(10), dateadd(dd,-4, getdate()),101)

    declare @end smalldatetime
    select @end = convert(varchar(10), getdate(),101)

    declare @table_1 Table (
    Property varchar(6)
    ,process varchar(50)
    ,steps_order int
    ,business_date smalldatetime
    ,run_time int
    )
    declare @table_2 Table (
    Property varchar(6)
    ,process varchar(50)
    ,steps_order int
    ,business_date smalldatetime
    ,run_time int
    )
    declare @table_3 Table (
    Property varchar(6)
    ,process varchar(50)
    ,steps_order int
    ,business_date smalldatetime
    ,run_time int
    )
    declare @table_4 Table (
    Property varchar(6)
    ,process varchar(50)
    ,steps_order int
    ,business_date smalldatetime
    ,run_time int
    )
    declare @table_5 Table (
    Property varchar(6)
    ,process varchar(50)
    ,steps_order int
    ,business_date smalldatetime
    ,run_time int
    )

    insert @table_1
    select property, process, steps_order, business_date, datediff(ms, start_time, end_time) from night_audit where business_date = @end
    AND Property not like 'TR%'
    order by property, process

    insert @table_2
    select property, process, steps_order, business_date, datediff(ms, start_time, end_time) from night_audit where business_date = dateadd(dd, -3, @start)
    AND Property not like 'TR%'
    order by property, process

    insert @table_3
    select property, process, steps_order, business_date, datediff(ms, start_time, end_time) from night_audit where business_date = dateadd(dd, -2, @start)
    AND Property not like 'TR%'
    order by property, process

    insert @table_4
    select property, process, steps_order, business_date, datediff(ms, start_time, end_time) from night_audit where business_date = dateadd(dd, -1, @start)
    AND Property not like 'TR%'
    order by property, process


    insert @table_5
    select property, process, steps_order, business_date, datediff(ms, start_time, end_time) from night_audit where business_date = @start
    AND Property not like 'TR%'
    order by property, process

    select a.property,
    a.process
    ,a.steps_order
    ,e.run_time as [@start]
    ,d.run_time as [dateadd(dd, -1, @start)]
    ,c.run_time as [dateadd(dd, -2, @start)]
    ,b.run_time as [dateadd(dd, -3, @start)]
    ,a.run_time as [dateadd(dd, 0, @end)]
    From @table_1 a JOIN @table_2 b ON a.Process = b.Process AND a.property = b.property
    JOIN @table_3 c ON a.Process = c.Process AND a.property = c.property
    JOIN @table_4 d ON a.Process = d.Process AND a.property = d.property
    JOIN @table_5 e ON a.Process = e.Process AND a.property = e.property
    order by a.property, a.run_time desc, a.process

Share This Page