Help! Dates and SQL

[B]

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..

[Simon Hayes]

"B" <no_spam@no_spam.com> wrote in message
news:8b********************@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]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:41**********@news.bluewin.ch...

"B" <no_spam@no_spam.com> wrote in message
news:8b********************@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

[B]

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" <sq*@hayes.ch> wrote in message
news:41**********@news.bluewin.ch...

"B" <no_spam@no_spam.com> wrote in message
news:8b********************@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