Can't SUM A Calculated Value
Oct 10, 2007
I know you can't aggregate a calculated value and I'm having a problem finding a solution
I have 3 groups: Course(1), Term(2), Category(3). Course = a particular class (i.e) Algebra; Term = 1 iof 4 terms in our school year; Category = for a class, one may have multiple categories like, participation, homework, quizzes, test, etc.
A class can be weighted for a Category like homework and will count 20% of the grade, tests 40% of the grade and so on. For each Category, I calculate the weighted total using the following:
=iif(Fields!Weight.Value<>0,sum(Fields!Earned.Value)/sum(Fields!Poss.Value)*Fields!Weight.Value*.01,0)
My delema is to sum up all those calculated values in the Term footer.
Thanks
View 4 Replies
ADVERTISEMENT
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
May 14, 2008
HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill
View 5 Replies
View Related
Jan 19, 2005
Hello,
I have following problem:
A measure in a cube need to be divided by another measure as follows:
MEASURE1MEASURE2
Measure 1 (SUM of Money spent by each person)
Measure 2 (Amount of Money available for each country per person).
Example:
USA: 155
Germany:134
France:143)
Measure1 is a SUM and works fine, but Measure2 should only be a distinct value for each country. So if person comes from Germany, then the SUM of Spent Money should be divided by 134.
Any idea how this can be done.
View 1 Replies
View Related
May 9, 2005
This is correct
store.currentmember.properties("Store_Manager")
I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR
Do you can help me?
View 1 Replies
View Related
Jul 25, 2005
I am a newbie to SQL Server using SQL Server 2000.
I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)?
However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money).
The fields that I am basing the calculation on are:-
ColumnName Type Size Allow Nulls
PurchasePrice money 8 0
LoanSize money 8 0
this is the code for the calculated field
([loansize] / [PurchasePrice] * 100)
and this is the description of the calculated field that is forced in the designer
ColumnName Type Size Allow Nulls
LTV money 8 1
and the designer does not allow me to change the type.
I would be grateful of any pointers
Regards
Tony
View 1 Replies
View Related
Jun 21, 2006
Hi...
I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????
View 1 Replies
View Related
Mar 12, 2014
How can I sum the calculated columns.
select [Funding] [Fundings], [Original] [Originals],[Variance] = [Previous_Year]-[Current_Year],[SumValue] = [CurrentYear]/4, [ActualValue] = [Variance] * 0.75,[FinanceYear], [New Value] = [Previous_Year]+[Current_Year] from Finance
View 1 Replies
View Related
Nov 8, 2014
How is the below formula written in a calculated field in SQL?
If TransactionType = 3 then
10% * ValueTransaction else
1.5% * ValueTransaction
end if
View 1 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI
===================================================
1000|liability | -10,000.00 | 08 | 2005
===================================================
1001| asset | 20,000.00 | 08 | 2005
===================================================
1000|liability | -9,000.00 | 09 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description Amount
asset 20,000.00
liability (10,000.00)
===========
Net Asset 10,000.00
===========
The above report would list 2 columns as Description & Amount, next it would sort the Description
column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.
Guys, hope someone out there can help me with the sql command for the above report?
View 3 Replies
View Related
Oct 3, 2006
hi
What are calculated colunms?
View 5 Replies
View Related
Jan 1, 2008
I have a location table with columns for [state tax rate], [local tax rate] and a bit column for each to indicate if it is active. From this I need to calculate a total tax percentage; i.e.(state tax rate * bit) +(local tax rate * bit). If I want to stay at the database with this I can identify three alternatives (I'm sure there are more):
1. Calculated column
2. Table Variable (or Temp. Table)
3. View
I'm interested in feedback as to which of these methods is more appropriate or if there is a better way I haven't identified.
Thanks
View 3 Replies
View Related
Oct 19, 2006
This doesn't necessarily belong in this forum, but I'm starting here in the hopes of getting some direction.
Business users have asked me to "map" a spreadsheet to our Datawarehouse. The spreadsheet contains a lot of calculations.
I created a first pass, but it was difficult to map the Analysis Services cube data to the spreadsheet data and in the process I had to hard code a lot of things that will make the spreadsheet less flexible for additional data.
So my question is, where is the best place to put calculated fields. In my SQL Statements, in ths SSIS transformations, or in the Analysis Cube?
Any help, or pointers to more information, would be greatly appreciated.
View 1 Replies
View Related
Oct 5, 2007
What's incorrect with my sql so that the pct is not being calculated? I'm returning all zeros.
------------------------------------------------------------------------------------------------
SELECT
sa.schoolc sch#,
s.schname AS School,
Y = sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END),
N = sum(CASE WHEN u.logindate IS NULL THEN 1 END),
pct = (sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)/
(sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)+sum(CASE WHEN u.logindate IS NULL THEN 1 END))) * 100
FROM
users AS u
INNER JOIN stugrp_active AS sa ON u.username = sa.suniq
INNER JOIN school AS s ON s.schoolc = sa.schoolc
WHERE
u.username like '3%'
AND sa.schoolc >= 300
GROUP BY
sa.schoolc,
s.schname
ORDER BY
sa.schoolc
View 12 Replies
View Related
Sep 26, 2007
I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.
here is a sample table:
Term
ID
LastName
FirstName
CRN
Subject
LetterGrade
Calc GPA
20072
1
Doe
John
1234
CHEM
B
20072
1
Doe
John
3214
BIO
A
20072
1
Doe
John
4321
LAW
B
20072
2
Bauer
Jack
1234
CHEM
A
20072
2
Bauer
Jack
3214
BIO
C
20072
2
Bauer
Jack
5467
FIN
B
A = 4.0
B = 3.0
C = 2.0
D = 1.0
View 10 Replies
View Related
Apr 20, 2007
This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report.
What is the simplest way to accomplish this?
View 1 Replies
View Related
Oct 20, 2005
I have a reporting services report that I'm creating from an Analysis Services cube. I created two calculated memebers. When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command. I have 21 GB of hard drive space.
View 3 Replies
View Related
Apr 19, 2007
I had made some calculated fields within my data set. Later I had to change my stored procedure. When I refreshed my data set my calculated fields disappeared. Is there a way to not lose your calculated fields in a dataset when you refresh it?
View 6 Replies
View Related
Jan 29, 2007
Ok I have three columns in my database that deal with ratings of individual ads. One is called totalrating, one is totalvotes, and one is averagerating. TotalRating gets incremented with the rating and totalvotes is incremented by one when someone votes. Then averagerating is a calculated column which divides the totalrating by the totalvotes. The problem is unless I manually set totalrating and totalvotes to 0, the stored procedure does not work. They both remain null. I tried to set the default value for each column to 0, which visual studio changed to ((0)). Maybe I am doing this wrong. If someone could help me I would really appreciate it. Thanks so much. Dave Roda
View 4 Replies
View Related
Sep 12, 2007
I need a calculated field C with several CASES. If (field A is 'daily' or 'half day' or 'hourly') and (field B is NULL) then C= D-50 If (field A is hourly and field B is NULL then C= 850I don't know sql server 2000 well enough to create this query.thanksMilton
View 6 Replies
View Related
Sep 19, 2005
Hi,I'm struggling to get a calculated column to work in sql, the fields to be calculated are:[AdRevenue_a] money[Admissions_a] int[DoorPrice_a] smallmoney[DoorSplit_a] moneyAnd the calculation I require is:(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100This is what I think it should be but it doesn't work...convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))Any suggestions??
View 6 Replies
View Related
Dec 13, 2002
I'm trying to create calculated columns in a SELECT query (inside a sp) that are based on other calculated columns in the same SELECT list. But since I'm not allowed to refer to each calculated column by name, I'm forced to repeat long calculations every time I build one calculation based on another.
I'd like to do something on the order of:
SELECT Price*Quantity AS SalePrice, SalePrice*1.08 AS SalesPriceWithTax
FROM Orders
Access used to let me do that, but not SQL. Any suggestions? Can I use variables declared and assigned before I start my SELECT, and then put those variables in my SELECT list? Anyone have a useful shortcut?
View 6 Replies
View Related
Mar 7, 2001
I am used to working in MS Access where you can return a value as in:
[date1]-[date2]=X
It will calculate that value provided "date1" and "date2" are fields in the recordset. One calc for each record.
I am getting an error message in SQL Server saying that neither "date1" nor "date2" are not contained in an aggregate function and there is no "group by" clause.
In Access this would not be a problem.
Can you help?
Thank you.
View 1 Replies
View Related
Jun 25, 2004
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example:
SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total
Obviously that doesn't work, but what could I do to get that basic thing to work?
Thanks!
View 4 Replies
View Related
Mar 8, 2004
I have created a few calculated members under one dimension (meaning the parent dimension is not Measures, but other dimensions). It can be showed in the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Office 2k, xp, even 2003). Is there any solution to display the calculated members (as with the dimension) in Excel PivotTable? vba code needed? service pack needed?
View 4 Replies
View Related
Apr 21, 2008
*first issue
how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns
create view vw1
as
select tab.col1,tab.col2 from
from tab
and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false
thanx
View 2 Replies
View Related
Oct 3, 2013
I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:
COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.
I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.
I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".
----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'
[code]....
View 1 Replies
View Related
Nov 3, 2014
I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.
System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.
Example:
Date of Offence - Number of Points
Aug. 6, 2013 - 6
Feb. 4, 2014 - 4
Apr. 25, 2014 - 4
- 2
May 8, 2014 - 2
Explanation:
On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).
Below are DB Tables:
tbl_Driver
DriverID DriverLicense FirstName LastName
tbl_Event
EventID EventTIme DriverID CarID
tbl_EventViolation
EventViolatinID EventID ViolationID Status (1 = no, 2 = yes radio buttons)
tbl_Violation
ViolationID ViolationName Points
tbl_ViolationClass
ViolationClassID ClassName
I made an SQL Code but to sum the accumulated points only but not the whole condition. See below:
SELECTa.DriverId,
a.DriverLicense,
a.FirstName + ' ' + a.LastName as DriverName,
a.FirstName,
a.LastName,
a.DriverMobileNo1,
[Code] .....
View 2 Replies
View Related
Nov 14, 2006
What is the best code pratice to use do the following code,
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
instead of
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?
View 1 Replies
View Related
Mar 29, 2007
I have calculated column which is referenced by function and I need to alter the function. How can I disable the calculated column so I am able to modify the function?
..................................................................
I have orders table and I need to disable the calculated column OrderDate so I may able to modify the function dbo.udfTicksToDateTime.
CREATE TABLE Orders (
OrderDateAsTicks BIGINT,
OrderDate AS dbo.udfTicksToDateTime(OrderDateAsTicks)
)
Thanks
Sanjeev
Cleveland
View 2 Replies
View Related
Jul 23, 2007
Dear All;
i want to use a calculated field in where clause but this does'nt works: e.g.;
select (colA + colB) as calcField
from tbl
where calcField > 100
what is the right way to do it, as i m getting error;
it works like :
select (colA + colB) as calcField
from tbl
where (colA + colB) > 100
but i m using a complex query as i m getting value from a function and then i hav to use that in where clause as well ;
Thanx in advance,
--
Muhammad Zeeshan
View 1 Replies
View Related
Feb 20, 2008
Okay, so yes, I am new to SQL server...
I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.
REATE Proc CreateMasterSchedule
as
Select
dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate,
(case when dbo.[MOP_Planning Overview].[Order Category]='101' AND CAST (Convert (char(10),[Planning Date], 110)as DateTime)
<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1)then dbo.[MOP_Planning Overview].[Transaction Quantity - Basic U/M] else 0 end)as PriorInProc,
If I try to use DateofPlanningDate in the above case statement, I get the invalid column name error.
Basically, I just need a way to reuse the value returned by this column.
Can anyone help?
View 6 Replies
View Related
Mar 28, 2008
Hi,
I'm writing a query that calculates values for each id, then I need to sum up all the values for each id and put them in another field, here is what I wrote but SQL cannot understand the column that I calculeted
select id,term_cd,
case when RIGHT(term_cd, 1) IN ('6') and substring(term_cd, 2,4) <= 2004) then '1'
when substring(term_cd, 2,4) <= 2004 and RIGHT(term_cd, 1) = 5 then '0.5'
else '1' end as term_count,
SUM(term_count) AS ttd_enrolled
group by id,term_cd
SQL gives me an error "invalid column name tern_count" in the line where I calculate the sum.
what's wrong with the query? or should I calculate the term_count in an inner query?
Appretiate your help
View 8 Replies
View Related
