What Are Calculated Columns?
Oct 3, 2006hi
What are calculated colunms?
ADVERTISEMENT
SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables
Apr 20, 2014I 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.
Calculated Columns Based On Multiple Columns?
Aug 12, 2014MS SQL 2008 R2
I have the following effectively random numbers in a table:
n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers
How To Sum Calculated Columns
Mar 12, 2014How 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
Using Named Calculated Columns
Dec 13, 2002I'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?
ReUsing Calculated Columns
Feb 20, 2008Okay, 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?
Trouble With Calculated Columns
Apr 8, 2008I have an OrderDetails table, ShipHeader table and a ShipDetails table in my database. I am trying to add a couple of columns to my OrderDetails table. I need two additional columns in my OrderDetails that are calculated. One should be called "Shipped" and give me the sum of all the ship Qty records for theat line item. The other should be called "Returned" and do the same for the return records. I was able to create this in a View without a problem. However, I need a method of creating this table that will still allow me to edit the information in the other columns in the OrderDetail table while viewing the shipped and returned information for each line item. With a View, I cannot edit any of the information while viewing it. Should I use a function? A stored procedure? If so, how do I code this? The tables are listed below.
CREATE TABLE OrderHeader
(
OrderID int identity primary key,
Status int,
StartDate datetime,
EndDate datetime
)--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed"
CREATE TABLE OrderDetail
(
OrderDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE ShipHeader
(
ShippingID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost"
CREATE TABLE ShipDetail
(
ShipDetailID int identity primary key,
ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderDetailID int,
Qty int
)
Displaying Calculated Columns In A Matrix
Jan 31, 2008Hi,
Im trying to create a calulated value in my matrix table
I have the following matrix
ADSL CABLE BROADBAND %of adsl %ofcable %ofbroadband
AWAITING 5 9 11 0.2 0.36 etc
IN PROGRESS 67 10 5 0.8 0.1 etc
CHURNED 8 1 15 0.3 etc etc
I would like to create columns called
% of ADSL
% of cable
% of broadband
which is the count for that product divided by the total off adsl+cable+broadband for that particular status
I have the following problems
1. How do I add columns to a matrix which would allow these calculated columns to display?
thanks
Data Source Views - Calculated Columns
Feb 7, 2007Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
Reporting Services :: Sorting On Calculated Report Items Columns
Jun 21, 2015I need to sort my tablix report where I have several calculated columns like:
=ReportItems!Textbox47.value+ReportItems!Textbox48.value..
Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Dec 13, 2007Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Can't SUM A Calculated Value
Oct 10, 2007I 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
Calculated Field
May 14, 2008HiI 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 RelatedCalculated Member
Jan 19, 2005Hello,
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.
Calculated Members
May 9, 2005This 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?
Calculated Field
Jul 25, 2005I 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
Calculated Members
Jun 21, 2006Hi...
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????
IF In A Calculated Field
Nov 8, 2014How is the below formula written in a calculated field in SQL?
If TransactionType = 3 then
10% * ValueTransaction else
1.5% * ValueTransaction
end if
Calculated Field
Apr 11, 2006I 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?
Calculated Column Or ...
Jan 1, 2008I 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
Where To Put Calculated Fields
Oct 19, 2006This 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.
Percentage Not Being Calculated
Oct 5, 2007What'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
Calculated Field (GPA)
Sep 26, 2007I 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
Calculated Field
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?
Calculated Members
Oct 20, 2005I 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 RelatedCalculated Fields
Apr 19, 2007I 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 RelatedCalculated Column Not Working
Jan 29, 2007Ok 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 RelatedCalculated Field In SQL 2000
Sep 12, 2007I 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 RelatedFormula For Calculated Column
Sep 19, 2005Hi,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 RelatedCalculated Field In SQL Server 6.5
Mar 7, 2001I 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.
Use Calculated Field In Same Query
Jun 25, 2004Right 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!
Can't Display Calculated Members
Mar 8, 2004I 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 RelatedHow To Add A Calculated Column In A View
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