Grouping / Tying multiple queries together

  • Either I have been looking at this too long, or it is just not making sense to me...

    I have function that creates #tblTemp.  From there, I have written 5 queries that all retrieve 3 records back (grouped by category called Class).  The queries are as follows:

    1 - Class, Sum(Volume) as 'Monthly Volume' ... group by class

    2 - Class, Sum(Volume) as 'Business Volume' from #tblTemp Where Datepart(dw,vDate) NOT IN (1,7) group by class

    3 - Class, Average(Volume) as 'Average' ... group by class

    4 - Class, vDate, Volume from (Select Class, Max(Volume) from #tblTemp group by class) [Not exactly coded correctly]

    --Retrieved the date, class, and total volume by class

    What I would like to see is one query that would return the 3 records by list it in columns... for example

       Class, Monthly Volume, Business Volume, Average, Max Volume and Date occurred

    1

    2

    3


    "Life without progression is entropy"
    Sam Jaynes

  • SELECT Class,

    Sum(Volume) as 'Monthly Volume',

    Sum(case when Datepart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'Business Volume' ,

    Average(Volume) as 'Average',

    MAX( vDate) vDate, 

    Max(Volume) Volume

    from #tblTemp

    group by class

    _____________
    Code for TallyGenerator

  • Sleep does a wonder...  one question/comment.   I need to intergate the date of where the Max(Volume) occured.  The stand-alone query that works right now is:

    Select Class, vDate, Volume FROM #tblTemp Where Volume IN (Select MAX(Volume) From #tblTemp GROUP BY Class)

    I really just need the date returned for this column in the aforementioned solution you provided.


    "Life without progression is entropy"
    Sam Jaynes

  • Opening this up to the forurm... I need insert the following query as a column:

    Select Class, vDate 'Date of Peak', Volume

    FROM #tblTemp

    Where Volume in (Select Max(Volume) From #tblTemp group by Class)

    Into the following T-SQL statement:

    Select Class, SUM(Volume) 'Total_ALL', MAX(Volume) as 'Peak',

    SUM(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Total',

    AVG(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Average'

    FROM #tblTemp

    GROUP BY Class

    As noted, the T-SQL statement will return 3 records for the three classes.  The problem is tying the date that the peak volume occured for the class, and tying it to the Class in the SELECT statement.


    "Life without progression is entropy"
    Sam Jaynes

  • Your challenge here is that you must aggregate the date since you are using a GROUP BY clause.  Take a look at your first query,

    Select Class, vDate 'Date of Peak', Volume

    FROM #tblTemp

    Where Volume in (Select Max(Volume) From #tblTemp group by Class)

    In the event that your peak volume occurs more than once, this query will return more than one row.  In order to fold this into your second SELECT, you would have to aggregate the vDate also and have your query return the 'Most Recent' date of peak volume for each Class. 

    Select Class,

        SUM(Volume) 'Total_ALL',

        MAX(Volume) as 'Peak',

        MAX(vDate) as 'Date of Peak',

        SUM(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Total',

        AVG(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Average'

    FROM #tblTemp

    GROUP BY Class

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Thank you for your response... I am not sure how this would work, as it will just return the last date in the given month, not the date that contained the largest volume... sorry, any suggestions.


    "Life without progression is entropy"
    Sam Jaynes

  • This would be much easier if you could post the table DDL for your temp table and some sample data, but here goes a shot anyway....

    Select Class,

        SUM(Volume) 'Total_ALL',

        MAX(Volume) as 'Peak',

        t3.vDate as 'Date of Peak',

        SUM(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Total',

        AVG(CASE WHEN DatePart(dw,vDate) NOT IN (1,7) then Volume else NULL end) as 'BD_Average'

    FROM #tblTemp t

        INNER JOIN (Select t1.class, vDate    

                    FROM #tblTemp t1

                        INNER JOIN (Select Class, Max(Volume) as Volume From #tblTemp group by Class) t2

                        ON t1.Volume = t2.Volume and t1.Class = t2.class) t3

        ON t.class = t3.class

    GROUP BY Class

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

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