• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

SQL Query - Find Price with most recent date then join tables

I have to different tables in SQL:

First table (SecurityPrices):

SID   DataID  SecurityID       PriceDate                Price
26638      2      667        6/25/2007 12:00:00 AM         60.49      
26181      2      667        6/26/2007 12:00:00 AM         59.21      
25720      2      667        6/27/2007 12:00:00 AM         60.43      
25258      2      667        6/28/2007 12:00:00 AM         59.71      
22948   2       421      1/01/2007 12:00:00 AM    34.23
22948   2       421      1/02/2007 12:00:00 AM    34.24
22948   2       421      1/02/2007 12:00:00 AM    35.56

Second Table (PositionSummary):
PortfolioID    SecurityID     Quantity
1                  667                345
1                  421                125
1                  3                    4500
43                667                234
43                421                394
43                3                    48

I want to combine these two tables into one, but the Price value should have Max PriceDate (which would be the most recent price).  

I was able to come up with this query:

SELECT
  w.SecurityID, Price, q.Quantity, q.PortfolioID
FROM SecurityPrices w
INNER JOIN PositionSummary q on w.SecurityID = q.SecurityID
WHERE PriceDate =
  (SELECT MAX(PriceDate)
     FROM SecurityPrices AS w2
    WHERE w2.SecurityID = SecurityID)
AND Quantity > 0
ORDER BY SecurityID

Which combines the two tables together...but does not list SecurityID's that don't have an entry in SecurityPrices (for instance, SecurityID 3 is not listed, there is no entry in SecurityPrices for SecurityID=3)  

I want it to display Quantity for SecurityID and NULL for Price

Any ideas?
0
davery10
Asked:
davery10
  • 4
  • 2
1 Solution
 
JimBrandleyCommented:
This should do it:

SELECT select id, price, quantity, portifolioID
  FROM
(SELECT
  w.SecurityID, Price, q.Quantity, q.PortfolioID
FROM SecurityPrices w
INNER JOIN PositionSummary q on w.SecurityID = q.SecurityID
WHERE PriceDate =
  (SELECT MAX(PriceDate)
     FROM SecurityPrices AS w2
    WHERE w2.SecurityID = SecurityID)
AND Quantity > 0
UNION
SELECT
  SecurityID, null, Quantity, PortfolioID
FROM PositionSummary q on w.SecurityID = q.SecurityID
WHERE SecurityID NOT IN (SELECT  SecurityID FROM SecurityPrices)
  AND Quantity > 0)
ORDER BY SecurityID

Jim
0
 
davery10Author Commented:
Jim:

Thanks for your help. That query returns:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'on'.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'AND'.
0
 
JimBrandleyCommented:
Sorry - didn't quite finish cleaning it up.
SELECT select id, price, quantity, portifolioID
  FROM
(SELECT
  w.SecurityID, Price, q.Quantity, q.PortfolioID
FROM SecurityPrices w
INNER JOIN PositionSummary q on w.SecurityID = q.SecurityID
WHERE PriceDate =
  (SELECT MAX(PriceDate)
     FROM SecurityPrices AS w2
    WHERE w2.SecurityID = SecurityID)
AND Quantity > 0
UNION
SELECT
  SecurityID, null, Quantity, PortfolioID
FROM PositionSummary q
WHERE SecurityID NOT IN (SELECT  SecurityID FROM SecurityPrices)
  AND Quantity > 0)
ORDER BY SecurityID
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
JimBrandleyCommented:
Let's try again - I just noticed another error:
SELECT id, price, quantity, portifolioID
  FROM
(SELECT
  w.SecurityID, Price, q.Quantity, q.PortfolioID
FROM SecurityPrices w
INNER JOIN PositionSummary q on w.SecurityID = q.SecurityID
WHERE PriceDate =
  (SELECT MAX(PriceDate)
     FROM SecurityPrices AS w2
    WHERE w2.SecurityID = SecurityID)
AND Quantity > 0
UNION
SELECT
  SecurityID, null, Quantity, PortfolioID
FROM PositionSummary q
WHERE SecurityID NOT IN (SELECT  SecurityID FROM SecurityPrices)
  AND Quantity > 0)
ORDER BY SecurityID
0
 
davery10Author Commented:
Jim:

This worked:

SELECT
  w.SecurityID, Price, q.Quantity, q.PortfolioID
FROM SecurityPrices w
INNER JOIN PositionSummary q on w.SecurityID = q.SecurityID
WHERE PriceDate =
  (SELECT MAX(PriceDate)
     FROM SecurityPrices AS w2
    WHERE w2.SecurityID = SecurityID)
AND Quantity > 0

UNION
SELECT
  SecurityID, null, Quantity, PortfolioID
FROM PositionSummary q
WHERE SecurityID NOT IN (SELECT  SecurityID FROM SecurityPrices)
  AND Quantity > 0
ORDER BY SecurityID


Thanks very much for your help!
0
 
JimBrandleyCommented:
My pleasure. Good luck!

Jim
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now