Solved

SQL:Inner Join Date issue

Posted on 2014-01-09
12
417 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 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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