Solved

Get latest date, and previous

Posted on 2013-06-05
12
361 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
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 10

Expert Comment

by:bigbigpig
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:metropia
Comment Utility
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
Comment Utility
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
Comment Utility
I think this is what you are looking for..

Something like:

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

HTH,

Kent
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:metropia
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 150 total points
Comment Utility
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
Comment Utility
Thank you everyone.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now