I need to add the last and second to last order date to a sql report. The last date is easy Max(OrderDate) but how do I find teh second to last date?
Will this work? max(OrderDate)-1 ?
Last Date and second to last date
-
- 3StarLounger
- Posts: 247
- Joined: 05 Nov 2012, 19:40
-
- 3StarLounger
- Posts: 247
- Joined: 05 Nov 2012, 19:40
Re: Last Date and second to last date
I solved this using SELECT Top 2 * FROM Orders orderby [Date] descending;
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Last Date and second to last date
I was composing this, but I see you already found the solution.
-------------
That would only work if the dates are guaranteed to be consecutive.
Otherwise, you can use
DMax("OrderDate", "TableOrQueryName", "OrderDate<#" & Format(DMax("OrderDate", "TableOrQueryName"), "mm/dd/yyyy") & "#")
Another option would be to create a separate Top 2 query based on the table, sorted descending on OrderDate.
-------------
That would only work if the dates are guaranteed to be consecutive.
Otherwise, you can use
DMax("OrderDate", "TableOrQueryName", "OrderDate<#" & Format(DMax("OrderDate", "TableOrQueryName"), "mm/dd/yyyy") & "#")
Another option would be to create a separate Top 2 query based on the table, sorted descending on OrderDate.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 247
- Joined: 05 Nov 2012, 19:40
Re: Last Date and second to last date
DMax("OrderDate", "TableOrQueryName", "OrderDate<#" & Format(DMax("OrderDate", "TableOrQueryName"), "mm/dd/yyyy") & "#")
How do I get the top 2 distinct order dates using this code?
How do I get the top 2 distinct order dates using this code?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Last Date and second to last date
You originally posted that you already had the most recent date. The DMax expression can be used to retrieve the next most recent date.
But a Top 2 query is easier.
But a Top 2 query is easier.
Best wishes,
Hans
Hans