Tom:

This is precisely what I needed. Many thanks for your help.

BC



Tom Cooper wrote:
> You can use Case in the order by clause, something like:
>
> order by case ba When 'Business Area There' Then 1
> When 'Business Area Here' Then 2
> When 'Business Area Wherever' Then 3
> Else 4 End,
> ba, Grouping (ba), rg , Grouping (rg), left(sProductHierarchy,5)
>
> That would put 'Business Area There' first, 'Business Area Here' second,
> 'Business Area Wherever' third, and all other values of ba would follow in
> alphabetical order.
>
> Tom
>
> "Blasting Cap" wrote in message
> news:OXcIb6jtGHA.1508@TK2MSFTNGP05.phx.gbl...
>> Tom:
>>
>> Thanks for the post. It works like a charm, except for the order by (on
>> my actual data not the test data I posted).
>>
>> I've been tweaking the order & groupby parts but can't seem to get it to
>> come out like I want to.
>>
>> On my actual data, I have data like:
>>
>> Business Area There
>> Business Area Here
>> Business Area Wherever
>>
>> - that's the order they need to be in - there, here and wherever. However,
>> the order by orders them in the Here, There and Wherever order. I haven't
>> been able to get them ordered properly yet, but the query does exactly
>> what I want it do do otherwise.
>>
>> Any idea how to tweak the order by to make it come out in the actual order
>> of Product Hierarchy first?
>>
>> Thanks,
>>
>> BC
>>
>>
>>
>>
>> Tom Cooper wrote:
>>> 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 ) 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 >= midnight yesterday and < midnight today), but will run much
>>> faster 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
>>>
>>> "Blasting Cap" wrote in message
>>> news:%231P0slosGHA.4324@TK2MSFTNGP05.phx.gbl...
>>>> Arnie Rowland wrote:
>>>>> 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.
>>>>>
>>>>>
>>>> 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
>>>>
>