GROUP BY for heirarchical data

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY for heirarchical data

Author

Topic

JohnC28
Starting Member

11 Posts

Posted - 2008-03-07 : 05:01:50

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)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 10:26:20

Maybe I missed something but:


ID     ParentID   [Name]
1	0	National
2	1	Super Region A
3	2	Region 1
4	3	Sub Region 1
5	3	Sub Region 2
6	2	Region 2
7	6	Sub Region 3
8	6	Sub Region 4
9	1	Super Region B
10	9	Region 3
11	10	Sub Region 5
12	10	Sub Region 6
13	10	Sub Region 7
14	9	Region 4
15	14	Sub Region 8
16	14	Sub Region 9
17	14	Sub Region 10
18	14	Sub Region 11


ID    AreaID    SalesDate               SalesAmount
1	4	2008-03-02 10:20:00	3.49
2	4	2008-03-01 10:20:00	2.81
3	5	2008-02-28 10:20:00	4.14
4	5	2008-02-27 10:20:00	1.89
5	5	2008-03-05 10:20:00	1.02
6	5	2008-03-04 10:20:00	3.13
7	7	2008-03-03 10:20:00	5.12
8	7	2008-03-03 10:20:00	6.17
9	7	2008-03-06 10:20:00	3.49
10	8	2008-03-03 10:20:00	4.29
11	8	2008-03-02 10:20:00	4.46
12	11	2008-03-01 10:20:00	3.33
13	11	2008-03-06 10:20:00	3.92
14	12	2008-02-29 10:20:00	5.89
15	13	2008-03-02 10:20:00	6.16
16	13	2008-03-04 10:20:00	3.34
17	15	2008-03-05 10:20:00	2.61
18	15	2008-03-04 10:20:00	5.12
19	16	2008-03-03 10:20:00	8.28
20	17	2008-03-02 10:20:00	2.44

How are those tables supposed to connect?

What can do is get your grandtotal and union to sub-group total etc

Select 'Grand Total', Sum(SalesAmount), min(SalesDate)
FROM SalesData

Union

Select [Name], Sum(SalesAmount),min(SalesDate)
FROM SalesArea {somehow join) SalesData on {linking fields}
Where SalesArea = {subareacode?}
Group by [Name]
UNION

---and so forth.

Poor planning on your part does not constitute an emergency on my part.

JohnC28
Starting Member

11 Posts

Posted - 2008-03-07 : 10:42:00

Hi dataguru

The link is AreaID in the sales table - FK to ID in the SalesArea table (your 2nd field is incorrectly called AreaId - that should be ParentID).

That join is clearly straightforward.

My problem is how to group by higher up the heirarchy.

So if I send in 1 to a SPROC, I'd want data grouped at national level (just one row), Super Regional Level (2 rows in my sample data), Regional Level(4 rows of output), and finally sub-regional level.

However, the sales data may only be at sub-regional level and that's the issue I can't get round. How do I GROUP BY in a recursive fashion down to the level that actually holds the join data.

JohnC28
Starting Member

11 Posts

Posted - 2008-03-07 : 11:49:43

This should be (my caps):

ID PARENTID [Name]
1 0 National
2 1 Super Region A
3 2 Region 1
4 3 Sub Region 1
5 3 Sub Region 2
etc

ID AreaID SalesDate SalesAmount
1 4 2008-03-02 10:20:00 3.49
2 4 2008-03-01 10:20:00 2.81
3 5 2008-02-28 10:20:00 4.14

so AreaID joins to ID in the first table, and ID joins to ParentID in a self join all the way up to 0.

Sorry not to be clear about that :)

JohnC28
Starting Member

11 Posts

Posted - 2008-03-07 : 11:53:02

Just to be absolutely clear Row 2 of the SalesData table would join to 'Sub Region 1' from the Area table.

'Sub Region 1' would join to 'Region 1' through a self-join, 'Region 1' to 'Super Region A' and then to 'National'.

A GROUP BY at National level would SUM/AVG all data in the SalesData table, a GROUP by at the next level (any child with 'National' as the ParentID would SUm/AVG all data in the descendants of each 2 level child and so forth.

Does that help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-07 : 12:30:43

Or use the WITH CUBE or WITH ROLLUP to get all data in one operation.

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 17:02:37

I am not sure how to use those functions, nor in this scenario...But I did get the desired results using the below. (Your code is at the top..there was a type in one the table names..this runs and produces the results)


 	
 

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


CREATE TABLE [dbo].[SalesArea](
[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)

---------
Select  Roll.ID,Roll.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.ID = area.ID) total
		on total.ParentID = area.ID  
	   JOIN  SalesArea roll on area.ParentID = roll.ID
 GROUP by  Roll.ID,Roll.Name
--
UNION ALL
Select area.ID,area.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID) total
		on total.ParentID = area.ID  
Group by area.ID,area.Name
UNION

Select	 total.ID,Total.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID) total
		on total.ParentID = area.ID  

Group by  total.ID,Total.Name

