adding more to the complex query

my head a splode.
so i'm finalizing the ugly transformation query, and i think i'm almost
done. there's only two problems that i'm having trouble with. first,
some ddl simplified samples for what i'm working with (please assume
foreign keys and such by context where possible, i want to keep this
brief - and once again CELKO, no need to harp on artificial keys):
create table orders (
new_id uniqueidentifier primary key not null,
old_id int identity (1, 1) unique not null
)
-- make four orders
insert into orders (new_id) values (newid())
insert into orders (new_id) values (newid())
insert into orders (new_id) values (newid())
insert into orders (new_id) values (newid())
create table old_transactions (
transactionid int identity (1, 1) primary key not null,
orderid int not null,
transactiontype int not null,
amount money not null,
transactiondate datetime not null
)
-- make eight OLD transactions for those orders
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
1, 100, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
1, 200, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
2, 100, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
2, 300, 5.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
3, 100, 10.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
3, 200, 15.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
4, 100, 5.00, getdate())
insert into old_transactions (
orderid, transactiontype, amount, transactiondate)
values (
4, 300, 0.00, getdate())
create table new_transactions (
transactionid uniqueidentifier primary key not null,
orderid uniqueidentifier not null,
productcode varchar(50) not null,
amount money not null,
transactiondate datetime not null
)
-- and here is the part i'm having trouble with
insert into new_transactions (
transactionid,
orderid,
productcode,
amount,
transactiondate)
select newid(), -- PROBLEM 1 (detailed below)
o.new_id,
case
when (select count(*) from old_transactions as ot2
where ot2.transactionid = ot1.transactionid and
ot2.transactiontype in (100, 200)
group by ot2.orderid) = 2
then 'PRODUCT 1'
when (select count(*) from old_transactions as ot2
where ot2.transactionid = ot1.transactionid and
ot2.transactiontype in (100, 300)
group by ot2.orderid) = 2
then 'PRODUCT 2'
end,
0.00, -- PROBLEM 2 (detailed below)
transactiondate
from old_transactions as ot1
join orders as o on ot1.orderid = o.old_id
PROBLEM 1: this statement inserts 1 row per transaction in the
old_transactions table. what i actually want is 1 row per distinct
orderid in the old_transactions table. as you might be able to tell
from the other logic in the statement, i'm collapsing all the
transactions in the old_transactions table for the same orderid into a
single new transaction in the new_transactions table.
PROBLEM 2: i need for the amount in the new_transactions table to be a
SUM aggregate of the amount in the old_transactions table for the
transactions of each orderid that i'm collapsing.
hopefully the ddl and the problem descriptions make sense. and thanks
in advance for any help!
jasonfirst, give the new_transactions.transactionid column a default of
newid(), then try
note: the transactiontype in (...) criteria assumes that there wouldn't
be duplicate transactiontypes per order
insert into new_transactions
(orderid, productcode, amount, transactiondate)
select o.new_id,
'PRODUCT 1',
sum(ot.amount),
min(ot.transactiondate)
from orders o
join old_transactions ot on o.old_id=ot.orderid
where ot.transactiontype in (100,200)
group by o.new_id
having count(*)=2
union all
select o.new_id,
'PRODUCT 2',
sum(ot.amount),
min(ot.transactiondate)
from orders o
join old_transactions ot on o.old_id=ot.orderid
where ot.transactiontype in (100,300)
group by o.new_id
having count(*)=2
jason wrote:

>my head a splode.
>so i'm finalizing the ugly transformation query, and i think i'm almost
>done. there's only two problems that i'm having trouble with. first,
>some ddl simplified samples for what i'm working with (please assume
>foreign keys and such by context where possible, i want to keep this
>brief - and once again CELKO, no need to harp on artificial keys):
>create table orders (
> new_id uniqueidentifier primary key not null,
> old_id int identity (1, 1) unique not null
> )
>-- make four orders
>insert into orders (new_id) values (newid())
>insert into orders (new_id) values (newid())
>insert into orders (new_id) values (newid())
>insert into orders (new_id) values (newid())
>create table old_transactions (
> transactionid int identity (1, 1) primary key not null,
> orderid int not null,
> transactiontype int not null,
> amount money not null,
> transactiondate datetime not null
> )
>-- make eight OLD transactions for those orders
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 1, 100, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 1, 200, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 2, 100, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 2, 300, 5.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 3, 100, 10.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 3, 200, 15.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 4, 100, 5.00, getdate())
>insert into old_transactions (
> orderid, transactiontype, amount, transactiondate)
>values (
> 4, 300, 0.00, getdate())
>create table new_transactions (
> transactionid uniqueidentifier primary key not null,
> orderid uniqueidentifier not null,
> productcode varchar(50) not null,
> amount money not null,
> transactiondate datetime not null
> )
>-- and here is the part i'm having trouble with
>insert into new_transactions (
> transactionid,
> orderid,
> productcode,
> amount,
> transactiondate)
>select newid(), -- PROBLEM 1 (detailed below)
> o.new_id,
> case
> when (select count(*) from old_transactions as ot2
> where ot2.transactionid = ot1.transactionid and
> ot2.transactiontype in (100, 200)
> group by ot2.orderid) = 2
> then 'PRODUCT 1'
> when (select count(*) from old_transactions as ot2
> where ot2.transactionid = ot1.transactionid and
> ot2.transactiontype in (100, 300)
> group by ot2.orderid) = 2
> then 'PRODUCT 2'
> end,
> 0.00, -- PROBLEM 2 (detailed below)
> transactiondate
>from old_transactions as ot1
>join orders as o on ot1.orderid = o.old_id
>
>PROBLEM 1: this statement inserts 1 row per transaction in the
>old_transactions table. what i actually want is 1 row per distinct
>orderid in the old_transactions table. as you might be able to tell
>from the other logic in the statement, i'm collapsing all the
>transactions in the old_transactions table for the same orderid into a
>single new transaction in the new_transactions table.
>PROBLEM 2: i need for the amount in the new_transactions table to be a
>SUM aggregate of the amount in the old_transactions table for the
>transactions of each orderid that i'm collapsing.
>hopefully the ddl and the problem descriptions make sense. and thanks
>in advance for any help!
>jason
>
>|||worked perfectly, and what's better, i understood it! thanks a lot,
this is exactly what i was needing.
jason