Solved

SQL:Inner Join Date issue

Posted on 2014-01-09
12
407 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 31

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

12 Experts available now in Live!

Get 1:1 Help Now