Matrix help

Hi all. Trying something that seems like it shoulb be simple.
Important Data:
StudentID (ex. 1, 2, 3)
Class (ex. Math, English)
Response (-10 to 10)
Here is what I'm trying to get
Student 1 Student 2 Average
Math -7 3 -2
English -1 9 4
Science 10 4 7
================================================== (sum) 2 16 9
So, here is where I am now. I've added two rows to the matrix,
one that is aggregated by MAX (or FIRST or whatever, since I
know I'll only get one StudenID per class), and one that is AVG.
That way, I can get a row that has AVG, and a column that has sum.
Is this possible? Thanks.Yes, you can use this expression in the matrix cell:
==IIF(InScope(<ClassGroupName>) And InScope(<StudentIDGroupName>),
Max(Fields!Response.Value), IIF(InScope(<ClassGroupName>),
Avg(Fields!Response.Value), Sum(Fields!Response.Value)))
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Will" <groups@.willwyatt.com> wrote in message
news:28d5f694.0407181851.2376aecf@.posting.google.com...
> Hi all. Trying something that seems like it shoulb be simple.
> Important Data:
> StudentID (ex. 1, 2, 3)
> Class (ex. Math, English)
> Response (-10 to 10)
> Here is what I'm trying to get
> Student 1 Student 2 Average
> Math -7 3 -2
> English -1 9 4
> Science 10 4 7
> ==================================================> (sum) 2 16 9
>
> So, here is where I am now. I've added two rows to the matrix,
> one that is aggregated by MAX (or FIRST or whatever, since I
> know I'll only get one StudenID per class), and one that is AVG.
> That way, I can get a row that has AVG, and a column that has sum.
> Is this possible? Thanks.

math or text

in the below sql why is year(classdate) " + " a " + " MONTH(classdate) a math command giving me 2006 - 12 = 167

and not "2006/12" as text? please help me

cmdGetCat = New SqlDataAdapter("SELECT DISTINCT year(classdate) " + " a " + " MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ", conNorthwind)

Hi,

i think the code that you provided has certain syntax problem, can you verify the codes?

anyway the sql statement should be something like this:

"SELECT DISTINCT year(classdate) + ' a ' + MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) "

|||

sorry its this.... i dont get 2006/12 ...i get 126 which is 2006-12=126 hahaha ..i need "yyyy/mm" why god why!!!

"SELECT DISTINCT year(classdate) + ' -/' + MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) "

|||

Hi,

You can use this sql statement to giv you yyyy/MM in varchar format:
SELECT CAST(YEAR(classdate) AS VARCHAR(4)) + '/' + CAST(MONTH(classdate) AS VARCHAR(2)) AS 'monthcode'
FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)

you can also choose to use the CONVERT function to return a certain format of the date and use the data in your programm

Hope this helps

|||

Here is a Convert Sample for you:

select convert(varchar(7),classdate,111) AS 'monthcode' FROM
...........

math notation in db?

I'm not a professional dba or dbd, but I'm proficient in the basics of
database design and sql. I want to create a database of math
definitions, and I'm wondering how one would go about creating a
database that contains mathematical notation (and I'm not just talking
about basic symbols where I could get away with ascii code). I need
to be able to insert a wide variety of mathematical expressions, from
fractions to integrals, into fields (just like you can enter in-line
math symbols in MS Word using equation editor). I have no clue how to
go about this. Is it a matter of developing certain programming
skills/languages? Would such a capabliltiy be proprietary (dbms-
specific)? Is it possible at all? Any help would be appreciated.
Thank you.One way to implement this is to use the Mathematical Markup Language
(MathML). MathML is based on XML and is used for describing mathematical
notation and encoding both its structure and content. The main purpose of
MathML is to enable mathematics to be used on Internet, but since it is XML
based it is widely used in applications that can process XML.

SQL Server supports Unicode and also SQL Server 2005 adds support for XML
data type. There are many parsers, and any browser can serve as presentation
layer.

See more info on the official W3C site at the links below:
http://www.w3.org/Math/whatIsMathML.html
http://www.w3.org/Math/
http://www.w3.org/TR/2007/WD-MathML3-20070427/
http://www.w3.org/Math/XSL/
http://www.w3.org/Math/XSL/pmathml2.xml
http://www.w3.org/Math/XSL/csmall2.xml
I have seen some applications that use MathML to store mathematical
notations, but developed in Oracle (still storing in XML columns).

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||>MathML is based on XML <<

How is this standard doing? I think I might be behind the curve,
since I have not done any math writing in a very long time. LaTEX was
the standard in the US for the American Mathematical Association
papers and other journals. It was also popular with textbook
publishers because you could get Knuth's Metafont symbols for
typesetting.|||On Nov 30, 5:43 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

One way to implement this is to use the Mathematical Markup Language
(MathML). MathML is based on XML and is used for describing mathematical
notation and encoding both its structure and content. The main purpose of
MathML is to enable mathematics to be used on Internet, but since it is XML
based it is widely used in applications that can process XML.
>
SQL Server supports Unicode and also SQL Server 2005 adds support for XML
data type. There are many parsers, and any browser can serve as presentation
layer.
>
See more info on the official W3C site at the links below:http://www.w3.org/Math/whatIsMathML...XSL/csmall2.xml
>
I have seen some applications that use MathML to store mathematical
notations, but developed in Oracle (still storing in XML columns).
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Very helpful post. Thank you.

Math is driving me nuts

The business rule is, the sales manager is commissioned on the avg. number
of appointments set up per salesrep per day during the month.

I have 2 tables: The UserLog table records only 1 entry per day per user
(salesrep). This will log how many salesreps worked a particular day. The
second table logs any appointments set up.

UserLog: ID, UserName, EnteredTime
Appointment: ApptID, EnteredTime, ApptDate

I figured that, for a given date ranged, I could
1. sum the number of appointments
2. sum the number of days worked
2. sum the salesreps / number of days = avg number of salesreps per day
3. number of appointments / avg number of salesreps per day = avg number
of appointments per sales rep

But this logic is flawed. If I average out every day and then take an
average of this daily average, I get a different result. Any ideas on how
to best solve this problem?

Thanks.Sounds weird; the more sales reps there are,
the less the manager makes for the same amount of work
(setting up appointments). I guess this statistic
is rational, if your prime concern is penalizing the
manager for setting up appointments for reps who
never show up for work :-)

