Solved

SQL:Inner Join Date issue

Posted on 2014-01-09
12
411 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
  • 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 73

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) 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 73

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 73

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 34
Webservices in T-SQL 3 30
Sql server, import complete table, using vb.net 9 32
transaction in asp.net, sql server 6 31
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

813 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

18 Experts available now in Live!

Get 1:1 Help Now