Post reply

Grouping multiple fields to get top 10 for month then using that data to get previous months data

  • October 13, 2010 at 12:43 am

    #227926

    Hi

    The database I’m querying has 3 columns which I need to group together to get a count of them. The fields within these columns can be identical but when the 3 are grouped together they are unique.

    For example:

    Month Col 1 Col2 Col3

    201009 Field A Field B Field C

    201009 Field A Field B Field D

    201009 Field A Field E Field C

    201009 Field B Field B Field F

    201009 Field B Field G Field G

    201009 Field C Field B Field F

    I need to get a count of the number of times these fields appear uniquely each month and produce a report showing the top 10. So far, pretty easy, I simply group the 3 fields and the month (which conveniently is in the format above) and put in a topN table.

    The problem is I also need to show the totals of those top 10 grouped items for the previous 11 months in the same table (now a matrix). The top 10 can change each month. All the data is in the same table.

    Matrix to look like:

    Col 1 Col2 Col3 201009 201008 201007 etc

    Field B Field B Field F 20 19 25

    Field A Field E Field C 18 14 22

    Field A Field B Field C 17 16 14

    etc

    If I can get sql to return the top 10 grouping for the current month along with each group’s relevant data for the previous months in one column, reporting services will do the matrix formatting. Alternatively if SQL returns the info into unique columns I’ll use a table. I’ve tried both but can’t get the data in the right format.

    Many thanks

  • October 13, 2010 at 2:00 am

    #1236014

    Something with Pivot maybe. One downside would be to get it to work when the month change. Probably some dynamic SQL is needed at that point. A small example.

    declare @raw table (YM int, C1 int, C2 int, C3 int)

    insert into @raw (YM, C1, C2, C3)

    select 201008, 1, 1, 1 union all select 201009, 1, 1, 1 union all

    select 201008, 1, 1, 2 union all select 201008, 1, 1, 2 union all

    select 201008, 1, 1, 2 union all select 201009, 1, 1, 2 union all

    select 201009, 1, 1, 2 union all select 201009, 1, 1, 2 union all

    select 201009, 1, 1, 2 union all select 201008, 1, 1, 3 union all

    select 201008, 1, 1, 3 union all select 201009, 1, 1, 3 union all

    select 201007, 1, 1, 4 union all

    select 201008, 1, 1, 4 union all select 201008, 1, 1, 4 union all

    select 201009, 1, 1, 4 union all select 201009, 1, 1, 4 union all

    select 201009, 1, 1, 4 union all select 201009, 1, 1, 4

    ;with cte as (select top 2 c1, c2, c3, ym from @raw

    where ym = 201009 group by c1, c2, c3, ym

    order by count(*) desc),

    cte2 as (select r.c1, r.c2, r.c3, r.ym

    from @raw r join cte c on r.c1 = c.c1 and r.c2 = c.c2 and r.c3 = c.c3)

    select * from cte2

    pivot (count(ym) for ym in ([201007], [201008], [201009])) as pvt

    /T

Viewing 2 posts - 1 through 1 (of 1 total)

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