Solved

JOIN in SQL Teradata

Posted on 2011-09-09
16
527 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
2 Access tables, count verbiage used 6 31
Supress rows in SSRS table based on Like or Soundex 2 39
Strange msg in the SSMS pane 13 48
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 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

22 Experts available now in Live!

Get 1:1 Help Now