/*Results
1	National	17.45	2008-02-28 16:31:00
2	Super Region A	14.2	2008-02-27 16:31:00
3	Region 1	16.48	2008-02-27 16:31:00
4	Sub Region 1	6.3	2008-03-01 16:31:00
5	Sub Region 2	10.18	2008-02-27 16:31:00
6	Region 2	23.53	2008-03-02 16:31:00
7	Sub Region 3	14.78	2008-03-03 16:31:00
8	Sub Region 4	8.75	2008-03-02 16:31:00
9	Super Region B	28.94	2008-03-01 16:31:00
10	Region 3	22.64	2008-02-29 16:31:00
11	Sub Region 5	7.25	2008-03-01 16:31:00
12	Sub Region 6	5.89	2008-02-29 16:31:00
13	Sub Region 7	9.5	2008-03-02 16:31:00
14	Region 4	18.45	2008-03-02 16:31:00
15	Sub Region 8	7.73	2008-03-04 16:31:00
16	Sub Region 9	8.28	2008-03-03 16:31:00
17	Sub Region 10	2.44	2008-03-02 16:31:00
*/

Drop Table SalesData
Drop Table SalesArea

I would like to see how the CUBE and ROLLUP could be used, or a cleaner way to do this. I was just playing around with it.

Poor planning on your part does not constitute an emergency on my part.

JohnC28
Starting Member

11 Posts

Posted - 2008-03-07 : 19:56:54

Hi dataguru

Thanks for sticking with this.

The result certainly looks good.

I'll give the code a try in the morning when I'm a bit more focussed (it's 1am here) and let you know how that works.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 20:14:36

okay. I like learning how to do new stuff...I wish I had picked up that relational self join thing sooner. Makes sense now though...

I am gonna keep playing with it to see if I can figure out the rollup and Cube stuff.

Poor planning on your part does not constitute an emergency on my part.

JohnC28
Starting Member

11 Posts

Posted - 2008-03-08 : 07:09:44

It works!

And performance seems good to.

The only issues now are
(a) What happens for areas with greater than three depths

(b) How do I get just part of the tree (say the bottom two levels)

If anyone's got any thoughts, I'd be pleased to hear them - in the meantime I will persue.

Thanks for the input dataguru!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 08:09:16

You can comment out part of my query to get just the parts.

If you do each segment individually you can see how they look.

The way this is written, it should work even with more levels.

To get different levels by passing a paramenter you may have to add some conditions. Since the areaId, ParentID , and ID columns are used together in the output...you can add the other levels as "sub level" or something to the query.

I will post back with something on that.

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 08:40:36

What it does is in each part of the UNION, it applies the @LEVEL paramenter to that sets ID column.

For example, the first section does the National,and Super Regions and by itself produces:



1	National	17.45	2008-02-29 08:28:00
2	Super Region A	14.2	2008-02-28 08:28:00
9	Super Region B	28.94	2008-03-02 08:28:00

Which represents the PARENTID column from the Area table, but wrapped inside the derived table for the self join and aliased as ROLL.

The 2nd set produces this by itself, where The ID column in this part is the SalesArea.ID column limited to the derived table which joins area and sales on Derived(joins on SalesData.AreaID to Area.ID).ParentID = Area.ID


3	Region 1	16.48	2008-02-28 08:28:00
6	Region 2	23.53	2008-03-03 08:28:00
10	Region 3	22.64	2008-03-01 08:28:00
14	Region 4	18.45	2008-03-03 08:28:00

The 3rd set produces this by itself, where The ID column in this part is the lowest level
the last part produces:


4	Sub Region 1	6.3	2008-03-02 08:28:00
5	Sub Region 2	10.18	2008-02-28 08:28:00
7	Sub Region 3	14.78	2008-03-04 08:28:00
8	Sub Region 4	8.75	2008-03-03 08:28:00
11	Sub Region 5	7.25	2008-03-02 08:28:00
12	Sub Region 6	5.89	2008-03-01 08:28:00
13	Sub Region 7	9.5	2008-03-03 08:28:00
15	Sub Region 8	7.73	2008-03-05 08:28:00
16	Sub Region 9	8.28	2008-03-04 08:28:00
17	Sub Region 10	2.44	2008-03-03 08:28:00

I then just order by the resulting ID column to get the unique list and levels you asked for.

this part would allow for filtering for levels, in this case filtering for the appropriate ID column (although one that is NOT returned in the data)



Declare @Level int
SET @LEVEL = 1


Select  Roll.ID,Roll.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.ID = area.ID) total
		on total.ParentID = area.ID  
	   JOIN  SalesArea roll on area.ParentID = roll.ID
Where Roll.ID = @LEVEL 
 GROUP by  Roll.ID,Roll.Name


UNION ALL

Select area.ID,area.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date 
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID) total
		on total.ParentID = area.ID  
Where Total.ID = @LEVEL 
Group by area.ID,area.Name 


UNION

Select	 total.ID,Total.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID) total
		on total.ParentID = area.ID  
Where area.ID = @LEVEL 
Group by  total.ID,Total.Name


