Hi,
The following is some realistic data, showing the Items, Groups, and GroupSummary. Hopefully, this will make it easier to see how the source data needs to get transformed into the result set.
Items and monthly values:
[pre]
Code:
Items Jan-2012 Feb-2012 Mar-2012 Apr-2012 May-2012 Jun-2012
Item1 - 15 10 12 12 1
Item2 67 100 91 70 102 92
Item3 156 222 90 6 15 9
Item4 - - - - 12 4
Item5 161 223 176 158 206 107
Item6 - 73 147 101 136 123
Item7 14 20 16 14 18 12
Total 397 652 529 360 501 347
GroupMembership and Effective Dates for the membership:
[pre][code]GroupMember Membership Effective Date
Item1 Group6 Jan-2011
Item2 Group3 Jan-2011
Item3 Group5 Jan-2011
Item3 Group4 Apr-2012
Item4 Group4 May-2012
Item5 Group4 Jan-2011
Item6 Group5 Feb-2012
Item7 Group7 Jan-2011
[/pre]
The effective date is the date when a group membership takes effect, overriding any previous group memberships. The group membership would be valid until overridden. For example, Item7 has been a member of Group7 since Jan 2011, and is in effect today since it has not been overridden. (In Excel, I have these dates actually set as the 1st day of each month. However, the usage is consistent everywhere, enabling straightforward comparison between two date values.)
The following is the desired Result set showing the summary by group by month. (This is the table I have been struggling with.)
Summary Jan-2012 Feb-2012 Mar-2012 Apr-2012 May-2012 Jun-2012
Group1 - - - - - -
Group2 - 12 5 - - -
Group3 67 100 91 70 102 92
Group4 161 223 176 164 233 120
Group5 156 295 237 101 136 123
Group6 - 15 10 12 12 1
Group7 14 20 16 14 18 12
Total 397 664 534 360 501 347[/code][/pre]
I have included a total row, to show that the totals from the first table will need to match the totals in the result set.
(The "-" shows zero values. I do not have text values in my data.)
Please let me know if I can clarify anything.
thanks,
Sajan.