Ignoring that ...
Your first algorithm is the one that's off.
You need to calculate appointments per day,
and reps (working) per day, to get appts per rep per day.
The average comes after, and you don't need to
count the number of days at all.

For simplicity, I'm going to write "EnteredDate"
instead "convert(varchar,EnteredTime,111)"
-- i.e. the yyyymmdd part of the time.

select avg(AD.Appts / RD.Reps)
from (select EnteredDate,count(*) Appts
from Appointment group by EnteredDate) AD
join (select EnteredDate,count(*) Reps
from UserLog group by EnteredDate) RD
on RD.EnteredDate = AD.EnteredDate
where RD.EnteredDate between ...

Hope that helps.

"J" <sorrynospam@.nobody.com> wrote in message
news:40fb37ea$0$74187$39cecf19@.news.twtelecom.net. ..
> The business rule is, the sales manager is commissioned on the avg. number
> of appointments set up per salesrep per day during the month.
> I have 2 tables: The UserLog table records only 1 entry per day per user
> (salesrep). This will log how many salesreps worked a particular day.
The
> second table logs any appointments set up.
> UserLog: ID, UserName, EnteredTime
> Appointment: ApptID, EnteredTime, ApptDate
> I figured that, for a given date ranged, I could
> 1. sum the number of appointments
> 2. sum the number of days worked
> 2. sum the salesreps / number of days = avg number of salesreps per
day
> 3. number of appointments / avg number of salesreps per day = avg
number
> of appointments per sales rep
> But this logic is flawed. If I average out every day and then take an
> average of this daily average, I get a different result. Any ideas on how
> to best solve this problem?
> Thanks.
>|||The "average number of appointments per salesrep per day" could mean two things. It
could mean the "average number of appointments per sales rep for the month", divided
by the number of days, or it could be the average of (the daily average appointments/sales rep).

