Pulling totals from 3 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pulling totals from 3 different tables

Author

Topic

jimmatz
Starting Member

4 Posts

Posted - 2015-02-20 : 14:32:11

I have 3 SQL tables, an order table, quote table and invoice table. Structured like this:
SO TABLE
Rep Code SO Amount SO Date
1 100 1/2/2015
2 50 2/15/2015
3 20 2/20/2015
2 50 2/20/2015
1 85 2/20/2015

SQ Table
Rep Code SQ Amount SQ Date
1 50 2/2/2015
2 14 2/18/2015
3 67 2/19/2015
1 96 2/20/2015
2 54 2/20/2015

INV Table
Rep Code INV Amount INV Date
1 654 2/2/2015
2 312 2/18/2015
3 54 2/19/2015
1 6 2/20/2015
3 48 2/20/2015

What I want to do is write a query which will sum up each amount by rep code, so it would look something like this:

Rep Code SO Total SQ Total Inv Total
1 185 146 660
2 100 68 312
3 20 67 102

I would also like it to only pull the totals if the appropriate date for each one is lets say in the last week. (So Today -7)

Let me know if this needs any clarification. Thanks for the help!!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 15:45:36

something like this perhaps:


declare @SO table(Rep_Code_so int, Amount int, SO_Date date)
insert into @so(Rep_Code_so, Amount, so_date) values
--Rep Code SO Amount SO Date
(1,100,'1/2/2015'),
(2,50 ,'2/15/2015'),
(3,20 ,'2/20/2015'),
(2,50 ,'2/20/2015'),
(1,85 ,'2/20/2015')

declare @SQ Table(Rep_Code_SQ int, Amount  int,  SQ_Date date)
insert into @sq(Rep_Code_SQ, Amount, SQ_Date) values
--Rep Code SQ Amount SQ Date
(1, 50, '2/2/2015'),
(2, 14, '2/18/2015'),
(3, 67, '2/19/2015'),
(1, 96, '2/20/2015'),
(2, 54, '2/20/2015')

declare @INV Table(Rep_Code_INV int, Amount int, INV_date date)
insert into @inv(Rep_Code_INV, Amount, INV_date) values
-- Rep Code INV Amount INV Date
(1, 654,'2/2/2015'),
(2, 312,'2/18/2015'),
(3, 54 ,'2/19/2015'),
(1, 6  ,'2/20/2015'),
(3, 48 ,'2/20/2015')

select so.Rep_Code_so, so.amount 'SO TOTAL', sq.amount 'SQ Total', inv.amount 'INV Total'
from 
(
	select Rep_Code_so, sum(amount) amount
	from @so so
	group by Rep_Code_so
) so

cross apply
(
	select Rep_Code_SQ, sum(sq.amount) amount
	from @sq sq
	where so.Rep_Code_so = sq.Rep_Code_SQ
	group by Rep_Code_sq
) sq
cross apply
(
	select Rep_Code_INV, sum(inv.amount) amount
	from @inv inv
	where so.Rep_Code_so = inv.Rep_Code_inv
	group by Rep_Code_INV
) inv

jimmatz
Starting Member

4 Posts

Posted - 2015-02-20 : 16:09:50

Thanks for the code Britton! I tweaked a couple things to match my field names but I'm getting an error that says:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sq.SALES_REP_CODE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "inv.SALES_REP_CODE" could not be bound.

Here is my code:
select so.SALES_REP_CODE,so.amount 'SO TOTAL', sq.amount 'SQ Total', inv.amount 'INV Total'
from
(
select SALES_REP_CODE, sum(SO_TOTAL) amount
from SOH
group by SALES_REP_CODE
) so

cross apply
(
select SALES_REP_CODE, sum(QUOTE_DEL_EXT_PRICE) amount
from SQH
where so.SALES_REP_CODE = sq.SALES_REP_CODE
group by SALES_REP_CODE
) sq
cross apply
(
select SALES_REP_CODE, sum(INV_AMOUNT) amount
from ARH
where so.SALES_REP_CODE = inv.SALES_REP_CODE
group by SALES_REP_CODE
) inv

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 17:02:45

you have this:

from SQH
where so.SALES_REP_CODE = sq.SALES_REP_CODE

but at that point, "sq" has not been defined.

Same problem below with inv.sales_rep_code, I think

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources