Re: Grouping Others -newbie

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 04/18/04 

Date: Sun, 18 Apr 2004 08:46:23 -0400

This is a test using a table called test2 as your budget table, which I am
joining to the Orders table in Northwind.... Although you will get zero
rows,
the example will help you get started.... If you wish to verify data, either
add rows to Orders for the current and prior year, or change the Getdates,
to use 1996, etc for which there is already Orders data... hope this helps..

drop table test2
go
create table test2 ( customerid varchar(6) not null)
go
insert into test2 values ('Alfki')
go

select isnull(t.customerid,'others'),
  sum(case when datepart(yy, getdate()) = datepart(yy,orderdate) THEN 1 else
0 end) as [Current Year],
  sum(case when datepart(yy, getdate())-1 = datepart(yy,orderdate) THEN 1
else 0 end) as [Prior Year]
 from test2 t right outer join orders
   on t.customerid = orders.customerid
   group by isnull(t.customerid,'others')
go
drop table test2 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ericl" <eric.losert@lyleandscott.com> wrote in message
news:mOadnajWStUX7h_dRVn2jQ@giganews.com...
> Hi,
>  I normally use crystal reporting as a front end to sql but am having a
> problem getting my head around the following.
>
> We have a report which shows certain customers sales and orders with all
> other customers grouped as others.
>
> I have set up a table which contains the list of relevant customers
> order numbers and budget info.
>
> Accno    Budget
> h0009    1000
> h0010    1000
>
>
> Budget table as above.
>
> Customers table Linked to Orders linked to order lines
>
> 1.I want to show all accounts not in budget file grouped as others.
> 2. A cumulative sales for current year as well as last year sales but
> also sales for a quarter or a month
>
> so the output from sql with a specific parameter of  a particular month
> would be.
>
>
> Account    currentyear    last year    month
> h0009    100000        5000000        20000
> others    5000000        70000000    50000
>
> I can filter for months in Crystal but would lose yearly figures.
>
> Is this feasible or would Analysis Services be a better option?
>
> Any help will be greatly appreciated.
>
> Many thanks

Relevant Pages

  • Re: Sequential integer column in view
    ... select countas rownum ... from customers c2 ... Yes, it does require uniqueness. ... select * from test2 ...
    (microsoft.public.sqlserver.programming)
  • Error in Arithmetic Operations
    ... Ref: SQL Server 2000 SP3 ... I have tested the following from the Query Analyzer ... Server when the litteral is used as denominator as shown in Test1? ... is much different from the result of Test2. ...
    (microsoft.public.sqlserver.programming)