Calculated Field And Conditions
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
ADVERTISEMENT
Nov 8, 2005
For 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:
View 3 Replies
View Related
Dec 28, 2006
I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example;
@ClientID Int,
@PmtAmt Money
IF @PmtAmt >= tblSettings.TopAmt THEN
Update tblClients
SET
ClientStatus='High'
WHERE ClientID=@ClientID
ELSE
Update tblClients
SET
ClientStatus='Medium'
WHERE ClientID=@ClientID
ENDIF
How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.
View 3 Replies
View Related
Sep 12, 2007
Hi guys,
My challenge is really 3 problems in 1.
I have Table1 and Table2 which are inner joined with an ID.
Each record in Table1 may have up to two corresponding values in Table2, which are Type and Name.
req1- I want to select Table2.Name twice (or more), with each selection conditioned on a specific Type.
req2- I also want all information pertaining to the same ID to be returned in each row
req3- I want all IDs to be returned, even if Table2.Name are empty for Type1 and/or Type2.
So, the source tables look something like this:
Table1.ID | ……
-----------------------
ID001
ID002
ID003
ID004
Table2.ID | Table2.Type | Table2.Name
--------------------------------------------------------
ID001 | Type1 | NameA
ID001 | Type2 | NameB
ID002 | Type1 | NameC
ID003 | Type2 | NameD
Ideally, my results should look like this:
Table1.ID | Table2.Name (Type=1) | Table2.Name (Type=2)
----------------------------------------------------------
ID1 | NameA for Type1 | NameB for Type2
ID2 | NameC for Type1 | (empty)
ID3 | (empty) | NameD for Type2
ID4 | (empty) | (empty)
Right now, I can only get Table2.Name to display once, and it won’t return the empty values, even with outer joins. The structure that I’m using is:
SELECT …
FROM …
WHERE Table2.Name IN
(SELECT Table2.Name
FROM Table1 INNER JOIN Table2 …
WHERE Table2.Name = ‘Type1’)
Sorry the abstractness, but this is the simplest way for me to express the problem.
Any help will be appreciated, thanks!
View 4 Replies
View Related
Dec 21, 2006
Hi:
I have a data flow task in which i have multiple OLEDB sources going to a one OLEDB destination via UNION component. I also have a derived column component sitting behind the union, because there are some columns, which exist in the destination table, but are not coming from source, but I am responsible of providing values for those fields. The question is that some of those values are conditional, for eg. Lets say we have a column called RecStatusCode in the derived column component. The value for that column depends on a condition/criteria, so I need to query a db and based on the value of another field, I set the value of the recstatus code. How do i accomplish that? I am thinking expressions, but I need clear direction in steps in how to set a value of a derived column based on a criteria(sql statement). Thanks.
MA
View 10 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
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
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
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
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
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
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
Jul 23, 2005
Hiin 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 Related
Jul 20, 2005
I 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 Related
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.
View 1 Replies
View Related
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!
View 5 Replies
View Related
Nov 11, 2006
I 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
View 3 Replies
View Related
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)"
View 3 Replies
View Related
Nov 18, 2015
Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.
I'm getting lost in this expression:
=SWITCH
( (InStr(Fields!Info.Value, CHR(13)&CHR(10) > 0, REPLACE(Fields!Info.Value, CHR(13)&CHR(10)), "x0Dx0A")), (InStr(Fields!Info.Value, CHR(13) > 0, REPLACE(Fields!Info.Value, CHR(13)), "x0D")), (InStr(Fields!Info.Value, CHR(10)
> 0, REPLACE(Fields!Info.Value, CHR(10)), "x0A")) )
Error is:
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,
[Code] .....
View 2 Replies
View Related
Nov 24, 2005
I 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.
View 1 Replies
View Related
May 11, 2015
is it possible to have a field that is calculated using a User Defined Function, and index that field?
View 9 Replies
View Related
Jul 23, 2005
I'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 Related
Jul 20, 2005
I'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 Related
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?
View 2 Replies
View Related
Feb 6, 2007
Hi!
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?
View 4 Replies
View Related
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!
View 17 Replies
View Related
May 12, 2008
Hi,
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
View 15 Replies
View Related
Mar 22, 2007
I'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!
View 9 Replies
View Related
Feb 27, 2008
Hello,
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.
View 1 Replies
View Related
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!
View 9 Replies
View Related