Here's an example of where these are different.

Case 1: 100 reps working each day, and 100 appt each day.
Either calculation gives an average of 1 appt/rep for the month

Case 2: 1 rep worked alone during half the month, and had 10000 appointment,
and 99 reps worked together the other half, but had 0 appointments.

Now it's either 10000 appointments total / daily avg of 50 reps ,
= 200 appts/rep
or
15 days where the appt/ref value was 10000/15 and 15 days where the
appt/ref value was zero, which averages to [15*(10000/15) + 15*0]/30,
or 10000/30 = 333 on average. This method is influenced by whether
slackers worked only on dead days or only on busy days, even if they
did nothing either way.

You probably want one or the other of these, but they measure different things.

Steve Kass
Drew University

J wrote:
> The business rule is, the sales manager is commissioned on the avg. number
> of appointments set up per salesrep per day during the month.
> I have 2 tables: The UserLog table records only 1 entry per day per user
> (salesrep). This will log how many salesreps worked a particular day. The
> second table logs any appointments set up.
> UserLog: ID, UserName, EnteredTime
> Appointment: ApptID, EnteredTime, ApptDate
> I figured that, for a given date ranged, I could
> 1. sum the number of appointments
> 2. sum the number of days worked
> 2. sum the salesreps / number of days = avg number of salesreps per day
> 3. number of appointments / avg number of salesreps per day = avg number
> of appointments per sales rep
> But this logic is flawed. If I average out every day and then take an
> average of this daily average, I get a different result. Any ideas on how
> to best solve this problem?
> Thanks.
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:mwIKc.7057$mL5.2991@.newsread1.news.pas.earthl ink.net...
> The "average number of appointments per salesrep per day" could mean two
things. It
> could mean the "average number of appointments per sales rep for the
month", divided
> by the number of days, or it could be the average of (the daily average
appointments/sales rep).

And in simpler terms, why you're getting different results
is because A/B + C/D is not (A + C)/(B + D).

On a different level: what the heck are these two things measuring?

The number of appointments per month is a clear-cut measure of
how hard the manager is working. Perhaps dividing by the number
of available salesreps is supposed to eve things out across months
where half the reps are on vacation or sick?

Well, if you work out the appointments per rep on a daily
basis, you are getting a better picture (to the limit of accuracy of your
data) on what the manager is doing.

> Here's an example of where these are different.
> Case 1: 100 reps working each day, and 100 appt each day.
> Either calculation gives an average of 1 appt/rep for the month
> Case 2: 1 rep worked alone during half the month, and had 10000
appointment,
> and 99 reps worked together the other half, but had 0
appointments.
> Now it's either 10000 appointments total / daily avg of 50 reps
,
> = 200 appts/rep
> or
> 15 days where the appt/ref value was 10000/15 and 15 days where
the
> appt/ref value was zero, which averages to [15*(10000/15) +
15*0]/30,
> or 10000/30 = 333 on average. This method is influenced by
whether
> slackers worked only on dead days or only on busy days, even if
they
> did nothing either way.
> You probably want one or the other of these, but they measure different
things.
> Steve Kass
> Drew University
> J wrote:
> > The business rule is, the sales manager is commissioned on the avg.
number
> > of appointments set up per salesrep per day during the month.
> > I have 2 tables: The UserLog table records only 1 entry per day per
user
> > (salesrep). This will log how many salesreps worked a particular day.
The
> > second table logs any appointments set up.
> > UserLog: ID, UserName, EnteredTime
> > Appointment: ApptID, EnteredTime, ApptDate
> > I figured that, for a given date ranged, I could
> > 1. sum the number of appointments
> > 2. sum the number of days worked
> > 2. sum the salesreps / number of days = avg number of salesreps per
day
> > 3. number of appointments / avg number of salesreps per day = avg
number
> > of appointments per sales rep
> > But this logic is flawed. If I average out every day and then take an
> > average of this daily average, I get a different result. Any ideas on
how
> > to best solve this problem?
> > Thanks.|||It entirely depends on which 'average' you want. Different ways of
grouping / summing the data can produce slightly different results.

