GROUP BY For Heirarchical Data

I have a table:

ID int, AreaID int, SaleAmount float, SaleDate smalldatetime

AreaID is an foreign key to a second table of heirarchical area data:

ID int, ParentId int, AreaName nvarchar.

The heirarchy varies in depth for different parts of the country - sometimes 3 levels deep, sometimes up to six.

My problem is this: how do a construct a sproc that will allow me to pass in any area id and then return one or more result sets with all the child data of that area id grouped (to allow a SUM() of the sales data and a MIN() of the dates) by EACH LEVEL of the heirarchy?

Sales data only exists in the bottom one or two levels.


I've tried looking at CTEs but can't seem to crack the problem. I got close with a WHILE loop, but that kept grouping the data at the same level.....

Thanks

John

Expected output from this data if, say, I passed in 1 (the National level)

'National', 47.81, 20-Feb-08
'Super Region A', 37.81, 21-Feb-08
'Region 1', 16.81, 21-Feb-08
'Region 2', 21.00, 22-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08
'Sub Region 3', 7.70, 23-Feb-08
'Sub Region 4', 12.30, 22-Feb-08

and so on. Note that it doesn't have to be one table - each level could come back as a separate table (in fact, that might be helpful).

If I passed in 3 I'd get

'Region 1', 16.81, 21-Feb-08
'Sub Region 1', 8.81, 21-Feb-08
'Sub Region 2', 8.00, 22-Feb-08


(NB these results are just typed in, so forgive typos please, and the sums and min dates are not necissarily the same as from the sample
data below)

CREATE TABLE [dbo].[SalesData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[AreaID] [int] NULL,
[SalesDate] [smalldatetime] NULL ,
[SalesAmount] [float] NULL
)


CREATE TABLE [dbo].[SalesAreas](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL ,
[Name] [nvarchar](64) NOT NULL
)

DECLARE @ID int
DECLARE @ID2 int
DECLARE @ID3 int

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( 0, 'National') --1
SELECT @ID = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region A')--2
SELECT @ID2 = @@IDENTITY

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 1') --3
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 1') --4
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 2') --5

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 2') --6
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 3') --7
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 4') --8

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID, 'Super Region B')--9
SELECT @ID2 = @@IDENTITY

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 3') --10
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 5') --11
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 6') --12
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 7') --13

INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID2, 'Region 4') --14
SELECT @ID3 = @@IDENTITY
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 8') --15
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 9') --16
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 10') --17
INSERT INTO SalesArea ([ParentID], [Name]) VALUES ( @ID3, 'Sub Region 11') --18



INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-5, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (4, DATEADD(d,-6, GETDATE()), 2.81)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-8, GETDATE()), 4.14)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-9, GETDATE()), 1.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-2, GETDATE()), 1.02)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (5, DATEADD(d,-3, GETDATE()), 3.13)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-4, GETDATE()), 6.17)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (7, DATEADD(d,-1, GETDATE()), 3.49)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-4, GETDATE()), 4.29)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (8, DATEADD(d,-5, GETDATE()), 4.46)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-6, GETDATE()), 3.33)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (11, DATEADD(d,-1, GETDATE()), 3.92)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (12, DATEADD(d,-7, GETDATE()), 5.89)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-5, GETDATE()), 6.16)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (13, DATEADD(d,-3, GETDATE()), 3.34)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-2, GETDATE()), 2.61)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (15, DATEADD(d,-3, GETDATE()), 5.12)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (16, DATEADD(d,-4, GETDATE()), 8.28)
INSERT INTO SalesData (AreaID, SalesDate, SalesAmount) VALUES (17, DATEADD(d,-5, GETDATE()), 2.44)