MAX(date) filter - 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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

MAX(date) filter

Author

Topic

Plaice
Starting Member

20 Posts

Posted - 2010-08-26 : 10:43:55

Hi all,

Hopefully a quick one, have the current working code:

SELECT        PART.ID, PART.DESCRIPTION, PART.STOCK_UM, PART.QTY_ON_HAND, PART.UNIT_MATERIAL_COST, PART.PRODUCT_CODE, 
                         MAX(INVENTORY_TRANS.TRANSACTION_DATE) AS Expr1, INVENTORY_TRANS.TYPE
FROM            PART LEFT OUTER JOIN
                         INVENTORY_TRANS ON PART.ID = INVENTORY_TRANS.PART_ID
WHERE        (PART.QTY_ON_HAND > 0)
GROUP BY PART.ID, PART.DESCRIPTION, PART.STOCK_UM, PART.QTY_ON_HAND, PART.UNIT_MATERIAL_COST, PART.PRODUCT_CODE, INVENTORY_TRANS.TYPE
ORDER BY PART.ID

Only have a slight issue. I'm getting two lines for each Part ID as the TRANSACTION.TYPE can either be I or O. I'm only concerned about the last transaction whether that be I or O. Can't quite figure about the adjustment on the query to show this.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 10:58:32

You will do yourself a big favor if you learn to indent your code properly.
Here is one solution.

SELECT		PART.ID,
		PART.[DESCRIPTION],
		PART.STOCK_UM,
		PART.QTY_ON_HAND,
		PART.UNIT_MATERIAL_COST,
		PART.PRODUCT_CODE, 
		INVENTORY_TRANS.TRANSACTION_DATE,
		INVENTORY_TRANS.[TYPE]
FROM            PART
LEFT JOIN	(
			SELECT	PART_ID,
				[TYPE],
				TRANSACTION_DATE,
				ROW_NUMBER() OVER (PARTITION BY PART_ID ORDER BY TRANSACTION_DATE DESC) AS RecID
			FROM	INVENTORY_TRANS
		) AS x ON x.PART_ID = PART.ID
			AND x.RecID = 1
WHERE		PART.QTY_ON_HAND > 0
ORDER BY	PART.ID

N 56°04'39.26"
E 12°55'05.63"

Plaice
Starting Member

20 Posts

Posted - 2010-09-01 : 06:35:01

Hi Peso thanks for the response.

Having a slight issue though, if I use your code then I get an error while trying to verify the statement:

The multi-part identifier "INVENTORY_TRANS.TRANSACTION_DATE" could not be bound.
The multi-part identifier "INVENTORY_TRANS.TYPE" could not be bound.

Have been playing around with both bits of code and still can't get it to work. It works fine with my initial code if I leave out the TYPE but unfortunately I need it for the report.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 08:52:22

For the two columns depicted in the error message, just change the prefix table name to the correct table name where TYPE and TRANSACTION_DATE columns are stored.

N 56°04'39.26"
E 12°55'05.63"

Plaice
Starting Member

20 Posts

Posted - 2010-09-01 : 11:31:42

Those are the correct tables names.

Although whilst double checking did get another error like:

OVER SQL not supported.

Plaice
Starting Member

20 Posts

Posted - 2010-09-02 : 10:12:09

Exact error is:

"The OVER SQL construct or statement is not supported."

So I guess the second isn't going to work in this instance so will have to find another way to do this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources