CODEWITHSUNDEEP
sqldatems-access
asjhdbashjdbasjhdbhjb
asjhdbashjdbasjhdbhjb

Reputation: 68

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.

Upvotes: 0

Views: 414

Answers (2)

Applecore
Applecore

Reputation: 4099

What you are looking to get is the last stock value before a week/month ending date. So, in each case you need to find the date that this is. In your case for week-end it would be:

DateAdd("d",5-Weekday(StockDate,6),StockDate)

And for the month-end it would be:

DateSerial(Year(StockDate),Month(StockDate)+1,-1)

You would then use these two results in queries to get the value immediately before the selected date:

SELECT DISTINCT 
    S.StockCode, 
    DateAdd("d",5-Weekday(S.StockDate,6),S.StockDate) AS WeekEnding,
    (SELECT TOP 1 S1.StockClose FROM tblStock AS S1 WHERE S.StockCode=S1.StockCode AND S1.StockDate<=DateAdd("d",5-Weekday(S.StockDate,6),S.StockDate) ORDER BY S1.StockDate DESC) AS WeekClose
FROM tblStock AS S

And

SELECT DISTINCT 
    S.StockCode, 
    DateSerial(Year(S.StockDate),Month(S.StockDate)+1,-1) AS MonthEnding,
    (SELECT TOP 1 S1.StockClose FROM tblStock AS S1 WHERE S.StockCode=S1.StockCode AND S1.StockDate<=DateSerial(Year(S.StockDate),Month(S.StockDate)+1,-1) ORDER BY S1.StockDate DESC) AS MonthClose
FROM tblStock AS S;

Note that I have renamed your fields, as Date is a reserved word in Access.

Regards,

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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.

Upvotes: 1

Related Questions