Access SQL - get field N records previous

[oracle128]

Assuming I have this table:
tbl_weekdates
weekdate_id
weeknum
weekending

eg.
id,weeknum,weekending
1,1,2007-01-06
2,2,2007-01-13
3,3,2007-01-20
...

I want to obtain:
id,weeknum,weekending,periodstart
1,1,2007-01-06,2007-01-06
2,2,2007-01-13,2007-01-06
3,3,2007-01-20,2007-01-13

Where periodstart is the minimum weekending of the last N rows ordered by weekending (ie. it's the weekending of the record N rows previous; in the above example, N=1).

Somehow I managed to do this with:SELECT w.*, (SELECT TOP 1 weekending FROM tbl_weekdates WHERE weekending<w.weekending ORDER BY weekending DESC) AS periodstart
FROM tbl_weekdates w;Which works fine and dandy, but breaks when I change 1 to anything else - "At most one record can be returned by this subquery". I tried:SELECT w.*, MIN(SELECT TOP 2 weekending FROM tbl_weekdates WHERE weekending<w.weekending ORDER BY weekending DESC) AS periodstart
FROM tbl_weekdates w;But it gives the same error. I also triedSELECT w.*, (SELECT MIN(weekdate) FROM (SELECT TOP 2 weekending FROM tbl_weekdates WHERE weekending<w.weekending ORDER BY weekending DESC)) AS periodstart
FROM tbl_weekdates w;But for some reason, adding that second level of nesting prevents the query from knowing what w.weekending is (it prompts for it).

My ultimate goal would be to get N=12, so I can do some stuff with 12 week averages. It must be done this way because:
-The week numbers don't match the calendar year
-Weeks may be skipped at the discression of the admin
-Bookings made during skipped weeks need to be included in the weekdate after the skip (eg. if 2 weeks are skipped over Xmas, any bookings which may occur in that time should be included in the week returned)
-weekdate_id cannot be assumed to always be sequential or in order, only the order of weekending can be relied on

Any ideas?

[smurfy]

I'm confused already:
"weekdate_id cannot be assumed to always be sequention or in order, only the order of weekending can be relied on"
What's it's point then. Weeknum seems to then serve the same purpose.

The point of an ID field being your primary key should be that it is an autonumber to keep the data in sequence.

Oh well, we all inherit dodgy databases.

I've got an SQL database with 90% of it's tables containing fields named after SQL keywords. like "From". Do you have any idea how hard that makes query writing? :rant over, sorry for hi-jacking:

http://msdn2.microsoft.com/en-us/library/ms189463.aspx
TOP denotes percentage, not a number of records (unless you happen to have exactly 100 records) :).

[degsy]

http://msdn2.microsoft.com/en-us/library/ms189463.aspx
TOP denotes percentage, not a number of records (unless you happen to have exactly 100 records) :).

???


Specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows.

It only returns a percentage if you use the % argument.



The error is because Min will only return one value, where as TOP can return a specified amount.


Have you tried adding aliases for your other fields. You only have aliases for w. and not the others. This is probably where Access is getting confused.

[oracle128]