Do you want :

Sum per rep per day added together for each manager then divided by
the number of reps ?
or
Sum of all appointments for all reps divided by the numbers of reps ?
or
Something else

There are a few combinations to consider. Also, there are different
ways of looking at averages. Mean, mode, median etc... (from what I
can remember from my GCSE's some 14 years ago - apologies if this is
wrong / incomplete)

I would suggest defining exactly what is required and then choosing a
solution to suit. Comparing different requirements will give different
answers.

Ryan

"J" <sorrynospam@.nobody.com> wrote in message news:<40fb37ea$0$74187$39cecf19@.news.twtelecom.net>...
> The business rule is, the sales manager is commissioned on the avg. number
> of appointments set up per salesrep per day during the month.
> I have 2 tables: The UserLog table records only 1 entry per day per user
> (salesrep). This will log how many salesreps worked a particular day. The
> second table logs any appointments set up.
> UserLog: ID, UserName, EnteredTime
> Appointment: ApptID, EnteredTime, ApptDate
> I figured that, for a given date ranged, I could
> 1. sum the number of appointments
> 2. sum the number of days worked
> 2. sum the salesreps / number of days = avg number of salesreps per day
> 3. number of appointments / avg number of salesreps per day = avg number
> of appointments per sales rep
> But this logic is flawed. If I average out every day and then take an
> average of this daily average, I get a different result. Any ideas on how
> to best solve this problem?
> Thanks.|||Let me make more sense of this:

I should have used the term 'telemarketers' instead of 'salesreps'.
The telemarketers set up appointments for an outside sales force.
The manager is commissioned on the appointments set up, but
the telemarketers workforce may very...

"Mischa Sandberg" <mischa_sandberg@.telus.net> wrote in message
news:mFHKc.88627$eO.41568@.edtnps89...
> Sounds weird; the more sales reps there are,
> the less the manager makes for the same amount of work
> (setting up appointments). I guess this statistic
> is rational, if your prime concern is penalizing the
> manager for setting up appointments for reps who
> never show up for work :-)
> Ignoring that ...
> Your first algorithm is the one that's off.
> You need to calculate appointments per day,
> and reps (working) per day, to get appts per rep per day.
> The average comes after, and you don't need to
> count the number of days at all.
> For simplicity, I'm going to write "EnteredDate"
> instead "convert(varchar,EnteredTime,111)"
> -- i.e. the yyyymmdd part of the time.
> select avg(AD.Appts / RD.Reps)
> from (select EnteredDate,count(*) Appts
> from Appointment group by EnteredDate) AD
> join (select EnteredDate,count(*) Reps
> from UserLog group by EnteredDate) RD
> on RD.EnteredDate = AD.EnteredDate
> where RD.EnteredDate between ...
> Hope that helps.
> "J" <sorrynospam@.nobody.com> wrote in message
> news:40fb37ea$0$74187$39cecf19@.news.twtelecom.net. ..
> > The business rule is, the sales manager is commissioned on the avg.
number
> > of appointments set up per salesrep per day during the month.
> > I have 2 tables: The UserLog table records only 1 entry per day per
user
> > (salesrep). This will log how many salesreps worked a particular day.
> The
> > second table logs any appointments set up.
> > UserLog: ID, UserName, EnteredTime
> > Appointment: ApptID, EnteredTime, ApptDate
> > I figured that, for a given date ranged, I could
> > 1. sum the number of appointments
> > 2. sum the number of days worked
> > 2. sum the salesreps / number of days = avg number of salesreps per
> day
> > 3. number of appointments / avg number of salesreps per day = avg
> number
> > of appointments per sales rep
> > But this logic is flawed. If I average out every day and then take an
> > average of this daily average, I get a different result. Any ideas on
how
> > to best solve this problem?
> > Thanks.|||J,

