DISTINCT query?

[maxie3]

I've got a problem for you Access/SQL gurus out there...

I am using Access 2000.

This problem involves Table1 with these columns: date, ticker, close. This table is updated daily with the closing prices of various stocks.

I am trying to develop a query that prompts me for a date (we can assume that the date entered will be valid, in all respects) and then proceeds to give me the 3 day moving average for each ticker in Table1.

The 3 day moving average is calculated as follows: for Day 4, it would be: (Day 4's closing price + Day 3's closing price + Day 2's closing price)/3.

The 3 day moving average for the next day (Day 5) would be: (Day 5's closing price + Day 4's closing price + Day 3's closing price)/3.

I don't think this can be done with operations performed on dates, since stocks don't trade on Saturdays, Sundays nor on some holidays and thus don't have prices on those days. I believe a better approach would be to use the TOP function or something similar to get the last 3 records for each ticker. But I can't figure out how to do this correctly.

So, ideally the output would look something like this:

InputtedDate Ticker1 Ticker1MovingAverage
InputtedDate Ticker2 Ticker2MovingAverage
InputtedDate Ticker3 Ticker3MovingAverage
etc...

Thanks in advance for any help,

maxie3

 

[BSman]

Create a grouping query for which you group by ticker, sum the close, and include a calculated column (your moving average) that is the sum/3.

Next, we assume that the date will include all weekdays and no weekend dates. Given that assumption (which conveniently ignores holidays), you need to include in the grouping query a criteria for the date that keeps the date within the correct range.

The easiest way to do this is to use a form to enter the date and then open the query. Have two controls on the form. One, txtEndingDate, is where the user enters the ending date. The second, txtStartingDate is one in which you calculate a starting date by including code for the onUpdate property of txtEndingDate. The code would do something like this (you can look up the specific details of the syntax in Access help):

Select Weekday(me.txtEndingDate)
' deduct 2 from the ending date to get the earlier date
' when the date is a Wednesday through a Friday
Case 4 to 6 'Wednesday through Friday
me.txtStartingDate = dateadd("d",me.txtEndingDate,-2)
' deduct 4 from the ending date to get the earlier date
' when the date is Sunday through Tuesday to account for
' Saturday and Sunday not being included in the dates
Case 1 to 3 'Sunday through Tuesday
me.txtStartingDate = dateadd("d",me.txtEndingDate,-4)
' deduct 3 from the ending date to get the earlier date
' when the date is a Saturday to account for
' Saturday not being included in the dates
Case 7 'Saturday
me.txtStartingDate = dateadd("d",me.txtEndingDate,-3)

Your query would then include the criteria:

Date Between Forms!MyForm!txtStartingDate And Forms!MyForm!txtEndingDate

You could include this kind of testing right in the criteria statement in your query, but it would be very involved and probably messy. But if you did, the same concepts would apply. You'd just use nested IIF() functions.

By the way, while you can have criteria in Access for a column which is not one of the grouping (or sum) columns returned by the grouping query, normally for grouping queries you are limited to using criteria only for returned columns. In that kind of situation you would create two queries, the first a select query with the date criteria mentioned. The second would be the grouping query that would actually calculated the moving averages, but would use the first query as its source (and therefore would only see the 3 days of data).

 

[jhall156]

I would first create this user defined function

CREATE FUNCTION GetBeginDate (@InDate as datetime)
RETURNS datetime AS
BEGIN
DECLARE @StartDate datetime
SELECT @StartDate = DateAdd(d, -1 * (2 + CASE
WHEN DatePart(dw,@InDate) BETWEEN 4 AND 6 THEN 0
WHEN DatePart(dw,@InDate) = 7 THEN 1
ELSE 2 END), @InDate)
RETURN @StartDate
END



Then I would write the Stored Procedure something like
CREATE PROCEDURE usp_MovingAvg @InDate datetime AS
DECLARE @StartDate datetime

SELECT CONVERT(varchar(10), [Date], 21) AS InDate,
SUM(CASE WHEN Ticker = 'Ticker1' THEN [Close] ELSE 0 END) AS Ticker1,
SUM(CASE WHEN Ticker = 'Ticker2' THEN [Close] ELSE 0 END) AS Ticker2,
SUM(CASE WHEN Ticker = 'Ticker3' THEN [Close] ELSE 0 END) AS Ticker3
FROM Table1
WHERE [Date] >= @StartDate
GROUP BY CONVERT(varchar(10), [Date], 21)


I haven't tested any of it of course but you could give it a shot

JHall

 

[jhall156]

Sorry, I forgot to divide by three , how about...
I would first create this user defined function

CREATE FUNCTION GetBeginDate (@InDate as datetime)
RETURNS datetime AS
BEGIN
DECLARE @StartDate datetime
SELECT @StartDate = DateAdd(d, -1 * (2 + CASE
WHEN DatePart(dw,@InDate) BETWEEN 4 AND 6 THEN 0
WHEN DatePart(dw,@InDate) = 7 THEN 1
ELSE 2 END), @InDate)
RETURN @StartDate
END



Then I would write the Stored Procedure something like
CREATE PROCEDURE usp_MovingAvg @InDate datetime AS
DECLARE @StartDate datetime

SELECT CONVERT(varchar(10), [Date], 21) AS InDate,
SUM(CASE WHEN Ticker = 'Ticker1' THEN [Close] ELSE 0 END)/3 AS Ticker1,
SUM(CASE WHEN Ticker = 'Ticker2' THEN [Close] ELSE 0 END)/3 AS Ticker2,
SUM(CASE WHEN Ticker = 'Ticker3' THEN [Close] ELSE 0 END)/3 AS Ticker3
FROM Table1
WHERE [Date] >= @StartDate
GROUP BY CONVERT(varchar(10), [Date], 21)


I haven't tested any of it of course but you could give it a shot

JHall

 

[maxie3]

Well, I was hoping to solve this problem without creating new procedures, functions, etc.

For what it is worth, this is what I had so far....

SELECT Sum([table1].[close]) AS sumOfClose, Avg([table1].[close]) AS avgOfClose, inputdate AS MADate, [table1].[ticker]
FROM table1
WHERE (((table1.Date) In (SELECT top 3 date from table1 WHERE date <= inputdate ORDER BY date DESC)))
GROUP BY [table1].[ticker];

It works when I have only 1 stock (1 ticker). But when I add the information for a 2nd stock, then it won't work unless I change the &quot;3&quot; to a &quot;6&quot;.

Obviously, I'd like to make it so that the query's logic is independent of how many tickers I have in the table. (So, I can add stock information without having to worry about updating the query.)

I appreciate the responses so far....but is there anyway to do it within a normal SELECT statement using the pre-defined Access functions?

 

[BSman]

Create two tables like I suggested, but with this difference. For the first query (qselDates) just return the top three dates for one ticker (and this is the table where you have the user entered ending date).

The next table will join qselDates with your data table where the date in your data table matches the dates returned in the qselDates table. This table will be a grouping query, group by ticker, sum the close values, and add a column that looks like this in the query definition grid:

MovingAverage: [Close]/3

That will do it.

 

[maxie3]

A friend pointed out that if I add the DISTINCT in the subquery, everything would work....so here's the final query:

SELECT Sum(close), Avg(close), inputdate, ticker
FROM table1
WHERE date in (SELECT distinct top 2 date from table1 WHERE date <= inputdate ORDER BY date DESC)
GROUP BY ticker;

Problem is I'm not sure why it works!

maxie3

 

[r937]

that query should not run in access, because the GROUP BY is not the same as the non-aggregates in the SELECT

and it should be top 3, no?

and finally, the subquery should be correlated, otherwise it will pick the top x dates overall, regardless whether, on a ticker by ticker basis, there's a row for that date

[tt]SELECT Sum(close)
, Avg(close)
, ticker
FROM table1 XX
WHERE datefield in
( SELECT top 3 date
from table1
WHERE datefield <= inputdate
and ticker = XX.ticker
ORDER BY datefield DESC )
GROUP
BY ticker[/tt]

rudy