Creating monthly records from effective date records

[karmaverma]

I need help with this apparently simple problem. I have a table with
the following records:

Effective_Date Commodity Price
10/1/2005 0
5/1/2006 2750
10/1/2006 0

Now I need to generate the following monthly records using the above
table:

Date Commodity Price
1/1/2006 0
2/1/2006 0
3/1/2006 0
4/1/2006 0
5/1/2006 2750
6/1/2006 2750
7/1/2006 2750
8/1/2006 2750
9/1/2006 2750
10/1/2006 0
11/1/2006 0
12/1/2006 0

I found a way to create this data but it is highly inefficient as it
involves 3 queries (nested). Since I am dealing with a significant
number of records, any ideas on how to get this done without multiple
queries will be highly appreciated.

Thanks,
Amit

[CDMAPoster]

ka********@gmail.com wrote:

I need help with this apparently simple problem. I have a table with
the following records:

Effective_Date Commodity Price
10/1/2005 0
5/1/2006 2750
10/1/2006 0

Now I need to generate the following monthly records using the above
table:

Date Commodity Price
1/1/2006 0
2/1/2006 0
3/1/2006 0
4/1/2006 0
5/1/2006 2750
6/1/2006 2750
7/1/2006 2750
8/1/2006 2750
9/1/2006 2750
10/1/2006 0
11/1/2006 0
12/1/2006 0

I found a way to create this data but it is highly inefficient as it
involves 3 queries (nested). Since I am dealing with a significant
number of records, any ideas on how to get this done without multiple
queries will be highly appreciated.

Thanks,
Amit

First try adding an auxiliary table (tblMonthsForCalculation) and
running the single query below to see if the speed problem goes away:

tblEffective
Effective_Date Date/Time Format m/d/yyyy
Commodity Price Currency

Effective_Date Commodity Price
10/1/05 $0.00
5/1/06 $2,750.00
10/1/06 $0.00

tblMonthsForCalculation
ID AutoNumber
theYear Long No Default
theMonth Long No Default

ID theYear theMonth
1 2005 10
2 2005 11
3 2005 12
4 2006 1
5 2006 2
6 2006 3
7 2006 4
8 2006 5
9 2006 6
10 2006 7
11 2006 8
12 2006 9
13 2006 10
14 2006 11
15 2006 12
16 2007 1

qryMonthlyPrice:
SELECT DateSerial(theYear,theMonth,1) AS theDate,
Last(tblEffective.[Commodity Price]) AS [Commodity Price] FROM
tblEffective, tblMonthsForCalculation WHERE
DateSerial([theYear],[theMonth],1)>=[Effective_Date] AND theYear = 2006
GROUP BY DateSerial(theYear,theMonth,1);

!qryMonthlyPrice:
theDate Commodity Price
1/1/06 $0.00
2/1/06 $0.00
3/1/06 $0.00
4/1/06 $0.00
5/1/06 $2,750.00
6/1/06 $2,750.00
7/1/06 $2,750.00
8/1/06 $2,750.00
9/1/06 $2,750.00
10/1/06 $0.00
11/1/06 $0.00
12/1/06 $0.00

The auxiliary table only needs to start at the last effective change
date prior to the year needed for the report. I couldn't tell from
your example so I am assuming that the change dates are always on the
first. Also, I did minimal testing. Note that GROUP BY seems to order
by date when a lone GROUP BY variable is a date. I don't think an
index on Effective_Date would hurt either.

James A. Fortune
CD********@FortuneJames.com

[Bookreader]

On 3 Feb 2006 15:30:44 -0800, ka********@gmail.com wrote:

I need help with this apparently simple problem. I have a table with
the following records:

Effective_Date Commodity Price
10/1/2005 0
5/1/2006 2750
10/1/2006 0

Now I need to generate the following monthly records using the above
table:

Date Commodity Price
1/1/2006 0
2/1/2006 0
3/1/2006 0
4/1/2006 0
5/1/2006 2750
6/1/2006 2750
7/1/2006 2750
8/1/2006 2750
9/1/2006 2750
10/1/2006 0
11/1/2006 0
12/1/2006 0

I found a way to create this data but it is highly inefficient as it
involves 3 queries (nested). Since I am dealing with a significant
number of records, any ideas on how to get this done without multiple
queries will be highly appreciated.

Thanks,
Amit

Instead of making the second table, you could just use something like
the following when you want to price something out.

SELECT price from table1 where priceDate < dteDate2 AND
priceDate > dteDate1
Or, use the same type of code in a loop to create the second table
using month-beginning or ending dates.

Also, in your tables Access won't like naming a column DATE, since
that is a reserved word. Much better to call it PriceDate or
something to make it clear what you mean.