Re: writing a query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 16:02:50 -0400

Here is the code from my Feb 2002 column on a similar problem. This one
deals with bank interest rates that can vary with time. You need to
calculate the daily interest, based on account balance. Listing 4 is the
one relevant to your particular problem.

-- Code from Tom Moreau's Feb 2002 SQL Server Professional column
-- Listing 1. Build script for Balances and Interest
create table Balances
(
  AccountID int not null
, BalDate datetime not null
, Balance money not null
, constraint PK_Balances
  primary key (AccountID, BalDate)
)
go

insert Balances values (1, '20010601', 6000.00)
insert Balances values (1, '20010602', 15000.00)
insert Balances values (1, '20010603', 4000.00)
go

insert Balances values (2, '20010601', 4000.00)
insert Balances values (2, '20010602', 5000.00)
insert Balances values (2, '20010603', 2000.00)
go

create index NC1_Balances on Balances (BalDate)
go

create table Interest
(
  IntDate datetime not null
, Lvl tinyint not null
, Threshold money not null
, Rate numeric (3, 2) not null
, constraint PK_Interest
  primary key (IntDate, Threshold)
)
go

insert Interest values ('20010101', 1, 0.00, 0.05)
insert Interest values ('20010101', 2, 5000.00, 0.10)
insert Interest values ('20010101', 3, 10000.00, 0.15)
go

insert Interest values ('20010603', 1, 0.00, 0.02)
insert Interest values ('20010603', 2, 10000.00, 0.05)
go

create index NC1_Interest on Interest (IntDate)
go

-- Listing 2. Calculating interest based on highest applicable rate
select
  r.AccountID,
  convert (money,
    sum (r.Rate * r.Balance)) Interest
from
(
  select
    b.AccountID,
    b.Balance,
    max (i.Rate) Rate -- Highest rate
  from
      Interest i
    join -- at or below balance
      Balances b on i.Threshold <= b.Balance
  where
      i.IntDate =
  (
    select -- Most recent interest date
      max (ii.IntDate)
    from
        Interest ii
    where -- in effect on balance date
        ii.IntDate <= b.BalDate
  )
  group by
    b.AccountID,
    b.BalDate,
    b.Balance
) r
group by
  r.AccountID
go

-- Listing 3. Adding the interest rate caps

insert Interest values
  ('20010603', 3, 922337203685477.5807, 0.00)
insert Interest values
  ('20010101', 4, 922337203685477.5807, 0.00)

-- Listing 4. Calculating interest for each tier

select
  b.AccountID
, sum ((
  case
    -- highest applicable range
    when b.Balance > h.Threshold
      then h.Threshold
    -- lower ranges
    when b.Balance between l.Threshold and h.Threshold
      then b.Balance
    else 0.00
  end
  - l.Threshold) * l.Rate
 ) Interest
from
     Balances b
join Interest l on l.IntDate <= b.BalDate
                and b.Balance >= l.Threshold
join Interest h on h.IntDate <= b.BalDate
                and h.IntDate = l.IntDate
                and h.Lvl = l.Lvl + 1
where
    l.IntDate =
(
  select
    max (i.IntDate)
  from
    Interest i
  where
    i.IntDate <= b.BalDate)
group by
 b.AccountID
order by
 b.AccountID
go

drop table Balances
drop table Interest
go 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rick" <Rick@discussions.microsoft.com> wrote in message
news:517347E4-5262-47AF-82B9-4BB7CDA1870B@microsoft.com...
I am trying to wrie a query that I am stuck on. What I need to do is total
up
some hours worked by a persons bill rate. But, I need to be able to select
the rate at the time the work is performed. So, lets say contractor A ia
making $23 hr in june and worked 35 hours and $35 hr in July and worked 25
hours. His rate change took affect July 1 and work was performed on 10
different days in each month.
My fields are as follows
--Effective date-The date his rate is valid. Different date for each rate.
--Billrate-The rate he charges,
--TimeDate--The date he did the work.
--RegularHours-The amount of hours worked on each day-I need to total that
up.
I am having a problem with tying the total hours worked to the effective
bill rate at the time.
Any suggestions?