• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

Get latest date, and previous

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
metropia
Asked:
metropia
  • 7
  • 2
  • 2
  • +1
3 Solutions
 
bigbigpigCommented:
You could do a "select top 2 [item], [item description], [date], [price] sort by [date] desc".  Then do whatever in your where clause.
0
 
Kent DyerCommented:
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
 
metropiaAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
metropiaAuthor Commented:
Right now I am writng the query directly on SSMS 2012. Thank you.
0
 
bigbigpigCommented:
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
 
Kent DyerCommented:
I think this is what you are looking for..

Something like:

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

HTH,

Kent
0
 
metropiaAuthor Commented:
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
 
metropiaAuthor Commented:
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
 
metropiaAuthor Commented:
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
 
metropiaAuthor Commented:
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
 
PortletPaulCommented:
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
 
metropiaAuthor Commented:
Thank you everyone.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now