August 28, 2004 at 11:52 am
I need to create a database for a service where the rates will vary by the day of the week and the hour of the day. Later I will need to query the customers monthly cost. I can't see to get my mind around the best approach to normalize tables in this type of relationship. Any suggestions are appreciated.
August 28, 2004 at 3:25 pm
I'd start by listing the objects that you think you need to store. You need a customer, you need some type of rental agreement, some inventory, some cost structure.
If the cost structure varies by day and hour, make a table of days and hours that map to costs. In another table, probably one that references both the cusotmer and the rental, link to your costs structure based on the appropriate date and time.
August 28, 2004 at 11:07 pm
What your saying makes sense but, the cost that is being calculated involves items being bought at one price and sold at another. Profits then need to be calculated at the moment of each sale based on the purchase price and the aggregate of rental rates that are subject to change by day and or hour.
I am looking for a design that will offer ease in calculating the final cost to each customer, without jumping through painful hoops.
August 29, 2004 at 3:49 am
Hi,
If you can explode a table that contains the hourly rent for every hour and asset,
then this kind of calculations become more or less trivial.
/rockmoose
Provide sample schema, data and sql:
[cust] -- [asset]
[hourtime] -- [asset]
/************************ create schema ***************************************/
set nocount on
create table cust(cust_nr int not null primary key clustered,
first_name varchar(35) not null )
create table asset( asset_nr int not null primary key clustered,
asset varchar(35) not null )
create table hourtime( [date] datetime not null check(floor(cast([date] as float)) = ceiling(cast([date] as float))),
[hour] int not null check([hour] between 0 and 23),
primary key clustered([date],[hour]) )
create table asset_rent( asset_nr int not null references asset(asset_nr),
[date] datetime not null,
[hour] int not null,
rate money not null,
constraint fk_asset_rent_hourtime foreign key([date],[hour]) references hourtime([date],[hour]) )
create table cust_asset( cust_nr int not null references cust(cust_nr),
asset_nr int not null references asset(asset_nr),
buy_date datetime not null,
sell_date datetime null,
buy_price money not null,
sell_price money null )
/************************ populate the tables ***************************************/
-- help table variable with 0,1,2,3,4,5,6,7,8,9
declare @nr table( nr int not null primary key )
while ( select count(*) from @nr ) < 10
insert @nr select count(*) from @nr
-- create number table with numbers from 0 - 999
select a.nr + 10 * b.nr + 100 * c.nr as nr into #numbers
from @nr a cross join @nr b cross join @nr c order by 1
insert cust select 1, 'John' union all select 2, 'Brian'
insert asset select 1, 'Sirloin Mansion' union all select 2, 'Boss Condominium'
insert hourtime( [date], [hour] )
select [day], [hour]
from
( select cast(37985+nr as datetime) as [day] from #numbers
where cast(37985+nr as datetime) between '20040101' and '20041231' ) days
cross join
( select nr as [hour] from #numbers where nr between 0 and 23 ) hours
order by 1,2
insert asset_rent( asset_nr, [date], [hour], rate )
select asset_nr, [date], [hour], asset_nr * 25.0 - [hour]
from hourtime cross join asset
order by 1,2,3
insert cust_asset( cust_nr, asset_nr, buy_date, sell_date, buy_price, sell_price )
select 1, 2, getdate(), getdate() + 67, 100000, 120000
union all
select 2, 1, getdate() - 40, getdate(), 212000, 320000
drop table #numbers
/******************** reports and calculations from tables *****************************/
select
c.first_name,
a.asset,
datepart( year, ar.[date] ) as [year],
datepart( month, ar.[date] ) as [month],
sum( ar.rate ) as total_rent,
count(*) as hours_rented,
count(*)/24 as full_days_rented
from
cust_asset ca
join cust c on ca.cust_nr = c.cust_nr
join asset a on ca.asset_nr = a.asset_nr
join asset_rent ar on ca.asset_nr = ar.asset_nr
where
ca.buy_date = dateadd( hour, ar.[hour], ar.[date] )
group by
c.first_name,
a.asset,
datepart( year, ar.[date] ),
datepart( month, ar.[date] )
order by
1,2,3,4
/*
first_name name year month total_rent hours_rented full_days_rented
----------------------------------- ----------------------------------- ----------- ----------- --------------------- ------------ ----------------
Brian Sirloin Mansion 2004 7 3654.0000 276 11
Brian Sirloin Mansion 2004 8 9306.0000 684 28
John Boss Condominium 2004 8 2238.0000 60 2
John Boss Condominium 2004 9 27720.0000 720 30
John Boss Condominium 2004 10 28644.0000 744 31
John Boss Condominium 2004 11 3306.0000 84 3
*/
select
c.first_name,
a.asset,
--ca.buy_date,
--ca.sell_date,
ca.buy_price,
ca.sell_price,
sum( ar.rate ) as total_rent,
count(*)/24 as full_days_rented,
ca.sell_price - ca.buy_price - sum( ar.rate ) as profit
from
cust_asset ca
join cust c on ca.cust_nr = c.cust_nr
join asset a on ca.asset_nr = a.asset_nr
join asset_rent ar on ca.asset_nr = ar.asset_nr
where
ca.buy_date = dateadd( hour, ar.[hour], ar.[date] )
group by
c.first_name,
a.asset,
--ca.buy_date,
--ca.sell_date,
ca.buy_price,
ca.sell_price
order by
1,2
/*
first_name name buy_price sell_price total_rent full_days_rented profit
----------------------------------- ----------------------------------- --------------------- --------------------- --------------------- ---------------- ---------------------
Brian Sirloin Mansion 212000.0000 320000.0000 12960.0000 40 95040.0000
John Boss Condominium 100000.0000 120000.0000 61908.0000 67 -41908.0000
*/
/****************** cleanup ***********************/
drop table asset_rent
drop table cust_asset
drop table hourtime
drop table asset
drop table cust
You must unlearn what You have learnt
August 29, 2004 at 10:25 am
Thanks Rockmoose! Your scenario fits my situation well.
August 29, 2004 at 12:53 pm
You are welcome.
If you use something like this make sure there are no gaps in the data.
There has to be an entry for every hour of the day otherwise you will get wrong results.
We have used this technique several times and it works well.
( although I have not used it to hour granularity, which is of no importance )
In the largest "time-cost" tables we have like 3mil rows and it works like a charm.
The logic to do calculations without these supporting tables would be horrenduos,
and the processing time terrible.
/rockmoose
You must unlearn what You have learnt
August 29, 2004 at 8:43 pm
I agree on the processing time. Currently we are using a design requiring numeruus temp tables and cursors to calculate the cost. With approximately 35,000 rows in the customer table the queries are taking fifteen minutes or more to complete.
Thanks again for a brilliant design.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply