?
Solved

JOIN in SQL Teradata

Posted on 2011-09-09
16
Medium Priority
?
534 Views
Last Modified: 2013-11-11
I have both shipper & payor accounts associated with revenue.
I need to combine this revenue, choosing BOTH shipper AND payor revenue, but not duplicate the revenue.

My query now is
SELECT month, shipper_acct, payor_acct, revenue
FROM DB
WHERE (DB.shipper_acct = 123 OR DB.payor_acct = 123)

and it returns
Month        shipper_acct     payor_acct    rev
201102   123                     123               $10
201102    123                     750              $60
201103    456                    123                $20
201103    123                     574               $30

I would like the result to be
Month      acct     rev
201102    123      $70
201103     123     $50

Thanks!
0
Comment
Question by:Euro5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36511063
SELECT month, 123, sum(revenue)
FROM DB
WHERE (DB.shipper_acct = 123 OR DB.payor_acct = 123)
group by month

0
 
LVL 18

Expert Comment

by:lludden
ID: 36511087
The specific for that query would just be

SELECT [Month],  123 AS acct, SUM(Rev) AS Rev
FROM (
SELECT month, shipper_acct, payor_acct, revenue
FROM DB
WHERE (DB.shipper_acct = 123 OR DB.payor_acct = 123)
) T1

If you want a general version, you need to decide which account has precedence in determining the revenue.  In the sample above, how do you decide to use Acct 123 instead of acct 456 or 750?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 36511092
SELECT month, shp.shipper_acct,  sum(shp.revenue + isnull(pay.revenue,0))
FROM DB shp
left join db pay on shp.shipper_acct = pay.payor_acct and shp.month = pay.month
WHERE (shp.shipper_acct = 123 OR shp.payor_acct = 123)
group by month, shp.shipper_acct

0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 36511126
note that the above will not capture

201103    574               $30  

in order to do that you would need to insert the results into a temporary table, and do a select on the table where the pay account and paymonth not in your table.

0
 

Author Comment

by:Euro5
ID: 36511927
What if I have a list of account numbers to pull the revenue on?
0
 
LVL 18

Expert Comment

by:lludden
ID: 36512071
If you have a query that has the account numbers, then you can do:

SELECT Month, Acct, SUM(Revenue) AS TotalRev FROM (
SELECT DISTINCT db.Month, DB.Revenue, A.Acct
FROM DB
  INNER JOIN (SELECT Acct FROM AcctList) A ON DB.Shipper_Acct = A.Acct OR DB.Payor_Acct = a.Acct
) T1


This will produce duplicate revenue if both shipper and payer are in the list (it will produce the revenue for each)

0
 

Author Comment

by:Euro5
ID: 36512239
I am provided with a list of accounts each week. I cannot gave duplicate revenue so that won't work.....hm
0
 
LVL 18

Expert Comment

by:lludden
ID: 36512306
You have a condition you need to decide on.  
What happens if the account list contains both a shipper and a payor?  

With the query I gave you, both will get the revenue.

If you want it to only show up for one, you need to decide which one will get it.  The query can then be adjusted so that if there is an AND, it will only give it to one.


0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 36513015
Euro,

you can use my query to build the temporary table.

In your final select just do

where shipper_acct in (123, 456, 789)
0
 
LVL 18

Expert Comment

by:lludden
ID: 36513162
Ged325,

He needs to decide how to handle the condition where a valid ID is in both columns.  Once you have that, whether you use temp tables or whatever is really just a matter of preference and/or performance.
0
 

Author Comment

by:Euro5
ID: 36513631
I see, so if account is same in ship and pay, I need to use only shipper so I don't get dup revenue.
0
 
LVL 18

Expert Comment

by:lludden
ID: 36520616
Here is the problem situation:  If your data is like this:
Month        shipper_acct     payor_acct    rev
201102   123                     123               $10
201102    123                     750              $60
201103    456                    123                $20
201103    123                     574               $30

And the list of accounts you want the totals for is this:
123, 750, 456

Where does the revenue go for the middle two records.  Both are in the list, but you don't want revenue duplicated.  So you need to make a rule to handle this situation.
0
 
LVL 1

Expert Comment

by:Dromame
ID: 36522425
Build your temp table:
CREATE TABLE tmp_tbl
AS SELECT month, shipper_acct AS acct, revenue
FROM DB
WHERE (DB.shipper_acct IN (123,456,567,678);

INSERT INTO tmp_tbl
SELECT month, shipper_acct, revenue
FROM DB
WHERE (DB.shipper_acct IN (123,456,567,678)
AND payor_acct <> shipper_acct;

SELECT month, acct, SUM(revenue)
 FROM tmp_tbl
GROUP BY (month,acct)
ORDER BY (acct, month);
0
 

Author Comment

by:Euro5
ID: 36523050
lludden - Yes, I understand. I need to use the provided list and get the revenue associated with it whether shipper or payor. How would I accomplish this?
0
 
LVL 18

Expert Comment

by:lludden
ID: 36526071
Assume this data:
Month        shipper_acct     payor_acct    rev
201102   123                     123               $10
201102    123                     750              $60
201103    456                    123                $20
201103    123                     574               $30

And this account list:
123, 750, 456

We get
Month    Acct   Shipper, Payor, Either
201102 123   70           10       70
201102 750   0              60      60
201103 456   20            0        20
201103 123  30             20      50

There is only 120 in revenue, but if we take the revenue if the acct is in payor or shipper as your originally asked, in this case, we get 200 as a grand total.
You need to decide on how to split the revenue if an both the shipper and payer are on separate accounts but both in the list you are processing.


The total revenue is 120, but
0
 
LVL 1

Accepted Solution

by:
Dromame earned 2000 total points
ID: 36526948
Euro5,


lludden has a point. If you want the total revenue for all accounts to still add up to the original $120 you have to decide which source you are accumulating. If you only care about the sum of revenue for individual accounts for a month the SQL listed below will generate the data you seek.
-- Build your temp table and insert the shipper_acct revenue:
CREATE TABLE tmp_tbl
AS (SELECT month_num, shipper_acct AS acct, revenue
FROM DB
WHERE shipper_acct IN (123,456,789)) WITH DATA;

-- Now insert the payor_acct revenue where it does not duplicate the shipper revenue
INSERT INTO tmp_tbl
SELECT month_num, payor_acct, revenue
FROM DB
WHERE payor_acct IN (123,456,789)
AND payor_acct <> shipper_acct;

--Finally sum the revenue by month and acct
SELECT month_num, acct, SUM(revenue) as rev
 FROM tmp_tbl
GROUP BY (month_num,acct)
ORDER BY (month_num);
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question