Solved

Get latest date, and previous

Posted on 2013-06-05
12
363 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dcount using a date in a table compared to today's date 3 32
Email Header Detail 12 58
Linked Server Issue with SQL2012 3 26
SQL Improvement  ( Speed) 14 30
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

825 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