Previous Date

[Skotor]

Have a database table containing sales collected per location.
[month] / [year] / [date] / [location #] / [sales]

When locations are collected the date of collection is keyed in. If that location is NOT collected it gets a date of 1/1/1901. [Month] and [Year] are always correct. I have no problem getting the last collection [date] using the LAST or MAX options for grouping in the query. But I have been unable to find a way to give me the previous collection date. Is there some equation I can build that would give me that date? Like if the last collection date was 9/22/2003, I would want the previous collection date that's not a 1/1/1901 collection. So if for August there was no collection, but for july there was, it would skip the 1/1/1901 entry for aug, and give me the 7/##/2003 date for july. Any tips or pointing in a general direction would be of great help. Thanks in advance!

 

[Nouba]

you could try this query

SELECT TOP 1
  [month]
, [year]
, [date]
, [location #]
, sales
FROM YourTable
WHERE [date] Not In (
  SELECT TOP 1 
    [date]
  FROM YourTable
  ORDER BY [date] DESC)
ORDER BY [date] DESC

 

[Skotor]

ehhh thats kinda what I was looking for. What that gave me was only locations entered on 8/31/03. Which is the last keyed in date. I have no problem getting that another way. What I'm looking for is a listing of all active locations, the last service date, and the prior service date. I can get all the information except the prior service date me self.

For each month of keyed in data I have my database generate a New_Sales table that contains [Month] / [Year] / [Date](default 1/1/1901) / [location #] / [Sales] for only open locations.

As sales roll in they update the date to what it should be and correct the data as needed. At then end of the month I append the data to the Master_Sales table.

I'm looking for a listing of all active locations and the last 2 ACTUAL service dates. The last 2 service dates that are not 1/1/1901.

So in the end I would want location number, prior service date, most recent service date. There is more info that will be added to the query also, but I can deal with that my self.

 

[namliam]

select max(serviceDate) from yourTable

Gets the most recent serviceDate in your Table

Now,
Select max(serviceDate) from YourTable where serviceDate <> (Select max(serviceDate) from yourtable)
Should get the second most recent serviceDate

You can play with where clauses if you need to... like so:
Select max(serviceDate) from YourTable where serviceDate <> (Select max(serviceDate) from yourtable where ....)
And ....

Regards

The Mailman

 

[Jon K]

I'm looking for a listing of all active locations and the last 2 ACTUAL service dates. The last 2 service dates that are not 1/1/1901.

Take a look at Rose's thread about "Top 3 invoices for each customer" for performance issues about using a subquery:-
http://www.access-programmers.co.uk/forums/showthread.php?threadid=55156

It seems what you are trying to do is similar to what Rose wants to do. So this query with a subquery may work:-

SELECT *
FROM [tableName] AS a
WHERE (Select Count(*) from [tableName] where [Location #]=a.[Location #] and [Date]>=a.[Date]) in (1,2) and not [Date]=#1/1/1901#;