Solved

SQL:Inner Join Date issue

Posted on 2014-01-09
12
432 Views
Last Modified: 2014-01-21
I asked this as a slightly more global question, but it has now become a bit more involved.


I have two tables:
Transactions
Products

Products contains an ProductID, Product name, Last_Change_Date as well as other descriptors.  It can have multiple records for the same ID & Product Name.

Transactions contain a ProductID, category, Purchase_Date and various fields of information based on the sale of specific products.

I'm trying to query the Transactions and retrieve a count of transactions for a specific category along with the Product Name and other descriptors from the Products table.
Without dat consideration, someone helped and gave me this great solution:


Select Distinct count(a.ProductID)
,B.PRODUCT_NAME
,sum(a.product_cost)
from TRANSACTION_TABLE a
INNER JOIN (SELECT DISTINCT Product_ID, Product_Name FROM Products) as B
ON A.PRODUCT_ID = B.PRODUCT_ID
where substr(a.Category,1,6)='WDEFFD'
GROUP BY B.PRODUCT_NAME



But now I need to accomodate a Purchase Date as it relates to the Last_Change_Date of the product. i.e. For each Transaction Purchase Date, I need to find
the Product record where the Max(Last_Change_Date) is < = the Transaction Date.
I tried this but it doesnt like it very much (neither do I):

Select Distinct count(a.ProductID)
,B.PRODUCT_NAME
,sum(a.product_cost)
from TRANSACTION_TABLE a
INNER JOIN
(SELECT DISTINCT Product_ID, Product_Name FROM Products
 where last_change_date = (select max(last_change_date) from Products where last_change_date <= a.Purchase_Date) ) as B
ON A.PRODUCT_ID = B.PRODUCT_ID
where substr(a.Category,1,6)='WDEFFD'
GROUP BY B.PRODUCT_NAME

This is going to be extremely pertinent as we move to designing a Data Warehouse.
0
Comment
Question by:GNOVAK
[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
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39768824
select max(last_change_date) from Products where last_change_date <= a.Purchase_Date

you need to group by Product there
0
 
LVL 32

Expert Comment

by:awking00
ID: 39768889
SELECT DISTINCT COUNT(A.ProductID), b.Product_Name, SUM(A.Product_Cost)
FROM TRANSACTION_TABLE a
INNER JOIN
(SELECT DISTINCT Product_ID, Product_Name, MAX(LastChangeDate) as maxlcd
 FROM Products
 GROUP BY Product_ID, Product_Name) as b
ON a.Product_ID = b.Product_ID
AND b.maxlcd <= a.Purchase_Date
where substr(a.Category,1,6)='WDEFFD'
GROUP BY b.Product_Name
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 39768897
try this...


  SELECT COUNT(a.productid), b.product_name, SUM(a.product_cost)
    FROM transaction_table a
         INNER JOIN
         (SELECT DISTINCT product_id, product_name
            FROM (SELECT product_id,
                         product_name,
                         MAX(last_change_date) OVER (PARTITION BY product_id) maxdate
                    FROM products)) b
             ON a.product_id = b.product_id AND a.transaction_date >= b.maxdate
   WHERE a.category LIKE 'WDEFFD%'
GROUP BY b.product_name


Note, I changed the substr to be a LIKE in order take advantage of any indexes that might be on category

Also, I removed the outermost DISTINCT as it was redundant with the group by
0
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39768913
So is this for Oracle or SQL Server?  I would guess Oracle.  The answer will be different in either case.
0
 

Author Comment

by:GNOVAK
ID: 39768914
The problem appears to be that it doesnt recognize
"a.Purchase_Date" in the subquery.
It says its an invalid identifier.  I think the issue has something to do with the A.  not be recognized at that level.
I'm not sure though
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 100 total points
ID: 39768921
So, if I understand correctly, what you are really trying to capture is variations of the Product_Name over time?  Or are there other fields in your Products table that might change over time, causing changes in the [Last_Change_Date] field, but keeping ProductID and Product_Name the same?  Can you provide some sample data from the Products table?

I think it is going to need to read more like:

Select Distinct count(a.ProductID)
,B.PRODUCT_NAME
,sum(a.product_cost)
from TRANSACTION_TABLE a
INNER JOIN
(SELECT DISTINCT Product_ID, Product_Name
 FROM Products
 WHERE Products.last_change_date = (
SELECT max(C.last_change_date)
FROM Products C
WHERE C.ProductID = a.ProductID
AND C.last_change_date <= a.Purchase_Date)
) as B
ON A.PRODUCT_ID = B.PRODUCT_ID
where substr(a.Category,1,6)='WDEFFD'
GROUP BY B.PRODUCT_NAME

Note that I added a test for ProductID in the subquery that returns the max of [last_change_date].  Otherwise, you could get a change date that relates to another ProductID.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39768936
>>> The problem appears to be that it doesnt recognize "a.Purchase_Date" in the subquery.

It depends on how you nest them.

The query in ID: 39768897  should work in both oracle and sql server 2005 and above
the only difference being in Oracle you don't put "AS" when you alias a subquery
0
 
LVL 32

Expert Comment

by:awking00
ID: 39769064
FWIW, I assumed sql server since the askers original query aliased the subquery using "as B".

My question now is, "Is the date field in the transaction table called purchase_date or transaction_date"?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39769921
FWIW, I assumed sql server since the askers original query aliased the subquery using "as B".
Except that substr() does not exist with T-SQL.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39770219
>>> Except that substr() does not exist with T-SQL.


yes, in T-SQL  SUBSTR would be replaced with  SUBSTRING,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39772768
Exactly!
0
 

Author Closing Comment

by:GNOVAK
ID: 39798054
Thanks everyone. Extremely helpful.
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how the fundamental information of how to create a table.

615 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