T-SQL Help

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

You did not mention the version of SQL Server you are using. Assuming that you are on SQL Server 2005, the following will work.

SET DATEFORMAT DMY;

SET NOCOUNT ON

DECLARE @t TABLE (Product CHAR(3), Price MONEY, Date DATETIME)

INSERT INTO @t (Product, Price, Date) SELECT 'HDD',200,'01/10/2008'

INSERT INTO @t (Product, Price, Date) SELECT 'HDD',100,'19/09/2008'

INSERT INTO @t (Product, Price, Date) SELECT 'HDD',50,'21/08/2007'

INSERT INTO @t (Product, Price, Date) SELECT 'FPP',0,'01/10/2008'

INSERT INTO @t (Product, Price, Date) SELECT 'FPP',100,'20/09/2008'

INSERT INTO @t (Product, Price, Date) SELECT 'SCR',500,'15/09/2008'

INSERT INTO @t (Product, Price, Date) SELECT 'SCR',100,'10/01/2008'

INSERT INTO @t (Product, Price, Date) SELECT 'HMR',0,'25/09/2008'

;WITH cte AS (

SELECT *, ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Date DESC) AS Seq

FROM @t

WHERE Price > 0

)

SELECT Product, Price, Date

FROM cte

WHERE seq = 1

/*

Product Price Date

------- --------------------- -----------------------

FPP 100.00 2008-09-20 00:00:00.000

HDD 200.00 2008-10-01 00:00:00.000

SCR 500.00 2008-09-15 00:00:00.000

*/

.

CrazyMan SSChampion Points: 12560 More actions · Answer 2

Thanks Jacob, i am using 2005, i need to last rtow with 0 as well, i cant exclude that ones, what shall i do for that, please advise

Cheers

🙂

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

So what is the logic?

if there is only one row, take it even if it is ZERO and other wise take only non-zero values?

.

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Hi Jacob

The logic is if there are more than one price, then get the latest price( Except 0), else get the price what ever it is ( including 0)

Cheers

🙂

jacob sebastian SSChampion Points: 11822 More actions · Answer 5

May be this one?