Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wang
Starting Member
48 Posts |
 Posted - 2007-08-03 : 08:05:20
|
Hi all.I have what I thought would be a simple problem, maybe my approach is all wrong!Say you have customers, registered in a year/month group. They can be active in another year/month group.So we get a simple table with registration year/month down the side and activity along the top. This shows "of all the people p who register in month m, how many are active in month a?"So assuming that all people registered in month m are also active in month m, the max(activity) for that slice (ie the max value for any active month for the registered month) should be the value if we collapse the activemonth group to activeyear.Let me try to draw it :DRegistered/Active 2006 2007 ... Jan Feb Mar ... Jan Feb Mar ... ...2 Jan 20 10 10 20 . 5 3 1 5 20 0 Feb 0 25 15 25 . 8 5 5 8 250 Mar 0 0 40 40 . 11 1 6 11 406 ... 20 35 65 65 . 24 9 12 24 852 Jan 0 0 0 0 . 50 34 44 34 50 0 Feb 0 0 0 0 . 0 45 40 41 457 ... 20 35 65 65 . 50 79 44 75 95..... 20 35 65 65 . 74 88 56 99 180 I think. The ...'s represent the grouping band total. |
|
|
Wang
Starting Member
48 Posts |
Posted - 2007-08-03 : 09:13:28
|
| [code]Registered/Active 2006 2007 Tot Jan Feb Mar ... Jan Feb Mar ... ...2 Jan 20 10 10 20 . 5 3 1 5 20 <-- Max of Row0 Feb 0 25 15 25 . 8 5 5 8 250 Mar 0 0 40 40 . 11 1 6 11 406 ... 20 35 65 65 . 24 9 12 24 85 <-- Sum of Max - (ie sum of the lower level group column) ^ Sum of Column2 Jan 0 0 0 0 . 50 34 44 34 50 0 Feb 0 0 0 0 . 0 45 40 41 457 ... 20 35 65 65 . 50 79 44 75 95Tot.. 20 35 65 65 . 74 88 56 99 180 ^ Sum[/code] |
 |
|
|
|
|
|
RSsk5 Aggregating Groups in Matrix