Solved

JOIN in SQL Teradata

Posted on 2011-09-09
16
525 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 39

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
 
LVL 39

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

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 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now