Re: A subquery? Not sure if this is doable?

Michel,

Thank you again. The table with weekly data has the index on the PK which
is date and Cusip, same for table with monthly data (Date and Cusip).

Weekly data +1 mil rows
Monthly data 250 thou rows

The 2nd version of your example does work to some degree. It does return
the weekly series and the repeating monthly figure. The SQL I am using is:

SELECT tbla.PEDATE, tbla.CUSIP, tbla.price, tbla.shs, tblb.netsales
FROM EPS_HISTORY AS tbla LEFT JOIN PS AS tblb ON (tbla.cusip=tblb.cusip) and
(tbla.pedate<=tblb.psdate) AND Year(tbla.pedate) = Year(tblb.psdate) AND
Month(tbla.PEDATE)=Month(tblb.PSdate)
WHERE (tbla.PEDATE>=DateAdd("m",-12,Date()))
ORDER BY tbla.CUSIP, tbla.PEDate;

The problem is that the sales are from that week's month-end point, not from
the previous month-end point. So for all the weeks in Jan-06, the sales
returned are from 1/31/06, not 12/30/05. I'm guessing this is a function of
the part

Year(tbla.pedate) = Year(tblb.psdate) AND
Month(tbla.PEDATE)=Month(tblb.PSdate)

but if I try and change the <>= parts, I no longer get the unique row per
week, the weeks repeat depending on how many there for each month.

Can it be modified so that it looks backward, and not forward?

Thx.

Kohai
"Michel Walsh" wrote:

Hi,


That depends on how many records you have to deal with, and if there are
indexes.


If you are sure there is only one record per month in the lessFrequent
table, you can try:

SELECT a.date, a.title, a.data, b.data
FROM moreFrequent As a LEFT JOIN lessFrequent AS b
ON a.title=b.title AND a.date >= b.date AND Year(a.date)=Year(b.date)
AND Month(a.date) = Month(b.Date)


Hoping it may help,
Vanderghast, Access MVP


"kohai" <kohai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3CFF299F-4C4F-4CDB-9915-416EB22DB8D2@xxxxxxxxxxxxxxxx
Michel,

Thanks for the speedy reply. I tried to use the SQL example you wrote and
my computer hangs and hangs and hangs.

This example should be returning each weeks data point for each company
and
the previous month-ends data point in the other table such that the
monthly
point will repeat for each week until a new month starts?


"Michel Walsh" wrote:

Hi,

SELECT a.date, a.title, LAST(a.data), LAST(b.data)
FROM (moreFrequent As a INNER JOIN lessFrequent as b
ON a.title=b.title AND a.date >= b.date) INNER JOIN lessFrequent
As
c
ON a.title=c.title AND a.date >= c.date
GROUP BY a.date, a.title, b.date
HAVING b.date =MAX(c.date)



should do. I hope you have indexes on title and date fields.

For a given a.date, a.title, b values are about all records with a date
less or equal to a.date.
For a given group, a.date, a.title, b.date, c values are about all
records
with a date less of equal to b.date. MAX(c.date) gives us the closest
one,
<= a.date. so, keeping b.date=MAX(c.date) limit the records from the
JOINs
to those that correspond to the closest b.date <=a.date


Hoping it may help,
Vanderghast, Access MVP


"kohai" <kohai@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6C8E57ED-61CE-43A4-A1F2-5706AAAC866D@xxxxxxxxxxxxxxxx
Hi,

Any help out there that the masters can shed some light on would be
greatly
appreciated.

I have tables where data is stored based on different
frequencies(Daily,
Weekly, Monthly). The primary keys are the Date and the Company ID.

What I am trying to figure out is whether there is any way to pull data
from
a table of greater date frequency and have the data from the table with
less
frequency such that the table with less frequency will repeat its value
until
its next point in time.

I know that joins would bring back only where the values are equal and
I
don't want to have to store the data of shorter frequency on a more
frequent
basis since it will be repeating and violate cardinal rule of db
design.

For Example:

More frequent:
1/5/05 ABC 5.25
1/5/05 DEF 2.41
1/5/05 XYZ 9.82
1/12/05 ABC 4.22
1/12/05 DEF 3.12
1/12/05 XYZ 8.88
....
2/1/05 ABC 4.25
2/1/05 DEF 6.41
2/1/05 XYZ 10.82
2/8/05 ABC 7.22
2/8/05 DEF 1.12
2/8/05 XYZ 9.99

Less Frequent:
12/31/04 ABC 52.5
12/31/04 DEF 31.9
12/31/04 XYZ 44.4
1/31/05 ABC 50.5
1/31/05 DEF 30.9
1/31/05 XYZ 49.4

My goal would be to have the weekly data point divided by the most
recent
month end point for each week.

1/5 05 ABC 5.25 / 52.5
1/5/05 DEF 2.41 / 31.9
1/12/05 ABC 4.25 / 52.5
1/12/05 DEF 3.12 / 31.9
2/1/05 ABC 4.25 / 50.5
2/1/05 DEF 6.41 / 30.9
2/8/05 ABC 7.22 / 50.5
etc.........

Is something like this feasable with some type of subquery? Do I need
a
table where these repeating dates are set up?

Your help is greatly appreciated.

Thank you.

kohai






.

Relevant Pages