Monarca Database Migrator

[rHINOx]

Monarca is the most powerful enterprise database migrator and re-engineering database tool. With Monarca you can easily import, integrate, transform, validate and migrate data from any-to-any existing databases.

Monarca takes high advantages of the ultimate Java Database Connectivity Technology (JDBC) allowing migrate data from any-to-any JDBC-ODBC database compliant. You can migrate from Oracle® to IBM DB2®, from dBase® to Oracle®, from IBM DB2® to PostgreSQL®, from MySQL® to Microsoft Access®, from Microsoft Excel® to Informix®, etc. Just plug-in your prefer JDBC Driver for your source and target databases and let Monarca do all the job for you.

You can find more information on:

http://www.endiansoft.com/monarca/index.html

[MAK]

Stop posting this in every forum. Just post it in the news section.

[deepa]

I have two tables. Cost_Weekdys and Cost_weekend.

Table Weekdays

DMA Station From_Date to_date EM_Spot EM_Cost DT_Spots DT_Cost PR_spot PR_Cost
---------------------------------------------------------------------------------------------
501 Telemundo 3-15-04 3-19-04 3 200.00 2 150.00 4 100.00
501 Univision 3-22-04 3-26-04 2 100.00 1 100.00 3 90.00
501 Telefura 3-29-04 4-2-04 1 80.00 3 110.00 2 80.00
501 Telemundo 4-5-04 4-9-04 2 95.00 8 112.00 1 300.00


Table Weekend

DMA Affiliate From_Date to_date DT_Spots DT_Cost PR_spot PR_Cost
---------------------------------------------------------------------------

501 Telemundo 3-20-04 3-21-04 2 150.00 4 100.00
501 Univision 3-27-04 3-27-04 2 100.00 1 100.00
501 Univision 3-28-04 3-28-04 4 60.00 2 76.00
501 Telefura 4-3-04 4-3-04 7 35.00 1 80.00
501 Telefura 4-4-04 4-4-04 5 45.00 3 55.00
501 Telemundo 4-10-04 4-11-04 2 95.00 8 112.00

Have to create report by weekly

it should look like as follows

DMA Affiliate From_Date to_date Orderd_Spots Cost

---------------------------------------------------------------------------
501 Telemundo 3-15-04 3-21-04 14 700.00
501 Univision 3-22-04 3-28-04 15 626.00
501 Telefura 3-29-04 4-4-04 22 485.00
501 Telemundo 4-5-04 4-11-04 21 714.00

Need help with query.

Select DMA,from_date,to_date,orderd_spots=(EM_SPOT+DT_spo ts+PR_Spot)
,cost = (EM_cost+DT_cost+PR_cost)
from weekdays,weekend
where weekdays.dma = weekend.dma

Thanks in advnced.

[MAK]

--Please create new thread when it is a new posting. Please do not post questions on some irrelevant threads.
--BTW. Here is the answer

create Table Weekdays
(DMA int,Station varchar(100),From_Date datetime,
to_date datetime,EM_Spot int,EM_Cost int,DT_Spots int,
DT_Cost int,PR_spot int,PR_Cost int)


insert into weekdays select 501, 'Telemundo','3-15-04','3-19-04',3, 200.00, 2, 150.00, 4, 100.00
insert into weekdays select 501, 'Univision' ,'3-22-04','3-26-04', 2, 100.00, 1, 100.00, 3, 90.00
insert into weekdays select 501, 'Telefura' ,'3-29-04','4-2-04', 1, 80.00, 3, 110.00, 2, 80.00
insert into weekdays select 501, 'Telemundo' ,'4-5-04','4-9-04', 2, 95.00, 8, 112.00, 1, 300.00



create Table Weekend
(DMA int,Affiliate varchar(100),From_Date datetime,
to_date datetime,DT_Spots int,
DT_Cost int,PR_spot int,PR_Cost int)

insert into weekend select 501, 'Telemundo' ,'3-20-04','3-21-04', 2, 150.00, 4, 100.00
insert into weekend select 501, 'Univision' ,'3-27-04','3-27-04', 2, 100.00, 1, 100.00
insert into weekend select 501, 'Univision' ,'3-28-04','3-28-04', 4, 60.00, 2, 76.00
insert into weekend select 501, 'Telefura' ,'4-3-04','4-3-04', 7, 35.00, 1, 80.00
insert into weekend select 501, 'Telefura' ,'4-4-04','4-4-04', 5, 45.00, 3, 55.00
insert into weekend select 501, 'Telemundo','4-10-04','4-11-04', 2, 95.00, 8, 112.00


SET DATEFIRST 1
go
Select min(DMA) as DMA,Station,min(from_date) as FromDate,max(to_date) as to_date,
sum(orderd_spots) as OrderSpots,sum(cost) as Cost from
(
select DMA,from_date,to_date,Station,datepart(wk,from_dat e) as week,
orderd_spots=(EM_SPOT+DT_spots+PR_Spot),cost = (EM_cost+DT_cost+PR_cost) from weekdays
union all
select DMA,from_date,to_date,Affiliate as Station,datepart(wk,from_date) as week,
orderd_spots=(DT_spots+PR_Spot),cost = (DT_cost+PR_cost) from weekend
) as mytable
group by DMA,Station,week
order by DMA,Station,week