Rates of Change...

Hi,

Is there an easy way to determine the rate of change for table? I realize
that I could do periodic select count(*) from XXX, but that would only
determine the size of the table. It would ignore updates.

What I'd really like to do would be able to determine the number of non-read
operations over a table so that I can determine the rate of change for that
table.

Thanks for any help..You could also create INSERT and UPDATE triggers on the table in question
that would INSERT a record into a separate table. Everytime a record is
inserted and updated a new record would be inserted into a history table.
This may create performance issues which Erland's solutions do not.

CJ
"Madison Pruet" <mpruet@.attbi.com> wrote in message
news:wZkLa.42831$R73.7095@.sccrnsc04...
> Hi,
> Is there an easy way to determine the rate of change for table? I realize
> that I could do periodic select count(*) from XXX, but that would only
> determine the size of the table. It would ignore updates.
> What I'd really like to do would be able to determine the number of
non-read
> operations over a table so that I can determine the rate of change for
that
> table.
> Thanks for any help..

Rates

Where can one can a general idea of the going rate for particular classes?Which class; upper, middle, or lower? Also, with or without a 4 year degree?
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:O4pMchwPGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Where can one can a general idea of the going rate for particular classes?
>

Rate() and PMT()

If you are familiar with Crystal reports or Visual basic, you may be familiar with the Rate and Pmt functions.

I need to duplicate them in SQL sever 7.

Anybody have code for this already? I hate re-inventing the wheel.

More (Unnecesary) details:
I have a client who has handed me the formula that I need to use for calculating Interest rates. Unfortunatly, the formula was written in Crystal reports, so now I need to pick it apart and do the work that CR does automaticly. Any help?Check this site, it should contain what you're looking for:

http://www.wheatworks.com/formula.htm

Rate Table - Need Help To Display Data Horizontally

Hi everyone. I need help creating a query for a transportation company. I am trying to querying a table of rates that stores it's values vertically and want to display the information horizontally.

Below is an example of sample data from the table. One column in the table lists all the different cities that a group of breakpoints apply to; another lists the different weight breakpoints; and the third contains the corresponding rates for each weight break. See example below.

Weight Range
(not a field in the database
just added to describe meaning
of breakpoint column) City Breakpoint Rate
0 -100 A 100 $100
101 - 200 A 200 $200
201 - 300 A 300 $300
0 -100 B 100 $100
101 - 200 B 200 $200
201 - 300 B 300 $300

I want to display the information horizontally
City 0-100 101-200 201-300
A 100 200 300
B 100 200 300

The only other twist is that different companies have different weight breaks and they are all stored in the same table. For example Company ABC's rates have the following weight breaks.

Weight Range
(not actual field
in database) Breakpoint field Rate
0-100 --> 100 $100
101-200 --> 200 $200
201-300 --> 300 $300

Company XYZ can have breakpoints such as the following.
0-50 --> 100 $100
51-100 --> 200 $200
101-150 --> 300 $300

If creating one report to accomodate both companies is not possible then I could also create seperate reports for each company. Any help or suggestions would greatly be appreciated. Thanks in advance.i would suggest that you do this in the application layer|||I agree with r937. Don't make your SQL statement too complicated, because it will be a pain in the butt to fix or change things later. It would be way easier to loop through the results and place each value in a cell in a table. By doing this you can also do some error checking for bad data before you display.

Good luck
Hope it helps|||This is essentially a "table pivoting" problem.
It's not clear from your example but I'm assuming that the number of output columns can be larger than 4, depending on the input data.
In that case, only recursive SQL (using "WITH", i.e., CTEs) will help you. (Or maybe your SQL engine has a built-in PIVOT functionality ...)

See http://tinyurl.com/6wugk for a related problem (with solution).

H.t.h.

Rate calculation with matrix reports, How?

