A subquery? Not sure if this is doable?

[kohai]

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

 

[Michel Walsh]

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]

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]

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]

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]

Hi,


maybe an addition of one day for the infrequent table.. that would push the
data into the next month (ie, if originally the date is 31st of January
2006, adding 1 will make it the figure to use in February 2006:

ON a.title=b.title AND a.date >= b.date
AND Year(a.date)=Year(b.date+1)
AND Month(a.date) = Month(b.Date+1)


note that Year(date+1) first add one to the date, then take the year part,
somehow the same for Month(Date+1)

Sure, that makes a problem when a.date=b.date = end of the month. You
probably want use that figure for that week, not the value from the first of
the month. *if* so, then try

ON a.title=b.title AND a.date >= b.date AND Year(a.date)=Year(b.date+1+
Int(a.date=b.date) )
AND Month(a.date) = Month(b.Date+1 + int(a.date=b.date))


indeed, Int(a.date=b.date ) returns -1 if both dates are equal, 0 otherwise.
So the correction is as required under this assumption.



Hoping it may help,
Vanderghast, Access MVP