SQL 2012 :: Index On Calculated Field With UDF?
May 11, 2015is it possible to have a field that is calculated using a User Defined Function, and index that field?
View 9 Replies
ADVERTISEMENT
Index In Calculated Field From A Ntext Field
Nov 8, 2005For some reasons I have to use a ntext field for both small strings like "10" and large binalry files.
I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc.
To trick the sorting I use a calculated field:
ORDER BY RSort - where Rsort is:
convert(varchar(4), RD.response) as RSort
It works but put a high load on the SQL server when the number of responses increases.
I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended.
What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:
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 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 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
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 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 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 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!
Calculated Field And Conditions
Nov 3, 2014I 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] .....
Using Calculated Field In Where Clause
Jul 23, 2007Dear 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
AttribDescription As Calculated Field
Jul 23, 2005Hiin T-SQL,(how) is it possible to concatenate 3 (varchar) fields into one; eitherin a SQL query or through a calculated field (or using a view, ifanybody can explain to me how to use views), according to the followingrules:{first 30 chars of Trim(AttributeVal1)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal2)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal3)}=> define as new field StockItemDescriptionideally I would like SQL Server to do this processing rather thanbuilding all these answer strings on the client side.tiaAxel
View 5 Replies View RelatedHow To Generate Calculated Field
Jul 20, 2005I need to run a data report that will query an Access_Table that has thefollowing Project info:My Form has a command_button with the following code:Private Sub Command1_Click()Set db = New Connectiondb.CursorLocation = adUseClientdb.Open "PROVIDER=MSDataShape;DataPROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &"Access_Table.mdb;"Set adoAge = New RecordsetadoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date,IIf([Invoice_Date]<Now()-30 And[Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30,IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROMCust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimisticSet DataReport1.DataSource = adoAgeDataReport1.ShowEnd SubMy DataReport1 has the following RptTextbox:RptTextbox Datafield1 CustomerName2 OrderNo3 Invoice_Date4 Greaterthan305 Greaterthan60Problem arise when trying to display the above report that has an errormessage that says "Datafield Greaterthan30 not found"Is this an SQL Query limitation where Invoice_Date Datafield can only bequeried once & if we have more than one Datafieldwith the same fieldname (Invoice_Date) within the same Data report,thereport will fail to run? Should there be one moreNew Recordset to hold a second Datafield (Invoice_Date)?can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedSort A Calculated Field
Oct 10, 2007
I have a quick question that i have just a simple calc field.
Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.
Calculated Field Issue
Jan 22, 2008
Hi I am having a problem with something that was relatively simple in Crystal and i am guessing it is purely my inexperience that is the issue here!
Calculated Field
=iif(Fields!Fun_Debt_Status.Value <> "debt","",
iif(max(Fields!Fun_Days_Overdue.Value)>30,"On Stop",
iif(max(Fields!Fun_Days_Overdue.Value)>0,"Overdue",
iif(sum(Fields!Fun_OS_Amount.Value)> Fields!CREDIT_LIMIT.Value,"Over Credit Limit","ok"))))
Want i am trying to do as you can probably see is create a "Account Status" field depending upon overdue days for payment etc..
My report just produces an error (internal) when i try and run - the calculated field above isn't even on the report.
Please let me know what i am doing wrong! I maybe going at it in completely the wrong way altogether,
Thanks in Advance!
My Problem With Calculated Field
Nov 11, 2006I have a table for tracking the movement of cash register it has the fields
ID
Date
Amount_Debit
Amount_Credit
Explanation
I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I
used this but it gave me an error
sum(amount_debit-amount_credit) where id<=id
the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show
the balance of it
can any one help me to get the desired result in the best way possible
additional question if possible can I get correct cash balance if I drop the ID field depending on date
I use SQL Server 2005 Express
Calculated Field In A Csv File
Nov 13, 2007
I have a fairly simple SQL code that includes a calculated field (date difference) in Oracle. I am bring the data in a csv format, and its giving me nothing for that calculation. I checked the results in TOAD and they look fine. I know, i am missing something for the format but have tried everything.
I am new to this so any help is appreciated.
Thanks
select rpt.emc_last_run_date "Report Run Date",
(rpt.EMC_ACCEPT_DATE_TIME - rpt.EMC_QUEUED_DATE_TIME * 24 * 60) "Time To Accept(mins)"
Referencing A Calculated Field In A Query
Nov 24, 2005I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005
SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2
This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.
The only way how to avoid the issue in the SQLExpress seems probably to be
- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)
- Utilize computed columns if possible (no idea on performance impact here)
Am I right or is there any other way how to reference a calculated field?
Any suggestion is greatly welcomed! Thanks in advance.
Re-using A Calculated Sub-select Field In A View?
Jul 23, 2005I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!
View 6 Replies View RelatedDesign Question: Calculated Field
Jul 20, 2005I'm trying to create an OLAP system using SQL Server 2000 AnalysisServices (AS). I want the AS cube to be based on a database with astar schema.I have a field called Ratio. The initial ratio value is based uponthis formula: (CurrentTimePeriodAmount -PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, thatinitial ratio can be manually overridden and replaced. Then the ratiois used to calculate future Amounts.Should I place that calculated Ratio field in the "source" databasedesign or should it be part of the Analysis Services cube?
View 2 Replies View RelatedViews - Filtering On Calculated Field
Sep 12, 2007
Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.
Then I stuffed the self joins into a couple views for the events I'm interested in...
The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.
So how can I speed up the filtering on that calculated column?
Or am I going to have to get really creative with unions?
Calculated Field Crashes Vs 2005 Sp1 ?
Feb 6, 2007Hi!
when I'm trying something litle bit more complex thing than string manipulation in calculated field ex: =RunningValue(Fields!SALES.Value, Sum)
It just crashes visual studio when trying to run the report?! I think this could state as a bug in RS?
Sort A Report On A Calculated Field??
Apr 15, 2008
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"
That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this??
Thanks for any advice!
Calculated Field Using Two Data Sets
May 12, 2008Hi,
I'm trying to created a report.
Final report looks like this.
Total Loans/Lines (#)
13,283
Total Commitments ($ MM)
$1,703
Total Outstandings ($ MM)
$1,175
A
B
C
D
F
Bankruptcy
0
$0
$0
0.00%
0.00%
Charge Off
0
$0
$0
0.00%
0.00%
Source table looks like this;
Bankruptcy
0
Charge Off
0
CLTV
131
DSR
102
Exc Total
265
FICO
7
Foreclosure/Repossession
Grand Total
13283
Loan Amount
32
Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)
But it does not let me to use report expression as its not in the same scope.
Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.
Thanks
Create A Calculated Field That Gives Me The Avg 75 Percentile
Mar 22, 2007I'm trying to create a calculated field that gives me the avg 75 percentile.
Right now I get this value by doing the following:
Create data set:
Select top 75 percent <field>
from <table>
Then I create the following calculated field
Avg(Fields!<field>.value,"<data_set_name>")
But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.
Is it possible?
Thanks!
Report - Interactive Sort On Calculated Field
Feb 27, 2008Hello,
I have a report that calculates a field and therefore it does not exist in the dataset.
Can I perform an interactive sort on the textbox that contains this calculation?
Many thanks.
Calculated Field In Footer...running Total
Apr 26, 2007
Hello,
How do I add unique values on the report? For example say I have this in my report:
Customer: Food Purchased: Amount:
Judy Cat Food $12
Sarah Dog Food $13.50
Diane Rabbit Food $17
Jason Dog Food $16
Tammy Dog Food $15
In the footer of the report I want to print a summary box that looks like this:
Product: Number Purchased: Total:
Cat Food 1 $12
Dog Food 3 $44.50
Rabbit Food 1 $17
How do I do this?
Thanks!
Transact SQL :: Use Field Alias In Calculated Fields?
Jul 18, 2015use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
SQL 2012 :: Full Text Index How To Make It NOT To Index Embedded Or Attached Documents
Sep 30, 2015I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Creating A Calculated Field From Within VWD? (Visual Web Developer Express)
Feb 27, 2008I'm trying to create a calculated field using SQL Server Express, from within the Visual Web Developer Express 2008 IED. The field I am trying to create is called "total" and is supposed to be the sum of the subtotal, salestax, and shipping fields.
Is this possible from within VWD 2008? How?
Thank you!!!