Solved

Get latest date, and previous

Posted on 2013-06-05
12
366 Views
Last Modified: 2013-06-07
Hello,

I would like to ask for help with a sql query that I need to use later in a report.

My table contains only 4 columns: Item, Item Description, Date, Price

The table gets updated weekly on Fridays. I need to retrieve the last two most recent Prices.

For example, if today I run the query, I would get all rows for 5/31/2013 and 5/24/2013

Hopefully to get some help with how to calculate this dates, or some direction where I can find some examples or information that can help me figure this out.

Thank you very much.
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 10

Expert Comment

by:bigbigpig
ID: 39223705
You could do a "select top 2 [item], [item description], [date], [price] sort by [date] desc".  Then do whatever in your where clause.
0
 
LVL 17

Assisted Solution

by:Kent Dyer
Kent Dyer earned 50 total points
ID: 39223709
Is this SSRS (SQL Server Reporting Services)?  Which is a different solution from Native SQL..

For SQL, you can use MAX/MIN in your SQL Syntax.

http://stackoverflow.com/questions/1110998/get-day-of-week-in-sql-2005-2008

HTH,

Kent
0
 

Author Comment

by:metropia
ID: 39223714
I tried that but I get top two rows and they both have the same date.
SELECT TOP 2 [Date]
FROM [dbo].[Price]
ORDER BY Date DESC;

Open in new window

0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:metropia
ID: 39223719
Right now I am writng the query directly on SSMS 2012. Thank you.
0
 
LVL 10

Assisted Solution

by:bigbigpig
bigbigpig earned 50 total points
ID: 39223729
If all you want are the last 2 dates:

SELECT DISTINCT TOP 2 [Date]
FROM [dbo].[Price]
ORDER BY Date DESC;

Open in new window

0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39223737
I think this is what you are looking for..

Something like:

http://www.w3resource.com/sql/aggregate-functions/max-date.php

HTH,

Kent
0
 

Author Comment

by:metropia
ID: 39223773
This is a representation of how the date would be for all the items and their prices for the last two most recent dates (in this case Friday)
10701          2013-05-31 00:00:00.000 GMO FREE LECITHIN                              2.7000000000000000000
10702          2013-05-31 00:00:00.000 LECITHIN                                           0.7000000000000000000
10724          2013-05-31 00:00:00.000 NATURAL COCOA 75/25 IC/INDO                        1.1000000000000000000
10725          2013-05-31 00:00:00.000 DARK ALKALIZED COCOA                               1.2000000000000000000
10726          2013-05-31 00:00:00.000 LIGHT ALKALIZED COCOA                              1.1000000000000000000
10727          2013-05-31 00:00:00.000 NATURAL COCOA                                      1.1000000000000000000
10728          2013-05-31 00:00:00.000 RED ALKALIZED COCOA                                1.3000000000000000000
10729          2013-05-31 00:00:00.000 BLACK COCOA                                        1.6000000000000000000
10730          2013-05-31 00:00:00.000 IVORY COAST HIGH ROAST LIQUOR                      2.2000000000000000000
10731          2013-05-31 00:00:00.000 CAROB POWDER                                       0.6000000000000000000
10701          2013-05-24 00:00:00.000 GMO FREE LECITHIN                                  2.7000000000000000000
10702          2013-05-24 00:00:00.000 LECITHIN                                           0.7100000000000000000
10724          2013-05-24 00:00:00.000 FUDGIE NATURAL COCOA                               1.1000000000000000000
10725          2013-05-24 00:00:00.000 DARK DUTCH NATURAL                                 1.2000000000000000000
10726          2013-05-24 00:00:00.000 DUTCH COCOA                                        1.1000000000000000000
10727          2013-05-24 00:00:00.000 NATURAL COCOA                                      1.1000000000000000000
10728          2013-05-24 00:00:00.000 GARNET COCOA                                       1.3000000000000000000
10729          2013-05-24 00:00:00.000 BLACK COCOA                                        1.6000000000000000000
10730          2013-05-24 00:00:00.000 CHOCOLATE LIQUOR DISKS                             2.2000000000000000000
10731          2013-05-24 00:00:00.000 CAROB POWDER                                       0.6000000000000000000

Open in new window

0
 

Author Comment

by:metropia
ID: 39223792
I do not think that this query is giving me what I need, it does return the last two most recent dates, but only one row for each.

SELECT DISTINCT TOP 2 
	[Item]
,	[Date]
,	[Item Description]
,	[Price]
FROM 
	[dbo].[Price]
ORDER BY 
	[Date] DESC;

Open in new window

0
 

Author Comment

by:metropia
ID: 39223832
I think that using MAX is indeed part of the solution, and possibly also doing a self join to the table. My problem is that I do not know how to do it. Is there any chance you can help me through this? Thank you.
0
 

Author Comment

by:metropia
ID: 39223942
I figure it out:

SELECT 
	cp.[Commodity Code]
,	cp.[Date]
,	cp.[Description]
,	cp.[Market Price]
FROM 
	[dbo].[Commodity Price] AS cp 
WHERE 
	[Date] IN ( SELECT TOP 2 
					[Date] 
				FROM 
					[dbo].[Commodity Price]
				WHERE 
					[Commodity Code] = cp.[Commodity Code] 
				ORDER BY [Date] DESC
)
ORDER BY 
	[Commodity Code]
,	[Date] DESC

Open in new window


From article: Example #1 IN
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 150 total points
ID: 39224593
nicely done metropia

just as an observation, and by no means a criticism, what the above query does is a little different to what you originally asked for :)

compare:
I need to retrieve the last two most recent Prices.
to:
provide the full record for the 2 most recent dates of each [Commodity Code]

anyway, another way to achieve this would be by using the row_number() analytic function
SELECT 
     cp.Commodity Code]
,    cp.[Date]
,    cp.[Description]
,    cp.[Market Price]
FROM ( 
        SELECT 
             cpe.[Commodity Code]
        ,    cpe.[Date]
        ,    cpe.[Description]
        ,    cpe.[Market Price]
        ,    row_number() over (partition by cpe.[Commodity Code] order by cpe.[Date] DESC) as row_ref
        FROM 
            [dbo].[Commodity Price] AS cpe
     ) AS cp
WHERE 
     cp.row_ref < 3     
ORDER BY 
    cp.[Commodity Code]
,   cp.[Date] DESC 

Open in new window

using this function I think will be more efficient than the use of top2/order by in a correlated subquery. In essence row_number will produce its results in single pass of the data.
0
 

Author Closing Comment

by:metropia
ID: 39230307
Thank you everyone.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question