"weekdate_id cannot be assumed to always be sequention or in order, only the order of weekending can be relied on"
What's it's point then. Weeknum seems to then serve the same purpose. Weeknum will presumed to be sequential, but of course it will go back to 1 every year, which means SQL coding for that is going to be difficult to impossible (though it was going to be another angle to look at if I couldn't do it this easier way).

The point of an ID field being your primary key should be that it is an autonumber to keep the data in sequence.

Oh well, we all inherit dodgy databases.The database is an original creation, but the problem is, weekdates are going to be added and deleted by an admin user. Deleted records means non-sequential IDs, and they may not be added in chronological order. For example, the weekdays may be setup for the next few years, but admin may decide to take an unexpected 1-week break over Easter, in which case the weekdates will be deleted, and bookings made during that period associated with the week back. This means that any reliance on "average cost of bookings between weekdate_id and weekdate_id-12" will then take the average of only 11 weeks. Hence the reason I need to order the records, and take the minimum date of the 12 records before the viewed week.

http://msdn2.microsoft.com/en-us/library/ms189463.aspx
TOP denotes percentage, not a number of records (unless you happen to have exactly 100 records)TOP only denotes percentage if you include the percentage option (note the square brackets)expression
Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

EDIT: Thanks degs I'll try that out. I'm confused by this though:The error is because Min will only return one value, where as TOP can return a specified amount. The error seems to state the exact opposite - it's fine when TOP returns 1, but not any more than that, which is why I though MIN (also only returning one value) would work. I only need the one value to be returned though; the minimum date of the previous 12 weekdates, so I can use it to establish a row-by-row time period with which I can sample records, and hence make a 12-week Moving Average.

[smurfy]

D'oh :)

How about using RANK?

[oracle128]

Have you tried adding aliases for your other fields. You only have aliases for w. and not the others. This is probably where Access is getting confused.No good degsy, I usedSELECT w.*, (SELECT MIN(tmp.weekending) FROM (SELECT TOP 2 w2.weekending FROM tbl_weekdates w2 WHERE w2.weekending<w.weekending ORDER BY w2.weekending DESC) AS tmp) AS periodstart
FROM tbl_weekdates AS w;It still doesn't recognize w.weekending.

How about using RANK?It doesn't appear RANK is supported in Access.

[smurfy]

Sorry, you use Access front ends much more than I do and I was just trying to act as sounding board.
Typical M$ only allowing half-pie SQL.
Now I'll look at this properly.

SELECT
w.*,
(SELECT MIN(tmp.weekending) FROM (SELECT TOP 2 w2.weekending FROM tbl_weekdates w2 WHERE w2.weekending<w.weekending ORDER BY w2.weekending DESC) AS tmp) AS periodstart

FROM tbl_weekdates AS w

Your double-nested SELECT is your problem
SELECT TOP 2 w2.weekending FROM tbl_weekdates w2 WHERE w2.weekending<w.weekending ORDER BY w2.weekending DESC
Select field FROM w2 WHERE w2.field<w.field
You haven't defined w within this query.

[oracle128]

Select field FROM w2 WHERE w2.field<w.field
You haven't defined w within this query.w is the alias of tbl_weekdates from the outer-most SELECT. It can be used in an inner SELECT, so why not an inner-inner SELECT? And if not, then how can I transfer a reference of w from the outer SELECT, to the inner SELECT, so it can be used in the inner-inner SELECT?

[smurfy]

I was thinking it has to do with implicit vs defined joins but it could just be misplaced bracketing:
SELECT
w.*,
(SELECT MIN(tmp.weekending) FROM (SELECT TOP 2 w2.weekending FROM tbl_weekdates w2 WHERE w2.weekending<w.weekending ORDER BY w2.weekending DESC) AS tmp) AS periodstart

Your "AS tmp" definition is still within the SELECT tmp.weekending sub-query isn't it?

SELECT
w.*,
(SELECT MIN(tmp.weekending) FROM ((SELECT TOP 2 w2.weekending FROM tbl_weekdates w2 WHERE w2.weekending<w.weekending ORDER BY w2.weekending DESC) AS tmp)) AS periodstart

[oracle128]

That acts the same, still asks for w.weekending.

[smurfy]

Bugger. I'm stumped mate. I still go back to my thoughts above - Access allows nesting within it's sql but does that create implicit joins? I believe so.

Try writing the query with defined joins instead of nests?

[oracle128]

I tried writing it with a join when I first created it, and while I know that a table can easily be joined with itself, I couldn't figure it out how to do it given my specific needs (minimum value, TOP N, DESC order). A join probably is the solution, but I'm stuffed if I can figure out how to use one in this situation.

[smurfy]

How about a slightly different tangent altogether then for your inner nest ...
SELECT w.weekending
FROM tbl_weekdates w
where (select count(*) from tbl_weekdates x where w.weekdate > x.weekdate) = 12

(I think that syntax is right - no database handy to test that)

[oracle128]

No good.SELECT w.*
FROM tbl_weekdates w
where (select count(*) from tbl_weekdates x where w.weekending > x.weekending) = 12By itself returns the 12th record, regardless of sort order. It could maybe be modified to return the -12th, but I don't think it'd work for records 1-12.
However, included with the other statement:SELECT w.*, (SELECT MIN(tmp.weekending) FROM (SELECT w.weekending
FROM tbl_weekdates w
where (select count(*) from tbl_weekdates x where w.weekending > x.weekending) = -12
ORDER BY year DESC) AS tmp) AS periodstart
FROM tbl_weekdates AS w;It leaves periodstart null. Using positive 12 instead of -12, periodstart is set to the 12th weekending and doesn't change.

[smurfy]

Yeah, instead of "= 12" your would have to say "= ((SELECT count(*) from tbl_weekdates) -12)" in which case, as you say, it would fail if count of weeks < 12 because it would give a negative integer. So you would have to complicate it with a SELECT CASE clause .... argh

[oracle128]

But it doesn't work anyway. As I said, it's always using the 12th weekending, it doesn't move with the records.