Query end of week and end of month data

In an Access database, if I have some stock data that is in the format of

DateFormatted      DateSeconds        CodeName       Close     
2000-01-01          946684800          AAA           1.01
2000-01-02          946771200          AAA           1.02
2000-01-03          946857600          AAA           1.03
2000-01-04          946944000          AAA           1.04
2000-01-05          947030400          AAA           1.05
2000-01-08          947289600          AAA           1.06
.
.
.

and note that it goes from 05 to 08, as well as DateFormatted being a Short Text, rather than a Date. It seems like it will be almost impossible to query end of month (01-31) based upon a specific date in a WHERE clause, given the continuous date format. Instead, I am hoping that since such values may not exist in the database (as caused from weekends, public holidays, system errors etc), which will mean end of week may end on a Thursday, start of week may start on a Tuesday, end of month may be 3 days before the final day of the month, etc, there is a known method of obtaining such data in a query. How would this be possible?

Noting that in the above example, end of week will be 1.05 and end of month (if you keep continuing the trend of +0.01 for each day), will be 1.31.

Answer

If you want the last row in the data for each month, you can use a correlated subquery:

select s.*
from stocks as s
where s.date = (select max(s2.date)
                from stocks as s2
                where s2.code = s.code and
                      year(s2.date) = year(s.date) and
                      month(s2.date) = month(s.date)
               );

You can do something similar with datepart() for weeks.

Advertisement

Answer