/*Results where @LEVEL = 3
2	Super Region A	2.91	2008-02-28 08:19:00
4	Sub Region 1	6.3	2008-03-02 08:19:00
5	Sub Region 2	10.18	2008-02-28 08:19:00

@LEVEl = 5
3	Region 1	10.18	2008-02-28 08:19:00

@LEVEL = 1
1	National	17.45	2008-02-29 08:23:00

@LEVEL = 14
15	Sub Region 8	7.73	2008-03-05 08:24:00
16	Sub Region 9	8.28	2008-03-04 08:24:00
17	Sub Region 10	2.44	2008-03-03 08:24:00

@LEVEL = 2
2	Super Region A	14.2	2008-02-28 08:25:00
*/

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 08:52:29

Here is another alternative. You can remove the unions and do a filter for which sub-set you want. At this point I think you can get the idea of how you might be able to get a sproc out of this to return desired results.


Declare @Level int
SET @LEVEL = 1


IF @LEVEL = 1
Select  Roll.ID,Roll.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.ID = area.ID) total
		on total.ParentID = area.ID  
	   JOIN  SalesArea roll on area.ParentID = roll.ID
GROUP by  Roll.ID,Roll.Name


IF @LEVEL = 2
Select area.ID,area.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date 
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID) total
		on total.ParentID = area.ID  
Where Total.ID = @LEVEL 
Group by area.ID,area.Name 

IF @LEVEL = 3
Select	 total.ID,Total.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID) total
		on total.ParentID = area.ID  
Where area.ID = @LEVEL 
Group by  total.ID,Total.Name

Poor planning on your part does not constitute an emergency on my part.

JohnC28
Starting Member

11 Posts

Posted - 2008-03-08 : 19:45:05

There's something that perplexes me.....

(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
FROM SalesData data join SalesArea area
this line >>>>> on data.ID = area.ID) total

There is no relationship between the primary key of the area table and the primary key of the data table.

And yet this seems to work.

Why?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 20:09:50

Sure there is.

It is just a trick to join the tables and get the appropriate parent region. You can see it by looking at the subquery aggregate results, and then referring to the actual alias'd table "total" that your question refers to (which is the 2nd part of the UNION)

the results from that subset as I wrote it are:


ID	Name	Sales	Date
3	Region 1	16.48	2008-02-28 19:56:00
6	Region 2	23.53	2008-03-03 19:56:00
10	Region 3	22.64	2008-03-01 19:56:00
14	Region 4	18.45	2008-03-03 19:56:00

the subquery alias "total" itself produces:


ID	AreaID	SalesDate	SalesAmount	ParentID	Name
4	4	2008-03-03 20:00:00	3.49	3	Sub Region 1
4	4	2008-03-02 20:00:00	2.81	3	Sub Region 1
5	5	2008-02-29 20:00:00	4.14	3	Sub Region 2
5	5	2008-02-28 20:00:00	1.89	3	Sub Region 2
5	5	2008-03-06 20:00:00	1.02	3	Sub Region 2
5	5	2008-03-05 20:00:00	3.13	3	Sub Region 2
7	7	2008-03-04 20:00:00	5.12	6	Sub Region 3
7	7	2008-03-04 20:00:00	6.17	6	Sub Region 3
7	7	2008-03-07 20:00:00	3.49	6	Sub Region 3
8	8	2008-03-04 20:00:00	4.29	6	Sub Region 4
8	8	2008-03-03 20:00:00	4.46	6	Sub Region 4
11	11	2008-03-02 20:00:00	3.33	10	Sub Region 5
11	11	2008-03-07 20:00:00	3.92	10	Sub Region 5
12	12	2008-03-01 20:00:00	5.89	10	Sub Region 6
13	13	2008-03-03 20:00:00	6.16	10	Sub Region 7
13	13	2008-03-05 20:00:00	3.34	10	Sub Region 7
15	15	2008-03-06 20:00:00	2.61	14	Sub Region 8
15	15	2008-03-05 20:00:00	5.12	14	Sub Region 8
16	16	2008-03-04 20:00:00	8.28	14	Sub Region 9
17	17	2008-03-03 20:00:00	2.44	14	Sub Region 10

So AreaID = ID for this purpose...merely putting the data together so I could connect ParentID back into the Salesarea.ID field.

when wrapped into this:


Select area.ID,area.Name, Sum(SalesAmount) as Sales, min(SalesDate) as Date 
FROM  SalesArea area join 
				(Select area.ID,AreaID,SalesDate,SalesAmount,ParentID,[Name]
				 FROM SalesData data join SalesArea area 
					on data.AreaID = area.ID)  total 
		on total.ParentID = area.ID  

Group by area.ID,area.Name

I get the ParentID from the above subset, and link back to the salesarea table to get the [Name] associated with THAT parentID (from the alias'ed table called "total")

It was the only way it would produce the correct subset (above, in summary for the Regions 1,2,3,4)

I am sure there are better ways to do this, but this one was a neat thing to figure out.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources