SQL:Inner Join Date issue

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.
GNOVAKAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
Jesus RodriguezIT ManagerCommented:
select max(last_change_date) from Products where last_change_date <= a.Purchase_Date

you need to group by Product there
0
 
awking00Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Anthony PerkinsCommented:
So is this for Oracle or SQL Server?  I would guess Oracle.  The answer will be different in either case.
0
 
GNOVAKAuthor Commented:
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
 
Dale FyeConnect With a Mentor Commented:
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
 
sdstuberCommented:
>>> 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
 
awking00Commented:
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
 
Anthony PerkinsCommented:
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
 
sdstuberCommented:
>>> Except that substr() does not exist with T-SQL.


yes, in T-SQL  SUBSTR would be replaced with  SUBSTRING,
0
 
Anthony PerkinsCommented:
Exactly!
0
 
GNOVAKAuthor Commented:
Thanks everyone. Extremely helpful.
0
All Courses

From novice to tech pro — start learning today.