That's great, but the point is that you have not stated the business rule unambiguously. How about inventing some sample data for which
the two definitions of this average are unequal, so that you can try to decide which specific answer you want?

SK

J wrote:
> Let me make more sense of this:
> I should have used the term 'telemarketers' instead of 'salesreps'.
> The telemarketers set up appointments for an outside sales force.
> The manager is commissioned on the appointments set up, but
> the telemarketers workforce may very...
> "Mischa Sandberg" <mischa_sandberg@.telus.net> wrote in message
> news:mFHKc.88627$eO.41568@.edtnps89...
>>Sounds weird; the more sales reps there are,
>>the less the manager makes for the same amount of work
>>(setting up appointments). I guess this statistic
>>is rational, if your prime concern is penalizing the
>>manager for setting up appointments for reps who
>>never show up for work :-)
>>
>>Ignoring that ...
>>Your first algorithm is the one that's off.
>>You need to calculate appointments per day,
>>and reps (working) per day, to get appts per rep per day.
>>The average comes after, and you don't need to
>>count the number of days at all.
>>
>>For simplicity, I'm going to write "EnteredDate"
>>instead "convert(varchar,EnteredTime,111)"
>>-- i.e. the yyyymmdd part of the time.
>>
>>select avg(AD.Appts / RD.Reps)
>>from (select EnteredDate,count(*) Appts
>> from Appointment group by EnteredDate) AD
>>join (select EnteredDate,count(*) Reps
>> from UserLog group by EnteredDate) RD
>> on RD.EnteredDate = AD.EnteredDate
>>where RD.EnteredDate between ...
>>
>>Hope that helps.
>>
>>"J" <sorrynospam@.nobody.com> wrote in message
>>news:40fb37ea$0$74187$39cecf19@.news.twtelecom.net. ..
>>
>>>The business rule is, the sales manager is commissioned on the avg.
> number
>>>of appointments set up per salesrep per day during the month.
>>>
>>>I have 2 tables: The UserLog table records only 1 entry per day per
> user
>>>(salesrep). This will log how many salesreps worked a particular day.
>>
>>The
>>
>>>second table logs any appointments set up.
>>>
>>>UserLog: ID, UserName, EnteredTime
>>>Appointment: ApptID, EnteredTime, ApptDate
>>>
>>>I figured that, for a given date ranged, I could
>>> 1. sum the number of appointments
>>> 2. sum the number of days worked
>>> 2. sum the salesreps / number of days = avg number of salesreps per
>>
>>day
>>
>>> 3. number of appointments / avg number of salesreps per day = avg
>>
>>number
>>
>>>of appointments per sales rep
>>>
>>>But this logic is flawed. If I average out every day and then take an
>>>average of this daily average, I get a different result. Any ideas on
> how
>>>to best solve this problem?
>>>
>>>Thanks.
>>>
>>>
>>>
>>>
>>>
>>
>>
>|||I understand very well the points you are making, and thanks for clarifying
these issues. They want the average of the daily averages of Appointments
per Telemarketer. In this case, Mischa's example works nicely.

