Help! Dates and SQL

Hi
I would like to create a SP where it will populate TableA based from TableB.
TableB will be populated on a monthly basis using a DTS and within that I
would like to run the SP to populate TableA.

Can someone here please help me create the sql statements as a starting
point.

TIA!
Bob

TableB (source)
from_date to_date curr_code ex_rate
1/1/2004 1/10/2004 CAD .75000
1/11/2004 1/16/2004 CAD .74321
1/17/2004 2/4/2004 CAD .72222
2/5/2004 2/20/2004 CAD .71111
2/21/2004 2/28/2004 CAD .77888
3/1/2004 3/3/2004 CAD .79002
3/4/2004 3/14/2004 CAD .76803
3/15/2004 3/23/2004 CAD .70022
3/24/2004 4/2/2004 CAD .73365
etc...

TableA (destination):
date curr_code ex_rate
1/2004 CAD 0.738477 calculation:(.75000+
..74321+.72222) / 3
2/2004 CAD 0.737403
(.72222+.71111+.77888) / 3
3/2004 CAD 0.74798
(.79002+.76803+.70022+.73365) / 4
etc.."B" <no_spam@.no_spam.com> wrote in message
news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> Hi
> I would like to create a SP where it will populate TableA based from
> TableB.
> TableB will be populated on a monthly basis using a DTS and within that I
> would like to run the SP to populate TableA.
> Can someone here please help me create the sql statements as a starting
> point.
> TIA!
> Bob
>
> TableB (source)
> from_date to_date curr_code ex_rate
> 1/1/2004 1/10/2004 CAD .75000
> 1/11/2004 1/16/2004 CAD .74321
> 1/17/2004 2/4/2004 CAD .72222
> 2/5/2004 2/20/2004 CAD .71111
> 2/21/2004 2/28/2004 CAD .77888
> 3/1/2004 3/3/2004 CAD .79002
> 3/4/2004 3/14/2004 CAD .76803
> 3/15/2004 3/23/2004 CAD .70022
> 3/24/2004 4/2/2004 CAD .73365
> etc...
> TableA (destination):
> date curr_code ex_rate
> 1/2004 CAD 0.738477 calculation:(.75000+
> .74321+.72222) / 3
> 2/2004 CAD 0.737403
> (.72222+.71111+.77888) / 3
> 3/2004 CAD 0.74798
> (.79002+.76803+.70022+.73365) / 4
> etc..
>

Here's one possible solution. In future, please post CREATE TABLE and INSERT
statements for your tables and data - other people can then simply cut and
paste into Query Analyzer, and we don't have to guess about data types,
keys, constraints etc.:

http://www.aspfaq.com/etiquette.asp?id=5006

Note that 'date' is a reserved keyword, so you should avoid using it as a
column name - I've used start_of_month instead. See "Reserved Keywords" in
Books Online.

Simon

create table b (
from_date datetime not null,
to_date datetime not null,
curr_code char(3) not null,
ex_rate decimal(6,5) not null,
constraint pk_b primary key (from_date, to_date, curr_code)
)
go
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040101', '20040110', 'CAD', 0.75000)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040111', '20040116', 'CAD', 0.74321)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040117', '20040204', 'CAD', 0.72222)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040205', '20040220', 'CAD', 0.71111)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040221', '20040228', 'CAD', 0.77888)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040301', '20040303', 'CAD', 0.79002)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040304', '20040314', 'CAD', 0.76803)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040315', '20040323', 'CAD', 0.70022)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040324', '20040402', 'CAD', 0.73365)
go

select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate) as
'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime)
) dt
join b
on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
group by dt.start_of_month
go

drop table b
go|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
>> Hi
>> I would like to create a SP where it will populate TableA based from
>> TableB.
>> TableB will be populated on a monthly basis using a DTS and within that I
>> would like to run the SP to populate TableA.
>>
>> Can someone here please help me create the sql statements as a starting
>> point.
>>
>> TIA!
>> Bob
>>
>>
>> TableB (source)
>> from_date to_date curr_code ex_rate
>> 1/1/2004 1/10/2004 CAD .75000
>> 1/11/2004 1/16/2004 CAD .74321
>> 1/17/2004 2/4/2004 CAD .72222
>> 2/5/2004 2/20/2004 CAD .71111
>> 2/21/2004 2/28/2004 CAD .77888
>> 3/1/2004 3/3/2004 CAD .79002
>> 3/4/2004 3/14/2004 CAD .76803
>> 3/15/2004 3/23/2004 CAD .70022
>> 3/24/2004 4/2/2004 CAD .73365
>> etc...
>>
>> TableA (destination):
>> date curr_code ex_rate
>> 1/2004 CAD 0.738477 calculation:(.75000+
>> .74321+.72222) / 3
>> 2/2004 CAD 0.737403
>> (.72222+.71111+.77888) / 3
>> 3/2004 CAD 0.74798
>> (.79002+.76803+.70022+.73365) / 4
>> etc..
>>
>>
>>
>>
> Here's one possible solution. In future, please post CREATE TABLE and
> INSERT statements for your tables and data - other people can then simply
> cut and paste into Query Analyzer, and we don't have to guess about data
> types, keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go

<snip
Oops - the query I posted before won't handle multiple currencies correctly.
This version should.

select dt.start_of_month, dt.curr_code, avg(b.ex_rate) as 'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', curr_code, count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime),
curr_code
) dt
join b
on dt.curr_code = b.curr_code and
(
dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
)
group by dt.start_of_month, dt.curr_code

Simon|||The solution you recently posted is exactly what I am looking for.

And I am aware that "date" is a reserved word, i quickly sent the original
post out of haste.

Many thanks for your time!
Bob

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> > Hi
> > I would like to create a SP where it will populate TableA based from
> > TableB.
> > TableB will be populated on a monthly basis using a DTS and within that
I
> > would like to run the SP to populate TableA.
> > Can someone here please help me create the sql statements as a starting
> > point.
> > TIA!
> > Bob
> > TableB (source)
> > from_date to_date curr_code ex_rate
> > 1/1/2004 1/10/2004 CAD .75000
> > 1/11/2004 1/16/2004 CAD .74321
> > 1/17/2004 2/4/2004 CAD .72222
> > 2/5/2004 2/20/2004 CAD .71111
> > 2/21/2004 2/28/2004 CAD .77888
> > 3/1/2004 3/3/2004 CAD .79002
> > 3/4/2004 3/14/2004 CAD .76803
> > 3/15/2004 3/23/2004 CAD .70022
> > 3/24/2004 4/2/2004 CAD .73365
> > etc...
> > TableA (destination):
> > date curr_code ex_rate
> > 1/2004 CAD 0.738477 calculation:(.75000+
> > .74321+.72222) / 3
> > 2/2004 CAD 0.737403
> > (.72222+.71111+.77888) / 3
> > 3/2004 CAD 0.74798
> > (.79002+.76803+.70022+.73365) / 4
> > etc..
> Here's one possible solution. In future, please post CREATE TABLE and
INSERT
> statements for your tables and data - other people can then simply cut and
> paste into Query Analyzer, and we don't have to guess about data types,
> keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go
> select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate)
as
> 'ex_rate'
> from
> (
> select cast(convert(char(6), from_date, 112) + '01' as datetime) as
> 'start_of_month', count(*) as 'num'
> from b
> group by cast(convert(char(6), from_date, 112) + '01' as datetime)
> ) dt
> join b
> on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
> datetime)
> or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
> datetime)
> group by dt.start_of_month
> go
> drop table b
> go