?
Solved

JOIN in SQL Teradata

Posted on 2011-09-09
16
Medium Priority
?
542 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
  • 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 41

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 41

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 41

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…

588 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