"Steve Kass" <skass@.drew.edu> wrote in message
news:mD_Kc.7718$mL5.2515@.newsread1.news.pas.earthl ink.net...
> J,
> That's great, but the point is that you have not stated the business
rule unambiguously. How about inventing some sample data for which
> the two definitions of this average are unequal, so that you can try to
decide which specific answer you want?
>
> SK
> J wrote:
> > Let me make more sense of this:
> > I should have used the term 'telemarketers' instead of 'salesreps'.
> > The telemarketers set up appointments for an outside sales force.
> > The manager is commissioned on the appointments set up, but
> > the telemarketers workforce may very...
> > "Mischa Sandberg" <mischa_sandberg@.telus.net> wrote in message
> > news:mFHKc.88627$eO.41568@.edtnps89...
> >>Sounds weird; the more sales reps there are,
> >>the less the manager makes for the same amount of work
> >>(setting up appointments). I guess this statistic
> >>is rational, if your prime concern is penalizing the
> >>manager for setting up appointments for reps who
> >>never show up for work :-)
> >>
> >>Ignoring that ...
> >>Your first algorithm is the one that's off.
> >>You need to calculate appointments per day,
> >>and reps (working) per day, to get appts per rep per day.
> >>The average comes after, and you don't need to
> >>count the number of days at all.
> >>
> >>For simplicity, I'm going to write "EnteredDate"
> >>instead "convert(varchar,EnteredTime,111)"
> >>-- i.e. the yyyymmdd part of the time.
> >>
> >>select avg(AD.Appts / RD.Reps)
> >>from (select EnteredDate,count(*) Appts
> >> from Appointment group by EnteredDate) AD
> >>join (select EnteredDate,count(*) Reps
> >> from UserLog group by EnteredDate) RD
> >> on RD.EnteredDate = AD.EnteredDate
> >>where RD.EnteredDate between ...
> >>
> >>Hope that helps.
> >>
> >>"J" <sorrynospam@.nobody.com> wrote in message
> >>news:40fb37ea$0$74187$39cecf19@.news.twtelecom.net. ..
> >>
> >>>The business rule is, the sales manager is commissioned on the avg.
> > number
> >>>of appointments set up per salesrep per day during the month.
> >>>
> >>>I have 2 tables: The UserLog table records only 1 entry per day per
> > user
> >>>(salesrep). This will log how many salesreps worked a particular day.
> >>
> >>The
> >>
> >>>second table logs any appointments set up.
> >>>
> >>>UserLog: ID, UserName, EnteredTime
> >>>Appointment: ApptID, EnteredTime, ApptDate
> >>>
> >>>I figured that, for a given date ranged, I could
> >>> 1. sum the number of appointments
> >>> 2. sum the number of days worked
> >>> 2. sum the salesreps / number of days = avg number of salesreps per
> >>
> >>day
> >>
> >>> 3. number of appointments / avg number of salesreps per day = avg
> >>
> >>number
> >>
> >>>of appointments per sales rep
> >>>
> >>>But this logic is flawed. If I average out every day and then take an
> >>>average of this daily average, I get a different result. Any ideas on
> > how
> >>>to best solve this problem?
> >>>
> >>>Thanks.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >

Math in a column

Can't I perform Math functions in deriving a column?
Check out the following SQL which DOES NOT work...
SELECT GEN.loan_num,
MKT.yield_prem,
MKT.yield_fee,
GEN.ysp_to_wire,
GEN.loan_amt,
(MKT.yield_fee =
(GEN.loan_amt *
(MKT.yield_prem/100)))
FROM DataTrac.dbo.GEN,
DataTrac.dbo.MKT
--WHERE GEN.loan_num = '2000013699'
WHERE GEN.file_id = MKT.file_id
AND NOT (MKT.yield_fee =
(GEN.loan_amt *
(MKT.yield_prem/100)))
Let me know.
Thanks.
wnfisbaCan you show DDL, sample data, desired results, and what "DOES NOT work"
means? Do you get an error message? If so, what is it?
Please read http://www.aspfaq.com/5006
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:486F6FD7-6217-41B3-B4E6-1DC92410B90D@.microsoft.com...
> Can't I perform Math functions in deriving a column?
> Check out the following SQL which DOES NOT work...
> SELECT GEN.loan_num,
> MKT.yield_prem,
> MKT.yield_fee,
> GEN.ysp_to_wire,
> GEN.loan_amt,
> (MKT.yield_fee =
> (GEN.loan_amt *
> (MKT.yield_prem/100)))
> FROM DataTrac.dbo.GEN,
> DataTrac.dbo.MKT
> --WHERE GEN.loan_num = '2000013699'
> WHERE GEN.file_id = MKT.file_id
> AND NOT (MKT.yield_fee =
> (GEN.loan_amt *
> (MKT.yield_prem/100)))
> Let me know.
> Thanks.
> wnfisba|||In the SELECT list, the last expression is a problem
SELECT ...,
(MKT.yield_fee =
(GEN.loan_amt *
(MKT.yield_prem/100)))
I don't know what you want this to be, but perhaps you are just
assigning the name yield_fee to the calculated amount? If so,
consider:
yield_fee =
(GEN.loan_amt *
(MKT.yield_prem/100)))
Roy
On Wed, 22 Feb 2006 13:37:30 -0800, "wnfisba"
<wnfisba@.discussions.microsoft.com> wrote:

>Can't I perform Math functions in deriving a column?
>Check out the following SQL which DOES NOT work...
>SELECT GEN.loan_num,
> MKT.yield_prem,
> MKT.yield_fee,
> GEN.ysp_to_wire,
> GEN.loan_amt,
> (MKT.yield_fee =
> (GEN.loan_amt *
> (MKT.yield_prem/100)))
>FROM DataTrac.dbo.GEN,
> DataTrac.dbo.MKT
>--WHERE GEN.loan_num = '2000013699'
>WHERE GEN.file_id = MKT.file_id
>AND NOT (MKT.yield_fee =
> (GEN.loan_amt *
> (MKT.yield_prem/100)))
>Let me know.
>Thanks.
>wnfisba

Math functions

Good Afternoon LAdies & Gents

I was hoping you could assist me with this problem (more of a how-to)
I have a Database with 4 Tables and I need to incert some mathamatical functions to get totals, averages, percentages etc.

here is my problem

I have the following Tables

tblPrice, field(s) mfg_price, discount_price, ltc_price
tblCustomer, field(s) cpny_name, contact, address, phone, date
tblProduct, field(s) crt, server, notebook,
tblInventory, field(s) StockID, cust_num

I need to be able to combine the contact name from tblCustomer, with the product purshased from tblProduct, in addition with calculationsC my Post 2 u'r other Query http://dbforums.com/showthread.php?postid=2986841#post2986841 n start looking @. Grouping SUM() etc,

GW

Math functions

Hi,
How to find the list of SQL math functions in SQL Server 2005?

Thanks

KaiHi,

Got BOL installed ?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/46495a2e-81d0-4677-9d72-9db083cd1023.htm

You havent ?

http://msdn.microsoft.com/library/e...asp?frame=true

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Thanks.

Kai
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1143827343.530294.311400@.e56g2000cwe.googlegr oups.com...
Hi,

Got BOL installed ?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/46495a2e-81d0-4677-9d72-9db083cd1023.htm

You havent ?

http://msdn.microsoft.com/library/e...asp?frame=true

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--

Math function

Hi All

I'm trying to find a math function (if it exists) in SQL Server. If it
doesnt exist, then maybe someone can tell me what its called so I can
do a bit more reading on it

Basically I want to do this:

Parameter Components
1 1
2 2
3 1, 2
4 4
5 1, 4
6 2, 4
7 3, 4
8 8
9 1, 8
and so on

I'd like to be able to call a function and it would return true or
false like so

functionname(1, 9) = true
so 1 is a component of 9

functionname(2, 9) = false
so 2 is not a component of 9

