## Improvement?

### Improvement?

Hi, this is what i have, but it seems like i am making it more
complicated than need be.  this code does work.

what i am doing is computing a cost per month for utility bills.  however, 2
utility bills cover a month 90% of the time.  example:

I want the month of feb...

bill 1: 01/08/2002 - 02/08/2002  total cost = 400
bill 2: 02/08/2002 - 03/08/2002  total cost = 600

what i need to do is apply 8 days (datediff(02/01/2002, 02/08/2002)) out of
28 (total days in feb) and come up with cost for that portion of the month.
same for bill 2.

bill 1:  8/28 * 400 = 114.29
bill 2:  20/28 * 600 = 428.571

cost per month = 114.29 + 428.57 / 28 = 19.38

here is my code, but i think there is a better way to do this....

THIS WILL RETRIEVE THE FIRST PART OF THE BILL --- FROM 02/01/2002 -
02/08/2002

--!!!!!if month is december, add a year
WHEN CAST(MONTH(PERIOD_START_DATE)) AS VARCHAR) + 1 = 13
THEN CAST(YEAR(PERIOD_START_DATE))+ 1 AS VARCHAR)
ELSE CAST(YEAR(PERIOD_START_DATE)) AS VARCHAR)
END
+ '/'
+ CASE
--!!!!!if month is december go to Jan
WHEN CAST(MONTH(PERIOD_START_DATE)) AS VARCHAR) + 1 = 13 THEN '01'
ELSE CAST(MONTH(PERIOD_START_DATE)) + 1 AS VARCHAR)
END
+ '/01' AS DATETIME), PERIOD_END_DATE AS NUMERIC)
/
CAST(DATEDIFF(DAY, PERIOD_START_DATE, PERIOD_END_DATE) AS NUMERIC) *
SUM(TOTAL_AMOUNT_DUE)
FROM BILL_ACCOUNT_HISTORY AS BAH
LEFT OUTER JOIN SERVICE_HISTORY AS SH ON (BAH.BILL_ACCOUNT_HISTORY_ID =
SH.BILL_ACCOUNT_HISTORY_ID)
WHERE BAH.BILL_ACCOUNT_ID = 23531
AND DATEDIFF(MONTH,  CAST('02/08/2002' AS DATETIME),
PERIOD_END_DATE) = -14

THIS WILL RETRIEVE THE SECOND PART OF THE BILL --- FROM 02/08/2002 -
03/01/2002

CAST(CAST(YEAR(PERIOD_END_DATE) AS VARCHAR)
+ '/' +
CAST(MONTH(PERIOD_END_DATE) AS VARCHAR)
+ '/01' AS DATETIME) ) AS NUMERIC)
/ CAST(DATEDIFF(DAY, PERIOD_START_DATE, PERIOD_END_DATE) AS NUMERIC) *
SUM(TOTAL_AMOUNT_DUE)
FROM BILL_ACCOUNT_HISTORY AS BAH
LEFT OUTER JOIN SERVICE_HISTORY AS SH ON (BAH.BILL_ACCOUNT_HISTORY_ID =
SH.BILL_ACCOUNT_HISTORY_ID)
WHERE BAH.BILL_ACCOUNT_ID = 23531
AND DATEDIFF(MONTH, CAST('02/08/2002' AS DATETIME), PERIOD_START_DATE)
= -14

### Improvement?

Mike,

I think instead of trying to come up with formulas, it's safest to
let a calendar table do the work for you.  The following may look
a bit messy, but it's probably a lot easier to maintain.  One tricky
and days per bill as another.

Also, I assumed the end date on the bill was not counted, since it
is the same as the begin date on the next bill.

Some of the mess is for the output - especially to get it to show
which bill or bills was used for each month.

create table Bills (
billNo int identity(1,1),
dtFrom datetime,
dtTo datetime,
cost decimal(8,2)
)

insert into Bills values ('20020108','20020208',400)
insert into Bills values ('20020208','20020308',600)
insert into Bills values ('20020308','20020406',600)
insert into Bills values ('20020406','20020509',900)

create table Calendar (
dt datetime primary key,
mon tinyint,
yr smallint
)

end
-- Calendar table done
go

select
left(datename(month,min(dt)),3) as Mnth,
yr,
count(dt) as Days,
case when min(B2.billNo) = max(B2.billNo) then right(min(B2.billNo),10)
else right(min(B2.billNo),10)+','+right(max(B2.billNo),10)
end as BillsContributing,
cast(sum(dailyCost) as decimal(8,2)) as Cost
from Bills B1 join Calendar
on dt >= dtFrom and dt < dtTo
join ( -- B2 is daily cost by bill number
select
billNo,
cost*1.00/(datediff(day,dtFrom,dtTo)) as dailyCost
from Bills
) B2
on B1.billNo = B2.billNo
group by mon, yr

go

drop table Calendar
drop table Bills

Steve Kass
Drew University

> Hi, this is what i have, but it seems like i am making it more
> complicated than need be.  this code does work.

> what i am doing is computing a cost per month for utility bills.  however, 2
> utility bills cover a month 90% of the time.  example:

> I want the month of feb...

> bill 1: 01/08/2002 - 02/08/2002  total cost = 400
> bill 2: 02/08/2002 - 03/08/2002  total cost = 600

> what i need to do is apply 8 days (datediff(02/01/2002, 02/08/2002)) out of
> 28 (total days in feb) and come up with cost for that portion of the month.
> same for bill 2.

> bill 1:  8/28 * 400 = 114.29
> bill 2:  20/28 * 600 = 428.571

> cost per month = 114.29 + 428.57 / 28 = 19.38

> here is my code, but i think there is a better way to do this....

> THIS WILL RETRIEVE THE FIRST PART OF THE BILL --- FROM 02/01/2002 -
> 02/08/2002

>       --!!!!!if month is december, add a year
>       WHEN CAST(MONTH(PERIOD_START_DATE)) AS VARCHAR) + 1 = 13
>       THEN CAST(YEAR(PERIOD_START_DATE))+ 1 AS VARCHAR)
>       ELSE CAST(YEAR(PERIOD_START_DATE)) AS VARCHAR)
>       END
>      + '/'
>      + CASE
>       --!!!!!if month is december go to Jan
>       WHEN CAST(MONTH(PERIOD_START_DATE)) AS VARCHAR) + 1 = 13 THEN '01'
>       ELSE CAST(MONTH(PERIOD_START_DATE)) + 1 AS VARCHAR)
>       END
>      + '/01' AS DATETIME), PERIOD_END_DATE AS NUMERIC)
>      /
>      CAST(DATEDIFF(DAY, PERIOD_START_DATE, PERIOD_END_DATE) AS NUMERIC) *
> SUM(TOTAL_AMOUNT_DUE)
> FROM BILL_ACCOUNT_HISTORY AS BAH
>      LEFT OUTER JOIN SERVICE_HISTORY AS SH ON (BAH.BILL_ACCOUNT_HISTORY_ID =
> SH.BILL_ACCOUNT_HISTORY_ID)
> WHERE BAH.BILL_ACCOUNT_ID = 23531
>      AND DATEDIFF(MONTH,  CAST('02/08/2002' AS DATETIME),
> PERIOD_END_DATE) = -14

> THIS WILL RETRIEVE THE SECOND PART OF THE BILL --- FROM 02/08/2002 -
> 03/01/2002

>      CAST(CAST(YEAR(PERIOD_END_DATE) AS VARCHAR)
>      + '/' +
>      CAST(MONTH(PERIOD_END_DATE) AS VARCHAR)
>      + '/01' AS DATETIME) ) AS NUMERIC)
>      / CAST(DATEDIFF(DAY, PERIOD_START_DATE, PERIOD_END_DATE) AS NUMERIC) *
> SUM(TOTAL_AMOUNT_DUE)
> FROM BILL_ACCOUNT_HISTORY AS BAH
>      LEFT OUTER JOIN SERVICE_HISTORY AS SH ON (BAH.BILL_ACCOUNT_HISTORY_ID =
> SH.BILL_ACCOUNT_HISTORY_ID)
> WHERE BAH.BILL_ACCOUNT_ID = 23531
>      AND DATEDIFF(MONTH, CAST('02/08/2002' AS DATETIME), PERIOD_START_DATE)
> = -14

### Improvement?

this version allows you to define different periods for computing your
bills:

create table bills (start_date smalldatetime, end_date smalldatetime, cost
money)

insert bills values ('01/08/2002', '02/08/2002', 400)
insert bills values ('02/08/2002', '02/18/2002' ,300)
insert bills values ('02/18/2002', '03/08/2002' ,300)
insert bills values ('03/08/2002', '04/06/2002', 600)
insert bills values ('04/06/2002', '05/09/2002', 900)
insert bills values ('05/09/2002', '06/08/2002', 600)
insert bills values ('06/08/2002', '07/08/2002', 400)

create table bill_periods (n int, start_date smalldatetime, end_date
smalldatetime)
insert bill_periods values (1, '01/01/2002', '03/01/2002')
insert bill_periods values (2, '03/01/2002', '04/01/2002')
insert bill_periods values (3, '04/01/2002', '05/01/2002')
insert bill_periods values (4, '05/01/2002', '06/01/2002')
insert bill_periods values (5, '06/01/2002', '07/01/2002')
insert bill_periods values (6, '07/01/2002', '08/01/2002')

select  p.n,
min(p.start_date) as start_date,
max(p.end_date) as end_date,
sum(1.0 * cost / datediff(day, t.start_date, t.end_date) *
case
when t.start_date >= p.start_date and t.end_date >= p.end_date then
datediff(day, t.start_date, p.end_date)
when t.start_date >= p.start_date and t.end_date <= p.end_date then
datediff(day, t.start_date, t.end_date)
when t.start_date <= p.start_date and t.end_date <= p.end_date then
datediff(day, p.start_date, t.end_date)
when t.start_date <= p.start_date and t.end_date >= p.end_date then
datediff(day, p.start_date, p.end_date)
end) cost
from bill_periods as p
join bills as t
on (t.start_date between p.start_date and p.end_date)
or (t.end_date between p.start_date and p.end_date)
or (t.start_date < p.start_date and t.end_date > p.end_date )
group by n

drop table bills
drop table bill_periods

Mikhail Berlyant
Data Integrator, Data Systems
Brainbench MVP for Visual Basic   www.brainbench.com

> Hi, this is what i have, but it seems like i am making it more
> complicated than need be.  this code does work.

> what i am doing is computing a cost per month for utility bills.  however,
2
> utility bills cover a month 90% of the time.  example:

> I want the month of feb...

> bill 1: 01/08/2002 - 02/08/2002  total cost = 400
> bill 2: 02/08/2002 - 03/08/2002  total cost = 600

> what i need to do is apply 8 days (datediff(02/01/2002, 02/08/2002)) out
of
> 28 (total days in feb) and come up with cost for that portion of the
month.
> same for bill 2.

> bill 1:  8/28 * 400 = 114.29
> bill 2:  20/28 * 600 = 428.571

> cost per month = 114.29 + 428.57 / 28 = 19.38

> here is my code, but i think there is a better way to do this....

> THIS WILL RETRIEVE THE FIRST PART OF THE BILL --- FROM 02/01/2002 -
> 02/08/2002

>       --!!!!!if month is december, add a year
>       WHEN CAST(MONTH(PERIOD_START_DATE)) AS VARCHAR) + 1 = 13
>       THEN CAST(YEAR(PERIOD_START_DATE))+ 1 AS VARCHAR)
>       ELSE CAST(YEAR(PERIOD_START_DATE)) AS VARCHAR)
>       END
>      + '/'
>      + CASE
>       --!!!!!if month is december go to Jan
>       WHEN CAST(MONTH(PERIOD_START_DATE)) AS VARCHAR) + 1 = 13 THEN '01'
>       ELSE CAST(MONTH(PERIOD_START_DATE)) + 1 AS VARCHAR)
>       END
>      + '/01' AS DATETIME), PERIOD_END_DATE AS NUMERIC)
>      /
>      CAST(DATEDIFF(DAY, PERIOD_START_DATE, PERIOD_END_DATE) AS NUMERIC) *
> SUM(TOTAL_AMOUNT_DUE)
> FROM BILL_ACCOUNT_HISTORY AS BAH
>      LEFT OUTER JOIN SERVICE_HISTORY AS SH ON (BAH.BILL_ACCOUNT_HISTORY_ID
=
> SH.BILL_ACCOUNT_HISTORY_ID)
> WHERE BAH.BILL_ACCOUNT_ID = 23531
>      AND DATEDIFF(MONTH,  CAST('02/08/2002' AS DATETIME),
> PERIOD_END_DATE) = -14

> THIS WILL RETRIEVE THE SECOND PART OF THE BILL --- FROM 02/08/2002 -
> 03/01/2002

>      CAST(CAST(YEAR(PERIOD_END_DATE) AS VARCHAR)
>      + '/' +
>      CAST(MONTH(PERIOD_END_DATE) AS VARCHAR)
>      + '/01' AS DATETIME) ) AS NUMERIC)
>      / CAST(DATEDIFF(DAY, PERIOD_START_DATE, PERIOD_END_DATE) AS NUMERIC)
*
> SUM(TOTAL_AMOUNT_DUE)
> FROM BILL_ACCOUNT_HISTORY AS BAH
>      LEFT OUTER JOIN SERVICE_HISTORY AS SH ON (BAH.BILL_ACCOUNT_HISTORY_ID
=
> SH.BILL_ACCOUNT_HISTORY_ID)
> WHERE BAH.BILL_ACCOUNT_ID = 23531
>      AND DATEDIFF(MONTH, CAST('02/08/2002' AS DATETIME),
PERIOD_START_DATE)
> = -14

### Improvement?

Consider carrying Steve's Calendar table one step further and build a
calenfdar with all the temp*data for the enterprise in it -- fiscal
calendar, billing periods, holidays, etc.:

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
bill_period INTEGER NOT NULL,
fiscal_year INTEGER NOT NULL,
...);

Now you just join the Billing table to the Calendar with a BETWEEN
predicate and you have an exact answer with the ability to remove
holidays, or whatever other rules you might have.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.veryComputer.com/ ***
Don't just participate in USENET...get rewarded for it!

I'm somewhat new to Access (.adp)...

I've created a web application in ASP using an SQL Server database. The
client then wanted to access the database from an administrative point
of view. Instead of writing ASP code and creating a browser interface
for him I created an Access (.adp) application which connects to SQL
Server (via Internet). This all works quite well and allows him to
search for customer applications, insert comments, etc.

Recently, both he and I have noticed that upon initial start of the main
form we have to wait a long time for the form to open. I assume Access