July 19, 2006 at 5:23 pm
I'm a beginner in Sql 2000 and in this forum too. I have two tables, one with general information of customers and the other with customer's sales.
I need the the customer's sales by month and year, percent of "everything" and an average too.
I made a view but I don't know how to calculate the percent and average.
It's somenthing like this:
Customer Year Month Sales % Month Sales % annual avg % Grand Total
A 2006 1 100 1.7 2 350 2.8 110 450
B......
Can you give some advices for this, please.
Thanks.
July 20, 2006 at 4:45 am
This might give you an idea how to start
-- Prepare test data
declare @customers table (custid int, custname varchar(2))
insert @customers
select 1, 'AA' union all
select 2, 'AB' union all
select 3, 'C' union all
select 4, 'DZ'
declare @sales table (custid int, salesdate datetime, amount money)
insert @sales
select 1, '2006-07-02', 150 union all
select 1, '2006-07-05', 130 union all
select 2, '2006-06-01', 120 union all
select 2, '2006-07-02', 110 union all
select 3, '2006-07-19', 30 union all
select 3, '2006-07-20', 50 union all
select 4, '2006-06-10', 190 union all
select 4, '2006-07-20', 100
-- Start working
declare @output table (custid int, Customer varchar(2), Year smallint, Month tinyint, Sales money, [CustSaleCompOtherThisMonth%] smallmoney, [CustSaleCompSelfTotal%] smallmoney)
insert @output (custid, customer, year, month, sales)
select c.custid,
c.custname,
year(s.salesdate),
month(s.salesdate),
sum(s.amount)
from @customers c
inner join @sales s on s.custid = c.custid
group by c.custid,
c.custname,
year(s.salesdate),
month(s.salesdate)
update o
set [CustSaleCompOtherThisMonth%] = 100.0 * o.sales / z.ts
from @output o
inner join (
select year,
month,
sum(sales) ts
from @output
group by year,
month
) z on z.year = o.year and z.month = o.month
update o
set [CustSaleCompSelfTotal%] = 100.0 * o.sales / z.ts
from @output o
inner join (
select custid,
sum(sales) ts
from @output
group by custid
) z on z.custid = o.custid
select Customer,
Year,
Month,
Sales,
[CustSaleCompOtherThisMonth%],
[CustSaleCompSelfTotal%]
from @output
order by Customer,
Year,
Month
Output is
CustomerName Year Month Sales CustSaleCompOtherThisMonth% CustSaleCompSelfTotal%
------------ ---- ----- -------- --------------------------- ----------------------
AA 2006 7 280.0000 49.1228 100.0000
AB 2006 6 120.0000 38.7097 52.1739
AB 2006 7 110.0000 19.2982 47.8261
C 2006 7 80.0000 14.0351 100.0000
DZ 2006 6 190.0000 61.2903 65.5172
DZ 2006 7 100.0000 17.5439 34.4828
N 56°04'39.16"
E 12°55'05.25"
July 20, 2006 at 1:21 pm
I'll prove it tomorrow.
Thanks a lot Peter.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply