none
Grouping By Date RRS feed

  • Question

  • I am grouping a Tabular report by Day and the Details section lists each Value according to Day and Time it occurred.  If I want to find the Max Value per day across the date range, how do I accomplish this?  Using Max in the footer of the Group return the Max of a Value for a particular day.  For example I want to return 185 below, not 90:

    8/1/2008

        8/1/2008 2:30PM      85
        8/1/2008 2:40PM      90
        8/1/2008 2:59PM      10
    Total by Day                185


    8/2/2008

        8/2/2008 9:30AM      85
        8/2/2008 2:00PM      15
    Total by Day                100
     

    Appreciate the help.
    Wednesday, September 3, 2008 8:48 PM

All replies

  • Hello dougIndy,


    IMHO you have to use sum() instead of max().

    EDIT:

    Or do you mean the max from the grouping?

    ....then i would made a new dataset(i cut the time for the example):


    select max (groupsum) as maxGroupSum
    from  (
    select datum , sum( value) as groupsum
     from (
    select '8/1/2008' as datum,      85 as value
    union all
    select '8/1/2008' as datum,      90 as value
    union all
    select '8/1/2008' as datum,      10 as value
    union all
    select '8/2/2008' as datum,      85 as value
    union all
    select '8/2/2008' as datum,      15 as value
    ) as table_1
    group by datum) as tab2




    BanditTreiber.
    Germany, NRW
    • Edited by BanditTreiber Thursday, September 4, 2008 8:07 AM Something forgotten
    Thursday, September 4, 2008 7:15 AM
  • That is a great idea.  The only problem is that I have a complex query on the backside of the report and I am grouping by a SystemID and then by a PointType within the report itself.  I need the Max value per day returned in my Table I have built. 

    Is there any other information I can provide to clarify further?
    Thursday, September 4, 2008 1:35 PM