Math Between Columns
Jun 30, 2006I need to create a new column where I subtract one column by another.
I've been looking fo that for a while now, can someone explain how to do this?
ADVERTISEMENT
Doing Math Across Table Columns, Dealing With NULL
Oct 20, 2005Christine the Pharmacist writes "Please pardon as I am not horribly advanced with SQL...
Using SQL Server 2000, Windows 2000, Service pack 4
I'm trying to figure out marketshares for "preferred" products. I have a table put together by hospital and month of medication use with the totals of each drug dispensed in columns as well.
(example table below)
Hospital Month Drug A Drug B Drug C Drug D
A 7 5 10 58 73
B 7 NULL 26 98 43
etc.
** Drug C and Drug D are the preferred drugs
I'm getting correct preferred % in all cases except when a value is NULL (result is NULL). I'm using an aggregate (sum), which should ignore NULLs, but it ignores NULLs within a column, and not ignoring across columns. I've tried setting concat_null_yields_null off and I'm still getting NULL (since I'm using the plus sign).
This is my admittedly confused select statement, suspect this is where my problem lies (problems with saying "sum", but using "+" as well?):
select a.Hospital,a.month,convert(float,sum(c.Drug_C+d.Drug_D))/convert(float,sum(a.Drug_A+b.Drug_B+
c.Drug_C+d.Drug_D)) as preferred_share
Thanks for any advice!"
Math Or Text
Dec 13, 2006in the below sql why is year(classdate) " + " a " + " MONTH(classdate) a math command giving me 2006 - 12 = 167
and not "2006/12" as text? please help me
cmdGetCat = New SqlDataAdapter("SELECT DISTINCT year(classdate) " + " a " + " MONTH(classdate) AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ", conNorthwind)
Sql Math Question
Nov 18, 2007How do i update a sql table so that the result cannot be less than zero?for example, lets say I have the column "Number"I have a sql update statement that subtracts 1 from number:"Update oTable SET Number = (Number - 1)"Except that Number cannot be less than zero. Is there a way to do this in sql statement so that I don't have to have a select statement just to check that Number is greater than zero to begin with? Thanks
View 4 Replies View RelatedDoing Math On Tables
Oct 26, 2004Hello everyone:
I have a database for one of my websites, a picture rating site. Anyways, right now there are quicte a few tables, and I was wondering how to give the server a break and was wondering if this was possible:
Basicly I have a members table, and a votes table. Members will rate other users pcitures on a scale of one to ten, then the votes will be inserted into the votes table. The only problem with this is that calcuating all the votes a user has can put a straing on the server. I was wondering if it would be possible to create a math column in the members table that would automaticly figure out the users average and having it stored in a field in the members table, so all I would have to do is query the members average located in the mebers table, rather than tallying all the votes in the votes table for each member.
Hope this makes sense, a tutorial or any suggestions would be great!
Thanks
Some Math Logics
Feb 2, 2004I have 2 questions.
1) Lets say we have a table
CREATE TABLE TEST
(
N INT
)
These table have 10 records - the numbers from 1 to 10.
I need 1 query ONLY which will update the table and make it with 100 records - the numbers from 1 to 100.
2) How with 1 query ONLY i have select only the prime numbers
Date Math In SQL - How To Do?
Jan 16, 2006I have some dates stored in a field named "visit"
In a select they show as the format:
2005-07-28 10:45:00.000
So I need to write a query that will select Visits that are more than 90 days old. I thought it might be something simple like:
Select * from patientVisit where ((getdate())-Visit>90)
But that is pulling all visits not just 90 day old ones.
How do I pull the current date/time and compute the cutoff date time that would be 90 days prior for my selection Where clause?
Thanks
Math Functions
Mar 31, 2006Hi,How to find the list of SQL math functions in SQL Server 2005?ThanksKai
View 2 Replies View RelatedMath Error
Nov 23, 2006select convert(float,'1.2334e+006')1233400.0select convert(decimal(20,2),'1.2334e+006')Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric.can I set some options arithabort etc to have a workaround to thisproblem?Thanks.
View 3 Replies View RelatedMath Notation In Db?
Dec 1, 2007I'm not a professional dba or dbd, but I'm proficient in the basics ofdatabase design and sql. I want to create a database of mathdefinitions, and I'm wondering how one would go about creating adatabase that contains mathematical notation (and I'm not just talkingabout basic symbols where I could get away with ascii code). I needto be able to insert a wide variety of mathematical expressions, fromfractions to integrals, into fields (just like you can enter in-linemath symbols in MS Word using equation editor). I have no clue how togo about this. Is it a matter of developing certain programmingskills/languages? Would such a capabliltiy be proprietary (dbms-specific)? Is it possible at all? Any help would be appreciated.Thank you.
View 3 Replies View RelatedMath Function
Jul 20, 2005Hi AllI'm trying to find a math function (if it exists) in SQL Server. If itdoesnt exist, then maybe someone can tell me what its called so I cando a bit more reading on itBasically I want to do this:Parameter Components1 12 23 1, 24 45 1, 46 2, 47 3, 48 89 1, 8and so onI'd like to be able to call a function and it would return true orfalse like sofunctionname(1, 9) = trueso 1 is a component of 9functionname(2, 9) = falseso 2 is not a component of 9functionname(4, 5) = trueso 4 is a component of 5If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,I'd appreciate it!Thanks in advanceSam
View 2 Replies View RelatedDate Math
Jan 9, 2008Below is my table layout, query, and results. I need to perform some date math to display how much time is elapsed between locations as patients are checked into each care_unit. I believe I could use the unique careunit_key but am not sure how to go about this.
[srm].[CDMAB_CAREUNITS]
[CAREUNIT_KEY] [decimal](37, 0) NOT NULL,
[EPISODE_KEY] [decimal](38, 0) NULL,
[CARE_UNIT] [char](20) NULL,
[ROOM_NO] [char](5) NULL,
[BED_NO] [char](5) NULL,
[DATE_IN] [datetime] NULL,
[TIME_IN] [char](6) NULL
select
cu.careunit_key,
cu.care_unit,
convert(nvarchar,e.ADMISSION_DATE, 101) as Admit_Date,
convert(nvarchar,e.EPISODE_DATE, 101) as Discharge_Date,
convert(nvarchar,cu.date_in,101) as date_in,
substring(cu.time_in, 1,5) as time_in,
convert(nvarchar,cu.date_in,101) +
convert(datetime,substring(cu.time_in, 1,5)) as date_time_in
FROM srm.episodes e
inner join srm.CDMAB_CAREUNITS cu on cu.episode_key = e.episode_key
inner join srm.item_header ih on ih.item_key = e.episode_key
inner join srm.patients p on p.patient_key = ih.logical_parent_key
where e.account_number = '11111777777'
CAREUNIT_KEY
CARE_UNIT
ADMIT_DATE
DISCHARGE_DATE
DATE_IN
TIME_IN
DATE_TIME_IN
1488831
3
12/10/2007
12/11/2007
12/10/2007
3:00
12/10/07 3:00
1498258
S4C
12/10/2007
12/11/2007
12/10/2007
5:54
12/10/07 5:54
1498406
SREC
12/10/2007
12/11/2007
12/10/2007
11:05
12/10/07 11:05
1498472
S6W
12/10/2007
12/11/2007
12/10/2007
12:37
12/10/07 12:37
Doing Math With Cells
Apr 19, 2007Good day all
Does anyone know if there is such a quary that can be written which would add up(or any math functions) a line of cells (on different rows) similar to that of working with a excel document?
If so please steer me towards the correct syntax for this.
Regards
Rob
Date Math
Mar 12, 2008Hi,
I have a column coming from DB2. It is the time is stored as 6 decimals of a second. So the value I got coming in is 44846(DT_I8), which needs to be divided by 60 to get minutes (747 minutes remainder 26 seconds). Then divide 747 by 60 to get 12 hours remainder 27 minutes. Thus the time is 12:27:26.
I have got a dervived column doing (DT_R8)(SUBSTRING((DT_WSTR,8)PSCCLOGTIM,1,5)) / 60 but the answer I am getting out is 747.433333. Close, but not close enough. Now I assume my problem has something to do with the Math being done on actual numbers not Time based numbers.
Anyone got any ideas on where I am going wrong and what the expression should look like?
SQL Math Functions With COUNT() - Can Anyone Help, Please?
Mar 28, 2007I have been trying to make a database that counts up and down votes (like eBay ratings or reddit votes). I think (hope) I have got the database design right.
I know that you can perform math functions in SQL, but I want to use two COUNT()s from the same table and subtract one (the down votes) from the other (the up votes).
I have been learning ASP.NET 2.0 and it's going well, but I really need help with this. I asked a question on this forum before and the answers were great and really helpful.
If anyone can help that would be great. Thank you.
Jack.
Performing Math Problem
Nov 19, 2003I need to perform some division on the results of two alias columns that perform counts in a query. What is the best way to do this?
View 7 Replies View RelatedMath Is Driving Me Nuts
Jul 20, 2005The business rule is, the sales manager is commissioned on the avg. numberof appointments set up per salesrep per day during the month.I have 2 tables: The UserLog table records only 1 entry per day per user(salesrep). This will log how many salesreps worked a particular day. Thesecond table logs any appointments set up.UserLog: ID, UserName, EnteredTimeAppointment: ApptID, EnteredTime, ApptDateI figured that, for a given date ranged, I could1. sum the number of appointments2. sum the number of days worked2. sum the salesreps / number of days = avg number of salesreps per day3. number of appointments / avg number of salesreps per day = avg numberof appointments per sales repBut this logic is flawed. If I average out every day and then take anaverage of this daily average, I get a different result. Any ideas on howto best solve this problem?Thanks.
View 7 Replies View RelatedMath - Division Question..
Dec 6, 2007To set the stage, Tables are set up as such for this question:
-----------
Table: Answers
ID - Integer - Auto
Answer - Integer
QuestionID - Integer - FK from the QuizMaster table
StudentID - Integer - FK from the Students table
Table: QuizMaster
ID - Integer - Auto
Answer - Integer
Table: Students
ID - Integer - Auto
StudentName - Varchar(50)
----------
I would like to have an either a sql statement (1st) or a stored procedure (2nd) that would give me the percent correct for each student on the test.
In Access I could cheat and use this:
Code BlockSELECT Students.StudentName,
Count(Students.StudentName) AS TotalCorrect,
(SELECT Count(QuizMaster.ID) FROM QuizMaster;) AS TotalQuestions,
([TotalCorrect])/([TotalQuestions])*100 AS PercentValue
FROM (Answers INNER JOIN Students
ON Answers.StudentID = Students.ID)
INNER JOIN QuizMaster
ON (Answers.Answer = QuizMaster.Answer)
AND (Answers.QuestionID = QuizMaster.ID)
GROUP BY Students.StudentName
ORDER BY Students.StudentName, PercentValue;
Which is composed of the following...
Code Block
SELECT Students.StudentName,
Count(Students.StudentName) AS TotalCorrect
FROM (Answers INNER JOIN Students
ON Answers.StudentID = Students.ID)
INNER JOIN QuizMaster
ON (Answers.Answer = QuizMaster.Answer)
AND (Answers.QuestionID = QuizMaster.ID)
GROUP BY Students.StudentName;
SELECT Count(QuizMaster.ID) AS TotalQuestions
FROM QuizMaster;
([TotalCorrect])/([TotalQuestions])*100 AS PercentValue
But... that wont fly in SQL Server...What would work without using temp tables?
What would be the EASIEST way to do this?
Another way someone suggested is:
Code BlockCREATE PROCEDURE GetStudentGradeByID @StudentID Integer
AS
BEGIN
DECLARE @TotalQuestionsAsked as Integer;
DECLARE @StudentName as VarChar(50);
DECLARE @QuestionsCorrect as Integer;
DECLARE @PercentCorrect as Float;
set @TotalQuestionsAsked = select COUNT(*)FROM QuizMaster;
set @StudentName = select StudentName from Students where ID = @StudentID;
set @QuestionsCorrect = select count(Answers.ID) from Answers LEFT JOIN QuizMaster on Answers.QuestionID = QuizMaster.ID where StudentID = @StudentID AND QuizMaster.Answer = Answers.Answer;
set @PercentCorrect = (CAST (@QuestionsCorrect AS FLOAT) / CAST (@TotalQuestionsAsked AS FLOAT)) * 100.0;
select @QuestionsCorrect as TotalCorrect, @StudentName as Student, @TotalQuestionsAsked as NumQuestions, @PercentCorrect as CorrectPct;
END
But I need this as a datasource for a datagrid that would show all students etc... so using a parameter isn't really what I want.
So... could a stored procedure work for this?
Please Help Me W/ My Query, Simple Math!
Jan 29, 2008hi,
declare @query1 varchar(1000)
declare @query2 varchar(1000)
set @query1 = 'select count(*) from table1'
set @query2 = 'select count(*) from table2'
select @query1 - @query2
basically, i want to show the difference of this 2 select statements.. how do i do it?
thanks in advance!
Interesting Math In A T-SQL Query
Oct 2, 2007
The value in the table of one DB is 17869 sq. ft. Now to insert this value in a new table of other database the reporting basis is 1000 i.e I need to do 17869/1000 = 17.8 so I have to take the value as 18. Another thing to be kept in mind is the value in the new table should have leading Zeroes. If the value is 18 it should be displayed as 0000018 ( data type in new table is Varchar(7) and in old table char (9) ). What can be the best way to implement this??
Make Math Operations With DateTime
Sep 23, 2007hi,
how can i make mathematical operations with the DateTime format from thw Sql? -- this is the format 9/6/2007 11:09:00 PM --
how can i substract 30 days from that date and know the resulting one?
if i have two dates, how can i know what number of hours and minutes are between them (if they are fewer than 24), or what number of days and hours and minutes are (if the difference is grater than 24 hours)
please help me, thanks
Math Over Datetime In Varchar Format??
Jun 17, 2008Hello,
I have this column of time values:
00:00:03.3592675
00:00:00
00:00:03.8592515
00:00:03.6873820
00:00:03.8436270
00:00:03.3436430
It is in varchar format. I tried converting it to datetime but get an invalid format error message.
How can I sum up these values and average them out?
Thanks!
--PhB
Evaluating Varchar String With Math
Mar 5, 2008In reference to this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50392
madhivanan asks for sample table data to avoid using a cursor. I could really use this, so here is the table
CREATE TABLE #T (
FORMULA VARCHAR(15),
ANSWER decimal(15,8)
)
INSERT INTO #T SELECT '1', NULL
INSERT INTO #T SELECT '1.0 + 2', NULL
INSERT INTO #T SELECT '1/2', NULL
INSERT INTO #T SELECT '1/2+2', NULL
INSERT INTO #T SELECT '1/(2+2)',NULL
INSERT INTO #T SELECT '3*7.5', NULL
INSERT INTO #T SELECT '3/2*.6-2', NULL
INSERT INTO #T SELECT '(1-1)', NULL
SELECT * FROM #T
-- RUN THE MATH FUNCTIONS, UPDATE THE TABLE'S ANSWER COLUMN WITH THE ANSWER
SELECT * FROM #T
DROP TABLE #T
I would expect this output:
FORMULA ANSWER
====================
1 NULL
1.0 + 2 NULL
1/2 NULL
1/2+2 NULL
1/(2+2) NULL
3*7.5 NULL
3/2*.6-2 NULL
FORMULA ANSWER
====================
1 1.00000000
1.0 + 2 3.00000000
1/2 0.50000000
1/2+2 2.50000000
1/(2+2) 0.25000000
3*7.5 22.50000000
3/2*.6-2 -1.10000000
(1-1) 0.00000000
Any help appreciated, thanks,
Pseudocode To SQL Stored Proc Math Operation
Jan 30, 2006How do i implement this pseudocode in a SQL stored proc?
In my stored proc, intDS and intSS are the corresponding locally declared variables: @cnt1 and @cnt2 (both are int's).
double intRating = 0;
if (intDS != 0)
intRating = ((intDS - intSS ) / intDS) * 100;
if (intRating < 0)
intRating = 0;
intRating = RoundUpDown(intRating);
The last line should round up or downd the decimal value, if it has decimals, to the next round/whole number... e.g. 28.4352635 to 28.
Thank you!
Applied Math Problem - Creating A Cut List
Jan 28, 2008I realize this gets into business logic, but a DB based solution might be best. Possibly some pre-calculated measurement tables.
Business problem: Creating a cut list for a window covering (blinds) system. It's preferably a fast calculation I could execute while the user awaits "order confirmation and pricing". A .5 second delay per order would be acceptable.
An order can have unlimited number of line items, but typically between 1 and 20 (number of windows in a single house).
(For this problem) the raw material is man-made, and therefore is always the same length for a particular pattern/color (take 12' for this example; we stock patterns in 9', 12', or 16' but it's always the same for a given pattern/color). I need to tell the cutters how to use the rawmaterial with minimal waste. Note: The saw blade takes 1/16th inch.
For each line item; I have pre-calculated the exact cut size and number of slats. Measurements are in inches at 1/8 (.125) inch increments. The length of raw material length we stock for this pattern is an easy link.
The goal is to average less than 6 inches waste per 12' piece of raw material.
Example order:
5 line items for pattern/color FAUX/white, stocked in 12' lengths.
Line 1: 34 slats at 35.125 inches.
Line 2: 36 slats at 36.875 inches.
Line 3: 50 slats at 42 inches.
Line 4: 55 slats at 70 inches.
Line 5: 52 slats at 30 inches.
The resulting cut-list-workorder would evaluate all lines together and tell them how many 12' slats to pull and how to cut them with minimal material waste.
My question:
I'm left wondering if a database style pre-populated answer list (or some other database based solution) might be a lot faster and simpler than some chess-style complex trial-and-error array based VB algorythm (that I haven't even begun to imagine how to write). If so; how would it look?
In addition; the cutters get something like $10/hr so we can't make it too complex. I'd rather waste a little material and avoid undue confusion, but not much. With $5+ million in this material per year, excess waste can really add up. Imagine the difference between 12% and 15% waste. It's an area worth investing in.
Sorry; I know it's not a simple question. Someone might find it interesting enough to solve, or at least provide hope for a DB solution. At present; I just can't imagine it.
T-SQL (SS2K8) :: XQuery Syntax To Evaluate Math Formula
Feb 25, 2015I 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?
Need To Convert NULL Values To 0, (zero) In Order To Perform Math Calculations
Feb 20, 2007
Using a reporting services model/report builder we have two related tables:
- Fundings, (parent)
- Draws, (child)
Report Builder reports that subtract "Total Fundings.Amount", (which is SUM(FundingAmount)) from "Total Draw Amount", (which is SUM(DrawAmount)) to get a balance work as expected except when there are no Draw rows, in which case a NULL is returned. Obviously we want to convert NULL values of "Total Draw Amount" to zero so that when subtracted from "Total Fundings.Amount" the correct value is displayed. I've searched for a function similar to COALESCE (Transact-SQL) in report builder but found nothing.
Can anybody help me with this?
Thanks
Bruce
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
DB Design :: Create Two Column With Math Function In One Column?
Oct 5, 2015How Can I Create Two Column with Math Function In one Column Like Below.
Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key,
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total
)
Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?
Apr 29, 2015I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match
Aug 27, 2014I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns
May 26, 2015Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
Hiding/Showing Columns Based On The Columns Present In The Dataset
Jun 27, 2007I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
Can I do that??
Any reply showing me the right way is appricited.
-Thanks,
Digs