Joining latest record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining latest record

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-09-24 : 11:44:01

Hello,

This simple query's results are what I want.

SELECT TOP(1) SALESLINE.ITEMID, SALESLINE.SALESPRICE, SALESLINE.DATAAREAID, SALESLINE.CREATEDDATE
FROM SALESLINE WHERE SALESLINE.ITEMID = '0000005' ORDER BY CREATEDDATE DESC

But now I want to left outer join into another larger query but I keep getting null values. I am basically trying to pull the latest sales record based on an item.

Here is the large query:

SELECT PRODTABLEJOUR.ITEMID, PRODTABLEJOUR.PRODID, PRODTABLEJOUR.TRANSDATE, PRODTABLEJOUR.QTYGOOD,
PRODTABLEJOUR.QTYERROR, PRODTABLEJOUR.ADJUSTMENT, PRODTABLEJOUR.AMOUNTFINANCIAL,
((PRODTABLEJOUR.AMOUNTFINANCIAL + PRODTABLEJOUR.ADJUSTMENT) / PRODTABLEJOUR.QTYGOOD) AS COSTPRICE,
INVENTITEMGROUP.JPCODE, INVENTTABLE.ITEMGROUPID, LASTSALESPRICE.SALESPRICE

FROM PRODTABLEJOUR

LEFT OUTER JOIN INVENTTABLE ON PRODTABLEJOUR.DATAAREAID = INVENTTABLE.DATAAREAID
AND PRODTABLEJOUR.ITEMID = INVENTTABLE.ITEMID

INNER JOIN INVENTITEMGROUP ON INVENTTABLE.ITEMGROUPID = INVENTITEMGROUP.ITEMGROUPID
AND INVENTTABLE.DATAAREAID = INVENTITEMGROUP.DATAAREAID

--This is where I join but it is resulting in null values.

LEFT OUTER JOIN
(SELECT top(1) SALESLINE.ITEMID, SALESLINE.SALESPRICE, SALESLINE.DATAAREAID
FROM SALESLINE ORDER BY CREATEDDATE DESC) AS LASTSALESPRICE

ON PRODTABLEJOUR.ITEMID = LASTSALESPRICE.ITEMID AND PRODTABLEJOUR.DATAAREAID = LASTSALESPRICE.DATAAREAID

WHERE PRODTABLEJOUR.DATAAREAID = @Company AND PRODTABLEJOUR.PRODID LIKE @Production AND
PRODTABLEJOUR.ITEMID LIKE @Item AND PRODTABLEJOUR.TRANSDATE >= @StartDate AND PRODTABLEJOUR.TRANSDATE <=@EndDate
AND PRODTABLEJOUR.JOURNALTYPE = '3'

ORDER BY PRODTABLEJOUR.ITEMID

I want to lookup the latest record in salesline according to each prodtablejour.itemid

Any ideas?

Thank you in advance

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-09-24 : 14:24:47

Try something like

LEFT OUTER JOIN SalesLine
ON PRODTABLEJOUR.ITEMID = SalesLine.ITEMID
AND PRODTABLEJOUR.DATAAREAID = SalesLine.DATAAREAID
AND SalesLine.CreatedDate = (SELECT MIN(SL2.CreatedDate) FROM SalesLine as SL2 WHERE SL2.ItemID = PRODTABLEJOUR.ITEMID)

I don't normally like filtering just on CreatedDate because it is not necessarily unique, so you might look for other information from the Primary Key of SalesLine that you can use in the subquery to make sure the records are lining up the way they should.

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-09-24 : 15:04:18

Thank you very much AjarnMark.

Now extra records because some have two salesline with same item! I guess that is why you mentioned filtering on created date. Not unique!

This forum is great.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-09-24 : 16:16:28

Yes, exactly.

Here's an idea... whatever the Primary Key field or fields are in SalesLine, include those in the subquery so it looks something like this:

LEFT OUTER JOIN SalesLine as SL1
ON PRODTABLEJOUR.ITEMID = SL1.ITEMID
AND PRODTABLEJOUR.DATAAREAID = SL1.DATAAREAID
AND SL1.CreatedDate =
(
SELECT MIN(SL2.CreatedDate)
FROM SalesLine as SL2
WHERE SL2.ItemID = PRODTABLEJOUR.ITEMID
AND SL2.PKField1 = SL1.PKField1 AND SL2.PKField2 = SL1.PKField2 AND...
)

that should help make sure that the subquery is tracking the same rows as the primary query for SalesLine.

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-09-24 : 16:51:30

Yes, good suggestion. Thank you.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-11-24 : 15:58:53

Hi,

I need to weave out only one record even though createddate field is not a primary or unique key.

SalesLine.CreatedDate = (SELECT MIN(SL2.CreatedDate) FROM SalesLine as SL2 WHERE SL2.ItemID = PRODTABLEJOUR.ITEMID)

I need the inner select stmt to return only the first record. I've tried with top(1) and limit with no success.

Any advice welcome! Thanks in advance.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-12-03 : 15:29:01

I have tried

SalesLine.CreatedDate = (SELECT top(1) MIN(SL2.CreatedDate) FROM SalesLine as SL2 WHERE SL2.ItemID = PRODTABLEJOUR.ITEMID)

Does not work

SalesLine.CreatedDate = (SELECT first(MIN(SL2.CreatedDate)) FROM SalesLine as SL2 WHERE SL2.ItemID = PRODTABLEJOUR.ITEMID)

Error: First is not a recognized function.

Even though I'm selecting a max of something that is not a primary key, there has to be a way to get this to only select one record...

I have tried AjarnMark idea but this did not work, it produced even more records!

Any help greatly appreciated. Please let me know if I am not clear on exactly what I want!

Thanks all.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-12-15 : 11:42:57

Going to bump this one more time! I'm thinking that it might not be possible, I have been searching for a solution for a while now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources