Improvement?

Improvement?

Post by Mike Alb » Sat, 29 Jun 2002 00:46:47



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

   Thanks in advance for your help!!!

 
 
 

Improvement?

Post by Steve Kas » Sat, 29 Jun 2002 01:28:22


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
thing about this is that you need to deal with months as one group
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

>    Thanks in advance for your help!!!


 
 
 

Improvement?

Post by Mikhail Berlyan » Sat, 29 Jun 2002 02:32:41


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
Launch Your Yahoo!Music Experience  http://launch.yahoo.com
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

>    Thanks in advance for your help!!!

 
 
 

Improvement?

Post by Joe Celk » Sat, 29 Jun 2002 02:45:04


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!

 
 
 

1. Performance Improvements

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
is downloading the main table contents to our local systems. The main
table has about 9000 records. Of course this table continues to grow
daily and weekly as new users submit applications. We need to be able to
search ALL records so we can view and make changes to the customer
applications, etc. Of course, we'd like to do this quickly.

I noticed the Max Records property. But that would restrict us to a
subset of the available records. We need to see all records.

Any ideas ?

Ed

2. dll registering error

3. activex script performance improvement ???

4. Using PreparedStatement and WHERE IN

5. Stored Procedure Improvements?

6. How to concat strings from multiple rows without a function or cursor?

7. "OR" performance improvement

8. Looking for DISKIN.BIN or ISDISK.BIN to check drive status from Fox

9. query improvements

10. SQL Server performance improvement

11. Job improvement

12. Explanation for this weird performance improvement please!

13. Improvement performance of this SP?