Data extraction from Multiple Tables
-
Hi All
I want to make reports according to Salesperson which shows Daily total sales and costs. Every next day it will add that day total in total sales means at the end of month it will show total of 1to30.
But now the thing is data is not in a one table Like:-
My item Table has ItemGroup & ItemDivision field. I have to do calculation on the for a particular group eg if salesman has 400,000 totalsales on itemgroup A then he will get 1% reward of total sales. Other Calculation is on Itemdivision having same criteria.
Sales table has salesman Name
Billing Table has Date,Sell,cost field per item
Billing table has comman fields for both tables but sales and item tables not.
so my output is not correct. Can i chose according to ItemGroup only & then division individual salesperson in a singel query?with singel query i can make a single report my company wants this report on reporting server on singal report.
i will have to join other tables also what first want these 3 tables only...
-
Hi Vandy,
I'm finding it difficult to figure out what you're asking for. Could you give us some sample data and the query output you'd want from that data? That would really help...
Thanks
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan
Sorry I tried to make it simple.........
Actually we have ItemGroup field and itemDivision field in item table
let's say ItemGroup 'AA' and ItemDivision='COP'
I want to select data form table
Select * From item
Where ItemGroup='AA' AND ItemDivision='COP'
Now in other table i have Date, Sales, ItemCode, Cost fields.
Third table i have SalesmanCode , ItemCode field.
Andy is my salesperson he sold 100 items in a day in which 30 items have ItemCode=AA and 20 items have IemDivision=COP (ItemCode and COP not related with each other). I want to calculate total sum of that 30 items having ItemCode 'AA' and total sum of 20 items having ItemDivision='COP'
This is the first part of my problem. Hope now it's able to undersatnd.
Thanks
Vandy
-
If I'm understandnig you....I'd solve this something like this...
--a generic sales person name
create table sales_person
(
sales_person_id int identity(1,1) primary key,
person_name varchar(100)
)
go
--a generic product_list
create table product
(
product_name varchar(100) primary key,
product_cost money default 0.00
)
go
--a generic order detail
create table order_detail
(
order_detail_id int identity(1,1) primary key,
product_name varchar(100),
number int default 0.00,
constraint fk_product_name foreign key(product_name)
references product(product_name)
)
go
--a generic orders table
create table orders
(
order_id int identity(1,1),
sales_person_id int,
order_detail_id int,
order_date datetime not null default getdate(),
constraint pk_orders primary key(sales_person_id,order_detail_id,order_date),
constraint fk_sales_person_orders foreign key(sales_person_id)
references sales_person(sales_person_id),
constraint fk_orderdeatil_order foreign key(order_detail_id)
references order_detail(order_detail_id)
)
go
--key control to use with a view
create table sales_monthly_view_conrol
(
[month_to_report] int not null unique
)
go
create trigger tr_sales_control_insert_restrict
on sales_monthly_view_conrol
instead of insert
as
begin
if (select count(*) from sales_monthly_view_control) > 1
begin
print 'Only one record permitted on Control Table'
end
else
begin
insert sales_monthly_view_control
select * from inserted
end
end
go
create trigger tr_sales_control_delete_restrict
on sales_monthly_view_conrol
instead of delete
as
begin
if (select count(*) from sales_monthly_view_control) > 2
begin
delete sales_monthly_view_control
from sales_monthly_view_control t
join deleted d
on t.[month_to_report] = d.[month_to_report]
end
else
begin
print 'Must Contain at least 1 record for View to work'
end
end
go
--Your report is in the view
create view v_monthly_sales_cost_report
as
select person_name,
p.product_name,
sum(number) sales,
p.product_cost*sum(number) cost
from orders o
join order_detail d
on o.order_detail_id = d.order_detail_id
join sales_person s
on s.sales_person_id = o.sales_person_id
join sales_monthly_view_conrol v
on v.month_to_report = datepart(month,o.order_date)
join product p
on p.product_name = d.product_name
group by person_name,p.product_name,p.product_cost
go
HTH
Mathew J Kulangara
sqladventures.blogspot.com -
Thanks Mathew
This is what i was thinking that i need to create new tables, first two tables i have .
Order tables need to create.
Many Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply