Get the Maximum value for a Row

  • htilburgs

    Ten Centuries

    Points: 1057

    Problem description:

    I've a report (report1.rdl) that displays the number that a specific application is used.

    In the last column I like to have the maximum number that exists in the Row (see result.jpg).

    I've tried several things, but no luck.

    Can someone help me with this?

    (I've included sample information to reproduce)


    Table “Applications”

    CREATE TABLE [dbo].[ICT_Apps](

    [Firstname] [varchar](100) NOT NULL,

    [Lastname] [varchar](255) NOT NULL,

    [Application] [varchar](255) NOT NULL,

    [Daynr] [varchar](2) NOT NULL,

    [Monthnr] [varchar](2) NOT NULL,

    [Yearnr] [varchar](4) NOT NULL,

    [Weeknr] [varchar](2) NOT NULL

    ) ON [PRIMARY]

    Data inside table “Applications”

    INSERT INTO ICT_Apps

    select 'Frits','Philips','Office2003','1','11','2009','45' union all

    select 'Frank','Jansen','Office2003','1','11','2009','45' union all

    select 'Peter','Smits','Office2003','1','11','2009','45' union all

    select 'John','Doe','Office2003','1','11','2009','45' union all

    select 'Frank','Jansen','Telebanking','1','11','2009','45' union all

    select 'Peter','Smits','Telebanking','1','11','2009','45' union all

    select 'Frits','Philips','Office2003','2','11','2009','45' union all

    select 'Frank','Jansen','Office2003','2','11','2009','45' union all

    select 'Peter','Smits','Office2003','2','11','2009','45' union all

    select 'Frits','Philips','Telebanking','2','11','2009','45' union all

    select 'Frank','Jansen','Telebanking','2','11','2009','45' union all

    select 'Peter','Smits','Telebanking','2','11','2009','45' union all

    select 'Frits','Philips','Office2003','3','11','2009','45' union all

    select 'Frank','Jansen','Office2003','3','11','2009','45' union all

    select 'Peter','Smits','Office2003','3','11','2009','45' union all

    select 'Frank','Jansen','Telebanking','3','11','2009','45' union all

    select 'Peter','Smits','Telebanking','3','11','2009','45'

    Dataset “Application_List”

    select

    (Firstname+' '+Lastname) as Gebruiker,

    Application,

    Daynr,

    Monthnr,

    Yearnr,

    Weeknr

    from

    ICT_Apps

    Included:

    - Report (Report1.rdl.txt --> Rename to Report1.rdl)

    - Wannahave Result (Result.jpg)

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Try this:

    SELECT d.*, s.MaxUsage

    FROM (

    SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6

    FROM

    (SELECT [Application], [Lastname], Daynr

    FROM #ICT_Apps) p

    PIVOT (

    COUNT ([Lastname])

    FOR Daynr IN

    ( [1], [2], [3], [4], [5], [6] )

    ) AS pvt

    ) d

    LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage

    FROM (SELECT [Application], COUNT(*) AS Usage

    FROM #ICT_Apps

    GROUP BY [Application], Daynr) x

    GROUP BY x.[Application]

    ) s ON s.[Application] = d.[Application]

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • htilburgs

    Ten Centuries

    Points: 1057

    First of all thanx for the reply!

    Will this also work when the table "ICT_Apps" is automaticly filled each day and the results should be

    like "where monthnr=month(getdate())", because in this example you define each day...?

  • ChrisM@Work

    SSC Guru

    Points: 186120

    The pivot operator requires the new output columns to be described and mapped, but if they are not known at run-time the dynamic sql can be used to create the list. See here for one of many examples.

    Apply the month filter wherever it yields best performance:

    SELECT d.*, s.MaxUsage

    FROM (

    SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6

    FROM

    (SELECT [Application], [Lastname], Daynr

    FROM #ICT_Apps WHERE [Monthnr] = 11) p

    PIVOT (

    COUNT ([Lastname])

    FOR Daynr IN

    ( [1], [2], [3], [4], [5], [6] )

    ) AS pvt

    ) d

    LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage

    FROM (SELECT [Application], COUNT(*) AS Usage

    FROM #ICT_Apps WHERE [Monthnr] = 11

    GROUP BY [Application], Daynr) x

    GROUP BY x.[Application]

    ) s ON s.[Application] = d.[Application]

    ;WITH CTE_ICT_Apps AS

    (SELECT [Application], [Lastname], Daynr

    FROM #ICT_Apps WHERE [Monthnr] = 11)

    SELECT d.*, s.MaxUsage

    FROM (

    SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6

    FROM

    (SELECT [Application], [Lastname], Daynr

    FROM CTE_ICT_Apps) p

    PIVOT (

    COUNT ([Lastname])

    FOR Daynr IN

    ( [1], [2], [3], [4], [5], [6] )

    ) AS pvt

    ) d

    LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage

    FROM (SELECT [Application], COUNT(*) AS Usage

    FROM CTE_ICT_Apps

    GROUP BY [Application], Daynr) x

    GROUP BY x.[Application]

    ) s ON s.[Application] = d.[Application]

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • htilburgs

    Ten Centuries

    Points: 1057

    Chris,

    I used the query (2nd) with succes. GREAT RESULTS!

    Last question:

    I'm trying to get the columns Appliction, Daynr, MaxUsage but I'm stuck.

    I'm only getting Application and MaxUsage like in your example.

    I think it shouldn't be hard, but no luck.

    Can you help?

    Edit:

    What I mean is that:

    d.*, s.MaxUsage

    FROM (

    SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6

    FROM .......

    lookes like:

    d.*, s.MaxUsage

    FROM (

    SELECT [Application], Daynr

    FROM .........

  • htilburgs

    Ten Centuries

    Points: 1057

    I've tried to work out the "Fetch" command. See my result, but I got an error

    Msg 102, Level 15, State 1, Line 44

    Incorrect syntax near '@Dagen'.

    Msg 102, Level 15, State 1, Line 53

    Incorrect syntax near 's'.

    Again, a little help will be appreciated....;-)

    -- Declare Variables for the FETCH

    Declare @DagID INT

    Declare @Dagen Varchar(8000)

    Set @DagID = ''

    Set @Dagen = ''

    Declare max_cursor cursor for

    Select

    [Application], [Lastname], [Daynr]

    From

    ICT_Apps

    Where [Monthnr] = 11

    --Read data into @Dagen

    Open max_cursor

    Fetch next from max_cursor into @DagID

    While (@@FETCH_STATUS <> -1)

    Begin

    set @Dagen = '[' + @DagID + '],'

    Fetch next from max_cursor into @DagID

    End

    Close max_cursor

    Deallocate max_cursor

    Set @Dagen = LEFT(@Dagen,len(@Dagen)-1) -- Remove last ,(comma)

    ;WITH CTE_ICT_Apps AS

    (SELECT [Application], [Lastname], Daynr

    FROM #ICT_Apps WHERE [Monthnr] = 11)

    SELECT d.*, s.MaxUsage

    FROM (

    SELECT [Application], @Dagen

    FROM

    (SELECT [Application], [Lastname], Daynr

    FROM CTE_ICT_Apps) p

    PIVOT (

    COUNT ([Lastname])

    FOR Dag IN

    (@Dagen)

    ) AS pvt

    ) d

    LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage

    FROM (SELECT [Application], COUNT(*) AS Usage

    FROM CTE_ICT_Apps

    GROUP BY [Application], Daynr) x

    GROUP BY x.[Application]

    ) s ON s.[Application] = d.[Application]

  • ChrisM@Work

    SSC Guru

    Points: 186120

    No problem - read this, it will show you how it's done.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • htilburgs

    Ten Centuries

    Points: 1057

    I'm trying to understand (still beginner sql), but still getting strange results:

    EXEC dynamic_pivot

    'SELECT i.Application, i.daynr

    FROM ict_apps i',

    'application',

    'max(daynr)'

    Horizontal I've the application (OK), but vertical I like the days with a final column with the max from the previous columns. I cannot get it working. Still trying.....

    No luck, I can't get the results I like to have.

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Is this different to your original output?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • htilburgs

    Ten Centuries

    Points: 1057

    Yes, it is.

    My original output (from the first answer of Chris) was:

    Application 1 2 3 4 5 6 MaxUsage

    Office2003 4 3 3 0 0 0 4

    Telebanking 2 3 2 0 0 0 3

    With the dynamic pivot it is:

    Application Office2003 Telebanking

    Office2003 3 NULL

    Telebanking NULL 3

  • ChrisM@Work

    SSC Guru

    Points: 186120

    That's no good is it! How do you want it to look?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • htilburgs

    Ten Centuries

    Points: 1057

    Like the results from the first answer.

    Application 1 2 3 4 5 6.......31 MaxUsage

    Office2003 4 3 3 0 0 0....... x 4

    Telebanking 2 3 2 0 0 0....... x 3

  • ChrisM@Work

    SSC Guru

    Points: 186120

    htilburgs (12/8/2009)


    Like the results from the first answer.

    Application 1 2 3 4 5 6.......31 MaxUsage

    Office2003 4 3 3 0 0 0....... x 4

    Telebanking 2 3 2 0 0 0....... x 3

    Erm...this is the result from the first query - what do you want to change? Is it simply a case of getting this result by using Madhivanan's code?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • htilburgs

    Ten Centuries

    Points: 1057

    What do you mean?

    I just expand [1], [2] until [31] (for days of month)???

    Because this was how we started, but I liked the [1] till [31] to be dynamic.

    Or do you mean something else?

  • ChrisM@Work

    SSC Guru

    Points: 186120

    htilburgs (12/8/2009)


    What do you mean?

    I just expand [1], [2] until [31] (for days of month)???

    Because this was how we started, but I liked the [1] till [31] to be dynamic.

    Or do you mean something else?

    To give you just the day columns which already exist in the data?

    Jeff Moden's Dynamic Cross Tab paper[/url] explains how to do this better than anyone else can...

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 15 posts - 1 through 15 (of 16 total)

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