I have a report like this, and I would need to implement drilldown on
both column group and row group, and i am running into problems:
2003
Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
Total_Rate
West 10 10% 20 20% 50 50% 20 20% | 100
100%
East 20 10% 30 15% 20 10% 130 65% | 200
100%
North 20 20% 20 20% 40 40% 20 20% | 100
100%
South 30 30% 20 20% 10 10% 20 20% | 100
100%
----
Total 80 16% 90 18% 120 24% 190 38% 500
100%
I can do sum on the numbers, but the rate calculation is difficult.
Does anyone know how to do this?You can use the scope argument to aggregate functions to define a scope over
which to calculate a total.
For a percent-of-total calculation like you describe, you would want
something like this:
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
where "matrix1_Region" is the name of your row group
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407261518.4e5582b6@.posting.google.com...
> I have a report like this, and I would need to implement drilldown on
> both column group and row group, and i am running into problems:
> 2003
> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> Total_Rate
> West 10 10% 20 20% 50 50% 20 20% | 100
> 100%
> East 20 10% 30 15% 20 10% 130 65% | 200
> 100%
> North 20 20% 20 20% 40 40% 20 20% | 100
> 100%
> South 30 30% 20 20% 10 10% 20 20% | 100
> 100%
> ----
> Total 80 16% 90 18% 120 24% 190 38% 500
> 100%
> I can do sum on the numbers, but the rate calculation is difficult.
> Does anyone know how to do this?|||Where exactly within the matrix would you put the
calculation? Thanks.
>--Original Message--
>You can use the scope argument to aggregate functions to
define a scope over
>which to calculate a total.
>For a percent-of-total calculation like you describe,
you would want
>something like this:
>=Sum(Fields!Sales.Value)/Sum(Fields!
Sales.Value,"matrix1_Region")
>where "matrix1_Region" is the name of your row group
>--
>This post is provided 'AS IS' with no warranties, and
confers no rights. All
>rights reserved. Some assembly required. Batteries not
included. Your
>mileage may vary. Objects in mirror may be closer than
they appear. No user
>serviceable parts inside. Opening cover voids warranty.
Keep out of reach of
>children under 3.
>"Nick" <deadlocklegend@.gmail.com> wrote in message
>news:313b74d.0407261518.4e5582b6@.posting.google.com...
>> I have a report like this, and I would need to
implement drilldown on
>> both column group and row group, and i am running into
problems:
>> 2003
>> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
Q4_Rate | Total
>> Total_Rate
>> West 10 10% 20 20% 50 50% 20
20% | 100
>> 100%
>> East 20 10% 30 15% 20 10% 130
65% | 200
>> 100%
>> North 20 20% 20 20% 40 40% 20
20% | 100
>> 100%
>> South 30 30% 20 20% 10 10% 20
20% | 100
>> 100%
>> ----
--
>> Total 80 16% 90 18% 120 24% 190
38% 500
>> 100%
>> I can do sum on the numbers, but the rate calculation
is difficult.
>> Does anyone know how to do this?
>
>.
>|||As the value of the second data cell.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Terry" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c47417$66c96140$a301280a@.phx.gbl...
> Where exactly within the matrix would you put the
> calculation? Thanks.
>
> >--Original Message--
> >You can use the scope argument to aggregate functions to
> define a scope over
> >which to calculate a total.
> >For a percent-of-total calculation like you describe,
> you would want
> >something like this:
> >
> >=Sum(Fields!Sales.Value)/Sum(Fields!
> Sales.Value,"matrix1_Region")
> >where "matrix1_Region" is the name of your row group
> >
> >--
> >This post is provided 'AS IS' with no warranties, and
> confers no rights. All
> >rights reserved. Some assembly required. Batteries not
> included. Your
> >mileage may vary. Objects in mirror may be closer than
> they appear. No user
> >serviceable parts inside. Opening cover voids warranty.
> Keep out of reach of
> >children under 3.
> >"Nick" <deadlocklegend@.gmail.com> wrote in message
> >news:313b74d.0407261518.4e5582b6@.posting.google.com...
> >> I have a report like this, and I would need to
> implement drilldown on
> >> both column group and row group, and i am running into
> problems:
> >>
> >> 2003
> >> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
> Q4_Rate | Total
> >> Total_Rate
> >> West 10 10% 20 20% 50 50% 20
> 20% | 100
> >> 100%
> >> East 20 10% 30 15% 20 10% 130
> 65% | 200
> >> 100%
> >> North 20 20% 20 20% 40 40% 20
> 20% | 100
> >> 100%
> >> South 30 30% 20 20% 10 10% 20
> 20% | 100
> >> 100%
> >> ----
> --
> >> Total 80 16% 90 18% 120 24% 190
> 38% 500
> >> 100%
> >>
> >> I can do sum on the numbers, but the rate calculation
> is difficult.
> >> Does anyone know how to do this?
> >
> >
> >.
> >|||Thanks much. That worked.
But the requirement has changed slightly because sometimes the Quarter
numbers don't add up to the "supposed" yearly number for several
reasons. Instead of dividing by the sum of all quarter numbers, I
need to get from proc a yearly number and calculate the percentage by
the yearly number and then tally up. I am running into problems
because when I use sum(Q_No)/First(Year_No) the rates work right, but
the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
right but the individual rates are wrong.
Any help very much appreciated.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> You can use the scope argument to aggregate functions to define a scope over
> which to calculate a total.
> For a percent-of-total calculation like you describe, you would want
> something like this:
> =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> where "matrix1_Region" is the name of your row group
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > I have a report like this, and I would need to implement drilldown on
> > both column group and row group, and i am running into problems:
> >
> > 2003
> > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > Total_Rate
> > West 10 10% 20 20% 50 50% 20 20% | 100
> > 100%
> > East 20 10% 30 15% 20 10% 130 65% | 200
> > 100%
> > North 20 20% 20 20% 40 40% 20 20% | 100
> > 100%
> > South 30 30% 20 20% 10 10% 20 20% | 100
> > 100%
> > ----
> > Total 80 16% 90 18% 120 24% 190 38% 500
> > 100%
> >
> > I can do sum on the numbers, but the rate calculation is difficult.
> > Does anyone know how to do this?|||If you need a different calculation in the total cells than in the detail
cells, that's where the InScope function comes in.
You can do something like this:
=iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
In your case, it would be:
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407271256.179a0f98@.posting.google.com...
> Thanks much. That worked.
> But the requirement has changed slightly because sometimes the Quarter
> numbers don't add up to the "supposed" yearly number for several
> reasons. Instead of dividing by the sum of all quarter numbers, I
> need to get from proc a yearly number and calculate the percentage by
> the yearly number and then tally up. I am running into problems
> because when I use sum(Q_No)/First(Year_No) the rates work right, but
> the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> right but the individual rates are wrong.
> Any help very much appreciated.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > You can use the scope argument to aggregate functions to define a scope
over
> > which to calculate a total.
> > For a percent-of-total calculation like you describe, you would want
> > something like this:
> >
> > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > where "matrix1_Region" is the name of your row group
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > I have a report like this, and I would need to implement drilldown on
> > > both column group and row group, and i am running into problems:
> > >
> > > 2003
> > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > Total_Rate
> > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > 100%
> > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > 100%
> > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > 100%
> > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > 100%
> > > ----
> > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > 100%
> > >
> > > I can do sum on the numbers, but the rate calculation is difficult.
> > > Does anyone know how to do this?|||thanks Chris.
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
didn't work, but this did,
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
However, there is a problem when the year was collapsed, the sum of
yearly number gets multiplied by the number of Q, which is 4
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value) *
CountDistinct(Fields!Q_No.Value))
Thanks a lot for your help.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<OLHBCbCdEHA.592@.TK2MSFTNGP11.phx.gbl>...
> If you need a different calculation in the total cells than in the detail
> cells, that's where the InScope function comes in.
> You can do something like this:
> =iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
> In your case, it would be:
> =iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
> Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407271256.179a0f98@.posting.google.com...
> > Thanks much. That worked.
> >
> > But the requirement has changed slightly because sometimes the Quarter
> > numbers don't add up to the "supposed" yearly number for several
> > reasons. Instead of dividing by the sum of all quarter numbers, I
> > need to get from proc a yearly number and calculate the percentage by
> > the yearly number and then tally up. I am running into problems
> > because when I use sum(Q_No)/First(Year_No) the rates work right, but
> > the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> > right but the individual rates are wrong.
> >
> > Any help very much appreciated.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > > You can use the scope argument to aggregate functions to define a scope
> over
> > > which to calculate a total.
> > > For a percent-of-total calculation like you describe, you would want
> > > something like this:
> > >
> > > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > > where "matrix1_Region" is the name of your row group
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach of
> > > children under 3.
> > > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > > I have a report like this, and I would need to implement drilldown on
> > > > both column group and row group, and i am running into problems:
> > > >
> > > > 2003
> > > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > > Total_Rate
> > > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > > 100%
> > > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > > 100%
> > > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > > 100%
> > > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > > 100%
> > > > ----
> > > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > > 100%
> > > >
> > > > I can do sum on the numbers, but the rate calculation is difficult.
> > > > Does anyone know how to do this?