Solved

JOIN in SQL Teradata

Posted on 2011-09-09
16
528 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I update a 2nd table with what is inserted into the 1st? 5 35
export sql results to csv 6 35
Email Header Detail 12 53
TSQL - How to declare table name 26 29
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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