functionname(4, 5) = true
so 4 is a component of 5

If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,
I'd appreciate it!

Thanks in advance

SamOn 27 Aug 2004 06:00:50 -0700, Samuel Hon wrote:

> Hi All
> I'm trying to find a math function (if it exists) in SQL Server. If it
> doesnt exist, then maybe someone can tell me what its called so I can
> do a bit more reading on it
> Basically I want to do this:
> Parameter Components
> 1 1
> 2 2
> 3 1, 2
> 4 4
> 5 1, 4
> 6 2, 4
> 7 3, 4
> 8 8
> 9 1, 8
> and so on
> I'd like to be able to call a function and it would return true or
> false like so
> functionname(1, 9) = true
> so 1 is a component of 9
> functionname(2, 9) = false
> so 2 is not a component of 9
> functionname(4, 5) = true
> so 4 is a component of 5
> If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,
> I'd appreciate it!
> Thanks in advance
> Sam

Your example seems a little fuzzy, but it looks like you are saying that
the "components" of an integer n are the largest power of 2 <= n, and the
remainder when that power of 2 is subtracted from n.

If this is the case, then your function in VB.NET could be:

Public Function IsComponent(C as Integer, P as Integer) as Boolean
Dim L as integer = LargestPowerOfTwo(P)
If (C > 0) and ( (C = L) or (C = P-L) ) Then
Return True
Else
Return False
End If
End Function

Public Function LargestPowerOfTwo(X as Integer) As Integer
Dim I as Integer = 1
If X < 1 Then
Return 0
End if
Do While I*2 <= X
I = I * 2
Loop
Return I
End Function

This could easily be ported to any other language, including T-SQL should
you need it there.|||Hi Ross

Thanks for the reply

I found that the 'technique' I'm looking for is bitwise comparison.
Change the numbers to bits, and then compare

Sam

math error

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

Try

select convert(decimal(20,2),1.2334e+006)

Madhivanan|||Hi Madhivanan,
Thanks for the input. The column type is varchar where the value
1.2334e+006 is stored. So I need to convert it to float as suggested by
some before converting it to decimal. Anyway, that brings another
issue. When running the query the server just errors out without giving
the value that causes the error. It just says 'conversion error'. I
had to take the SQl out and put it in a cursor and use a loop to find
out which row is actually causing the error and find the value
1.2334e+006. Is there any easier way to find out which row in the table
causes the SQL server to error out. For example can I set the error
level so that I find more information so that I can locate the row in
the table.
Thanks.

Madhivanan wrote:

Quote:

Originally Posted by

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

>
Try
>
select convert(decimal(20,2),1.2334e+006)
>
Madhivanan

|||(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

Hi Madhivanan,
Thanks for the input. The column type is varchar where the value
1.2334e+006 is stored. So I need to convert it to float as suggested by
some before converting it to decimal. Anyway, that brings another
issue. When running the query the server just errors out without giving
the value that causes the error. It just says 'conversion error'. I
had to take the SQl out and put it in a cursor and use a loop to find
out which row is actually causing the error and find the value
1.2334e+006. Is there any easier way to find out which row in the table
causes the SQL server to error out. For example can I set the error
level so that I find more information so that I can locate the row in
the table.

Unfortunately, there are not really any good options. The best is probably
to run a SELECT query, and takes some hint from where it terminates. This
example illustrates:

CREATE TABLE #tmp1 (a varchar(23) NOT NULL, b int IDENTITY)
go
INSERT #tmp1(a) VALUES ('1234')
INSERT #tmp1(a) VALUES ('1232')
INSERT #tmp1(a) VALUES ('2344')
INSERT #tmp1(a) VALUES ('34.34')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('1234e+006')
INSERT #tmp1(a) VALUES ('777')
go
SELECT convert(decimal(20, 2), a) FROM #tmp1 ORDER BY b
go
DROP TABLE #tmp1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx