sql server dts: It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 )


The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.


--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


[quoted text, click to view]

I have a query that I need to do subtotals on. The query is as follows:

select
ba,
rg,
sum(order_qty) as oqty,
sum(order_val) as oval
from sales_ord_curr_yr a
inner join sales_product on
sales_product.prod_cd = a.prod_cd
inner join tblproducthierarchy on
sales_product.level_1_1 + sales_product.level_1_2 +
sales_product.level_2 + sales_product.level_3 = sproducthierarchy
where
level_1_1 in ('10','11','14')
and convert(varchar,ord_date,101) = convert(varchar,DATEADD(day, -1,
getdate()),101)
group by
ba,rg
left(sProductHierarchy,5)
order by
left(sProductHierarchy,5)

Basically, I am just after the previous day's orders.

However, what I need to do is print out subtotals based on the field BA
(varchar) changing, from 10, to 11, to 14. There are 2 RG's in BA 10, 5
in BA 11, and 5 in BA 14.

I want to create a spreadsheet in DTS and then email it to a user as a
regular job.

The results should be similar to:
BA RG Oqty Oval
10 100 1000 15000
10 200 550 12000
1550 27000
11 100 5 1000
11 200 5 1500
11 500 10 10000
20 12500
14 100 50 5000
14 200 25 2500
75 7500

Total 1550 + 75 + 20 27000+12500+7500

Is there any way to do this in SQL?

I know how to do all of that, except for putting subtotals on each
change in BA, and a grand total on all of it.

Any help would be appreciated,

Thanks for the help.

I'll try to find some time to look into the issue later this afternoon.
Perhaps others will sooner.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


[quoted text, click to view]

[quoted text, click to view]
Here is one of the three data sources


INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26831',0,0,290.50,290.50)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26910',1,0,287.78,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26914',1,0,287.78,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26916',1,0,287.78,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26918',1,0,287.78,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','89060',1,0,366.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','24224',1,0,1173.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','24512',10,0,4539.26,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','25069',5,0,1295.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26056',1,0,230.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','24602',1,0,292.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','25069',1,0,259.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26056',2,0,460.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','29383',1,0,749.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','24602',2,0,584.00,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',2,0,246.90,0.00)
INSERT INTO [sales_ord_curr_yr_test1]
([ord_date],[prod_cd],[order_qty],[ship_qty],[order_val],[ship_val])VALUES('Jul
27 2006 12:00:00:000AM','26062',1,0,123.45,0.00)
INSERT INTO [sales_ord_curr_yr_test1]

[quoted text, click to view]
Here is the second data source

INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('89406','14','700','00200','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25163','11','001','00640','0040')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25094','11','002','01100','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25071','11','001','00640','0040')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('88892','14','375','00100','0410')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('27067','11','004','00095','0000')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('29331','11','001','00300','0040')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26683','11','002','00170','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25301','11','002','00100','0040')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('29039','11','002','00300','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26859','11','002','00100','0027')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24058','11','005','00100','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('89184','14','001','00200','0015')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26837','11','002','00100','0027')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('89060','14','001','00300','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25124','11','002','01100','0030')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26767','11','002','00200','0025')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24111','11','005','00100','0030')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('29070','11','005','00300','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('89260','14','001','00100','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25087','11','001','00600','0030')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25869','11','002','00305','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24787','11','001','00300','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26561','11','002','00390','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24120','11','005','00100','0040')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26914','10','002','00100','0060')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26060','10','003','01100','0050')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26421','11','001','00500','0015')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24657','11','001','00625','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('29294','11','002','00400','0015')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('89290','14','001','00100','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('29063','11','005','00200','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26691','11','002','00170','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24512','11','001','00500','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24602','11','001','00600','0015')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24649','11','002','00410','0025')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25931','11','001','00690','0040')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('23990','11','005','00300','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24159','11','002','00410','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26621','11','002','00100','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25809','11','002','00200','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25915','11','002','00305','0010')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('24633','11','001','00600','0015')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('26605','11','002','00100','0032')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('27099','11','005','00100','0030')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('29383','11','001','00300','0020')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25683','11','002','00200','0035')
INSERT INTO [sales_product_test1]
([prod_cd],[level_1_1],[level_1_2],[level_2],[level_3])VALUES('25069','11','001','00600','0010')
INSERT INTO [sales_product_test1]

[quoted text, click to view]
Here is the 3rd data source


INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10000000000000','BUSINESS AREA
1','RG 8')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10001000000000','BUSINESS AREA
1','RG 29')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002000000000','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001000000','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001000040','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001000060','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001200000','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001200060','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001500000','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10002001500060','BUSINESS AREA
1','RG 16')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10003000000000','BUSINESS AREA
1','RG 23')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10003011000000','BUSINESS AREA
1','RG 23')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10003011000040','BUSINESS AREA
1','RG 23')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10003011000050','BUSINESS AREA
1','RG 23')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10003011000060','BUSINESS AREA
1','RG 23')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10004000000000','BUSINESS AREA
1','RG 3')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10004002000000','BUSINESS AREA
1','RG 3')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10004002000010','BUSINESS AREA
1','RG 3')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10004002000015','BUSINESS AREA
1','RG 3')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10004002000020','BUSINESS AREA
1','RG 3')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100000000000','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100001000000','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100001000100','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100001000130','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100001000150','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100002000000','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10100002000100','BUSINESS AREA
1','RG 22')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10300000000000','BUSINESS AREA
1','RG 12')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10300001000000','BUSINESS AREA
1','RG 12')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10300001000010','BUSINESS AREA
1','RG 12')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10300003000000','BUSINESS AREA
1','RG 12')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10300005000000','BUSINESS AREA
1','RG 12')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10300005000100','BUSINESS AREA
1','RG 12')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10500000000000','BUSINESS AREA
1','RG 11')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10500002000000','BUSINESS AREA
1','RG 11')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10500002000010','BUSINESS AREA
1','RG 11')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10500002000020','BUSINESS AREA
1','RG 11')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10992000000000','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10992001000000','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099200100100','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099200100200','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10992001200000','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099200120100','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099200120200','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10992001500000','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099200150100','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099200150200','BUSINESS AREA
1','RG 20')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10993000000000','BUSINESS AREA
1','RG 21')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('10993001000000','BUSINESS AREA
1','RG 21')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099300100100','BUSINESS AREA
1','RG 21')
INSERT INTO [tblproducthierarchy_test1]
([sProductHierarchy],[BA],[RG])VALUES('1099300100200','BUSINESS AREA
1','RG 21')

[quoted text, click to view]
Here are the table definitions. I posted them a while ago, but they
never showed up.

Here's the main table:

CREATE TABLE [sales_ord_curr_yr_test1] (
[ord_date] [datetime] NULL ,
[prod_cd] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[order_qty] [int] NULL ,
[ship_qty] [int] NULL ,
[order_val] [decimal](18, 2) NULL ,
[ship_val] [decimal](18, 2) NULL
) ON [PRIMARY]
GO


Here's the product file:

CREATE TABLE [SALES_PRODUCT_test1] (
[prod_cd] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[level_1_1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_1_2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_2] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO




CREATE TABLE [tblProductHierarchy_test1] (
[sProductHierarchy] [varchar] (14) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BA] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RG] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [aaaaatblProductHierarchy_PK] PRIMARY KEY NONCLUSTERED
(
[sProductHierarchy]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO




I will try to post the data in a few.

BC

Thanks for posting the DDL and data.

You can use the rollup option of the group by to generate subtotals (lookup
rollup and cube in BOL), so your query should look something like I have
chosen to mark the total rows with the literal 'Totals', there are lots of
other ways you can do this, for example, you can include the grouping()
function results as a column in your output, you just need some some way to
tell which rows are totals and which rows are details:

select
Case When Grouping (ba) = 1 Then 'Totals' Else ba End as ba,
Case When Grouping (rg) = 1 Then 'Totals' Else rg End as rg,
sum(order_qty) as oqty,
sum(order_val) as oval
from sales_ord_curr_yr_test1 a
inner join sales_product_test1 p on
p.prod_cd = a.prod_cd
inner join tblproducthierarchy_test1 h on
p.level_1_1 + p.level_1_2 +
p.level_2 + p.level_3 = h.sproducthierarchy
where
level_1_1 in ('10','11','14')
and ord_date >= DATEADD(day, -1, convert(char,getdate(),101))
and ord_date < convert(char,getdate(),101)
group by ba,rg, left(sProductHierarchy,5) with rollup
having grouping (left(sProductHierarchy,5)) = 1
order by ba, Grouping (ba), rg , Grouping (rg), left(sProductHierarchy,5)

A couple of comments, first when testing this query with the sample data you
provided, remember it will stop working at midnight tonight (I feel like I'm
talking to Cinderella <BG>) because it is testing for data with yesterday's
date. If testing after midnight tonight, either change the getdate()
functions to a date literal of '2006-07-28' (todays date) or change the
dates on your sample data so that they have yesterday's date since this
query only finds yesterday's data.

Second, your original query had a where clause that included:

and convert(varchar,ord_date,101) = convert(varchar,DATEADD(day, -1,
getdate()),101)

which is intended to get all the data from yesterday. You don't want to do
it this way because it has a function applied to a column in your table. If
at all posible you don't want to do this because 1) SQL Server will not be
able to use an index if you do this, and 2) SQL Server has to apply the
convert function on every row it examines. Instead, write it something
like:

and ord_date >= DATEADD(day, -1, convert(char,getdate(),101))
and ord_date < convert(char,getdate(),101)

which is logically exactly the same thing (give me all rows with a datetime
[quoted text, click to view]
because it only needs to do the conversion once (since it treats getdate()
as a constant and only evaluates it once) and can use indexes on the
ord_date column.

HTH,
Tom

[quoted text, click to view]