Showing posts with label populate. Show all posts
Showing posts with label populate. Show all posts

Monday, March 12, 2012

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

Sunday, February 19, 2012

help with update query needed

Could anyone help me with an update query? I need to populate Table 1
(bookings) with data from Table 3 (defaults), via a joining field in Table 2
(enquiries). All fields are of type INT, using SQL Server 2000.
Table 1 (bookings):
id, t_val, q_val
Table 2 (enquiries):
id, booking_id, enq_type
Table 3 (defaults):
id, def_enq_type, def_t_val, def_q_val
Table 3 data:
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
The fields to be updated are 'bookings.t_val' and 'bookings.q_val', from
'defaults.def_t_val' and 'defaults.def_q_val' respectively. 'bookings.id'
relates to 'enquiries.booking_id', and 'enquiries.enq_type' to
'defaults.def_enq_type'. Just to make it more complex, 'booking_id' in
enquiries can have duplicates, in which case I want the one with the highest
'enquiries.id' (ie the most recent record).
Any help very gratefully received, I've been staring at it for hours.Tony,
Do you have any DDL for the Tables? And also some sample data for the
Tables?
Any how do you perceive Table 1(Bookings) to look like?
Thanks
Barry|||Try This:
declare @.bookings table(id int, t_val int, q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1, 1, 9)
insert @.enquiries values(2, 2, 11)
insert @.enquiries values(3, 1, 10)
insert @.bookings
select e.booking_id, d.def_t_val, d.def_q_val
from @.enquiries e
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
select * from @.bookings|||Thanks for the replies so far, but I'm still struggling.
I think I should have made it clearer that the table 'bookings' is already
populated with data, and that it is just 2 new columns (t_val, q_val) that
need populating (so obviously I've omitted all other fields not needed for
this update query).
So if 'bookings' currently looks like:
id, t_val, d_val
1, null, null
2, null, null
3, null, null
4, null, null
and 'enquiries' looks like:
id, booking_id, enq_type
1,2,9
2,4,10
3,1,12
4,1,11
5,3,9
(note more than one record for booking_id 1)
and as mentioned before, 'defaults' looks like:
id, def_enq_type, def_t_val, def_q_val
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
then the end result for 'bookings' should be:
id, t_val, d_val
1, 3, 2
2, 2, 1
3, 2, 1
4, 2, 2
Again, any help very gratefully received.
"JeffB" wrote:

> Try This:
> declare @.bookings table(id int, t_val int, q_val int)
> declare @.enquiries table(id int, booking_id int, enq_type int)
> declare @.defaults table(id int, def_enq_type int, def_t_val int,
> def_q_val int)
> insert @.defaults values(1,9,2,1)
> insert @.defaults values(2,10,2,2)
> insert @.defaults values(3,11,3,2)
> insert @.defaults values(4,12,1,2)
> insert @.enquiries values(1, 1, 9)
> insert @.enquiries values(2, 2, 11)
> insert @.enquiries values(3, 1, 10)
> insert @.bookings
> select e.booking_id, d.def_t_val, d.def_q_val
> from @.enquiries e
> inner join @.defaults d
> on e.enq_type = d.def_enq_type
> inner join (select booking_id, max(id) maxid from @.enquiries group by
> booking_id) x
> on e.booking_id = x.booking_id and e.id = x.maxid
> select * from @.bookings
>|||Try this:
declare @.bookings table(id int, t_val int, q_val int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
insert @.bookings values(1, null, null)
insert @.bookings values(2, null, null)
insert @.bookings values(3, null, null)
insert @.bookings values(4, null, null)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1,2,9)
insert @.enquiries values(2,4,10)
insert @.enquiries values(3,1,12)
insert @.enquiries values(4,1,11)
insert @.enquiries values(5,3,9)
update b
set b.t_val = d.def_t_val,
b.q_val = d.def_q_val
from @.bookings b
inner join @.enquiries e
on b.id = e.booking_id
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
where b.t_val is null and b.q_val is null
select * from @.bookings