T-SQL (SS2K8) :: How To Perform Mathematical Formula Without Using Case
Sep 17, 2014
I am having 4 Columns Qty decimal(12,3),CF1 Decimal(12,3),CF2 Decimal(12,3),Flag TinyInt.
I want to perform following without using case if it is possible.
When value of Flag is 0 then Qty*(CF2/CF1)
When value of Flag is 1 then Qty
And i Don't want to use any functions like isnull,NullIf,IIF even not union or union all.How to do this calculation without using any function.
Actually i am having more then 100000 rows in table and if i use functions then my index might not be called.,that why want to avoid cases and functions.
Entity Value A 2424053.500000 B 1151425.412500 C 484810.700000 Table 2 contains
Entity Formula A (2100*(1-0.0668)*24*mday*10) B (1000*(1-0.0575)*24*mday*10) C (1260*(1-0.09)*24*mday*10)
Where mday is number of days taken from user
I need to calculate the output of value/formula for each entity can you provide me the query for the same
The datatype for formula column is varchar
I do not have the liberty to use cursors or loops.mday will be a input fromt the user say 'mday = 31' ..i need to divide the value in the first table with the computed value of the formula after replacement
I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
I want to evaluate a math formula inside of a function, so I can't use dynamic SQL.
I found that the query on an XML-variable with a literal works well.
DECLARE @sParsedFormula varchar(200); DECLARE @xFormula xml; SET @xFormula = ''; SET @sParsedFormula = '1+2'; SELECT @xFormula.query('3+3') , @xFormula.query('sql:variable("@sParsedFormula")')Output (1st value is correctly evaluated, 2nd not): "6", "1+2"
I can't directly pass @sParsedFormula to the query otherwise I get "The argument 1 of the XML data type method "query" must be a string literal". What is the correct XQuery-syntax to evaluate the SQL:variable?
Is there an easy way to send back to my C# program the pascal case of a field type? If you get the parameters or columns for a table or procedure the type is in all lowercase (varchar instead of VarChar). But in C#, it is in an enum (SqlDbType) that has the variables as mixed case so you can't compare them and just pass the type. You need to figure out what the case should be - varchar should be VarChar and bigint should be BigInt.
Select P.S,E.S,E.R from Pack P(nolock) join Exp E on P.Id=E.O on E.R is null case when E.R is not null then '' else '' end where P.s='PLT000044'
I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.
select ( Column1 ,Column2 ,Column3 ,Column4 ,coloumn5 from Mytable ) x pivot ( case when Column1 = 6 then sum(Column3) else max(Column4) End for coloumn5 in (' + @COLS + ') )p
select case when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC' end from dba.dbhakyedek where username='MC'
This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
The issue I'm having is that the values I need to divide by are in fact, a result set from the CASE statement. It's been a long time since I've done anything like this.
I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:
Case [LastHireDate] When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12' When Month([LastHireDate]) = '1' then '12' When Month([LastHireDate]) = '2' then '11' When Month([LastHireDate]) = '3' then '10' When Month([LastHireDate]) = '4' then '9'
[Code] ....
When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there.
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast 1000 Item1000 X X X X X 1024 Item1024 X X X X X 135 Item135 X X X X X 107 Item107 X X X 106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
hello sir Thank for ur response to me in such a way. I m student of cs in final semester & designing a project for online test series for all subject like mathematics, physics, chemistry, geography. Ihave a pb for storing mathematical questions which includes special symbols in organic chemistry structural farmulas etc.How can we store in database. i m sending a test paper for better understanding. plz help me.I will be hioghly obliged to u for this.
I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.
For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given
2 4 3 6 0
Average: 5
What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?
I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.
I would like to is there any way we can store mathematical equations, that can be typed using equations 3.0, in sql server database as a column. What would be the datatype for the same.
Hi,I would like to know if its possible to add/subtract/multiply/divide values between a cell in a database and a textbox/label on a web form? how would I go about doing this? Also, I would like a number from a cell to appear in a label when a page first loads. How can I achieve this?
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening? Is there a way to bypass this problem?
Long query ( values from 2 tables ) : SELECT MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal FROM DATA data, ATTR attribute, TREE_ELEMENT elm, TREE_ELEMENT subject WHERE data.elmId=elm.id AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 ) AND subject.id=elm.subjectId AND subject.name = ‘test’
Short query ( values from 1 table ) : SELECT MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal FROM DATA data, ATTR attribute, TREE_ELEMENT elm, TREE_ELEMENT subject WHERE data.elmId=elm.id AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 ) AND subject.id=elm.subjectId AND subject.name = ‘test’
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Sorry to ask a stupid question. I have SQL Server 2000 on SBS 2003. I can't find Perform.exe. Anybody know where it should be (I'm sure that you all do!),
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
