Max Date Problem - 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

Max Date Problem

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-11 : 16:21:50

Hi, I'having problems with my sql2008r2 view because I need just the MAX(date) but the query brings me all the records, exemple:

I have this fields :

artcode,PurchaseDate,PurchasePrice,CostPrice,SalePrice,Currency,XRate

I need all this fields but just the lastdate row if I just keep the artcode and purchasedate this bring me one row as I need it but when I put all the fields It brings me several rows and I don't know what I can Do.

This is my code, please help me !!

Thanks in advance...

SELECT     TOP (100) PERCENT artcode, MAX(PurchaseDate) AS LD, PurchasePrice, CostPrice, SalePrice, Currency, XRate
FROM         dbo.View_VK_ORDER_PRICES
WHERE     (artcode = '0330')
GROUP BY artcode, PurchasePrice, CostPrice, SalePrice, Currency, XRate

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 16:35:50

[code]
SELECT v.artcode, t.LD, v.PurchasePrice, v.CostPrice, v.SalePrice, v.Currency, v.XRate
FROM dbo.View_VK_ORDER_PRICES v
JOIN
(
SELECT artcode, MAX(PurchaseDate) AS LD,
FROM dbo.View_VK_ORDER_PRICES
WHERE artcode = '0330'
GROUP BY artcode
) t
ON v.artcode = t.artcode AND v.PurchaseDate = t.LD
[/code]

You can also use ROW_NUMBER() function to achieve this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-12 : 18:30:41

thanks tkizer, I followed your suggestions but it didn't works its pulling several rows and not just the max date.

Regards...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-12 : 18:49:28

Please show us some sample data and expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-13 : 00:38:32

Hi, I'm receiving some like this :

artcode|PurchaseDate|PurchasePrice|CostPrice|SalePrice|Currency|
100 | 2014-03-20 | 3.00 | 3.50 | 4.00 | usd |
100 | 2014-04-15 | 3.00 | 3.50 | 4.00 | usd |
101 | 2014-03-15 | 4.00 | 4.50 | 5.00 | mxn |
100 | 2014-06-12 | 3.00 | 3.50 | 4.00 | usd |

and I must receive only one row, the most recent date :

artcode|PurchaseDate|PurchasePrice|CostPrice|SalePrice|Currency|
100 | 2014-06-12 | 3.00 | 3.50 | 4.00 | usd |

Thanks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 04:00:00

[code]SELECT TOP(1) artcode,
PurchaseDate AS LD,
PurchasePrice,
CostPrice,
SalePrice,
Currency,
XRate
FROM dbo.View_VK_ORDER_PRICES
WHERE artcode = '0330'
ORDER BY PurchaseDate DESC;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-13 : 10:33:51

HiTkizer, I executed the code and the result was ok but is showing me just the max date of all the artcodes I have and I just need the Max date but of every artcode,sorry but I forgot to comment You this little detail :

artcode|PurchaseDate|PurchasePrice|CostPrice|SalePrice|Currency|
100 | 2014-03-20 | 3.00 | 3.50 | 4.00 | usd |
100 | 2014-04-15 | 3.00 | 3.50 | 4.00 | usd |
101 | 2014-03-15 | 4.00 | 4.50 | 5.00 | mxn |
101 | 2014-06-12 | 4.00 | 4.50 | 5.00 | usd |

and I must receive only one row, the most recent date of every artcode:

artcode|PurchaseDate|PurchasePrice|CostPrice|SalePrice|Currency|
100 | 2014-04-15 | 3.00 | 3.50 | 4.00 | usd |
101 | 2014-06-12 | 4.00 | 4.50 | 5.00 | usd |

Thanks for all Your valuable help

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-13 : 12:12:21

[code]SELECT *
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY artcodes ORDER BY PurchaseDate DESC) AS RowNum
FROM
dbo.View_VK_ORDER_PRICES
WHERE
artcode = '0330'
) AS T
WHERE
RowNum = 1[/code]

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-06-13 : 14:04:33

Thank You so much to all, it works perfectly.

Blessing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources