Calculation Of Fields Which Contain Nulls

Nov 1, 2007

I currently have 5 fields in the same SQL Server table, 4 of which pull Stock Levels from other tables and the 5th is a Total Stock field which is to add the 4 previous fields.

The problem is that some of the first 4 fields contain nulls as well as figures and when the Total Stock trys to add these together the outcome is Null, which is incorrect.

E.G

COL 1 COL 2 COL 3 COL 4 TOTALSTOCK

2 NULL 0 1 NULL

I have tried to change it to a NOT NULL Column but won't let me.

Is there a way to do an update to change NULL's to 0?

Any help would be grateful

View 4 Replies


ADVERTISEMENT

Help On Calculation Including Nulls

Jun 15, 2007

Hi everybody

I have this sql which calculates the total

MY TABLE IS:

A B C

100 5 3
100 0 5
20 0 0

sql is

A - B + C as total

the result is it gives me only the total of the first row since it has values on the on the B AND C

the result is

A B C total

100 5 3 98
100 0 5
20 0 0

with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...

thanks for any help

View 13 Replies View Related

Efficiency In Inserting Null Values Into Fields Which Allow Nulls.

Aug 9, 2004

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanks

View 2 Replies View Related

Including SQL Server Allow Nulls Fields In Updateable Controls

Jun 13, 2006

When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record!  If I remove the Allow Nulls field I can then edit it!  What am I missing here?

View 1 Replies View Related

Error Selecting Date Convert Fields With Nulls

Aug 3, 2000

I have a select statement which has to convert some date fields stored
as varchar based on critera of adding numbers to the date. What I have below
will return data show below but gives me an error message when it encounters
any data with null in the field. Is there something wrong in my conversion?
Thanks


declare @tdate varchar(10)
declare @rldate datetime
select @tdate = 20000630
select @rldate = @rldate + 2
select patient_.df_admit_date, patient_.df_ppdb_date from patient_
where (convert(datetime, df_ppdb_date, 101)+ 12) >= @tdate or
(convert(datetime, df_admit_date, 101) + 2 ) >= @tdate

19980910 20000626
19981215 20000627
19590114 20000621
20000629 NULL
20000706 NULL
20000711 NULL

(6 row(s) affected)

Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

View 1 Replies View Related

T-SQL (SS2K8) :: Varying Calculation For Fixed Number Of Fields

Aug 19, 2014

I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.

Fields Used: PERIOD ('MON-yyyy') and VALUE

The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))

This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?

View 1 Replies View Related

SQL 2012 :: Concatenate Fields And Insert Comma Between - How To Handle Nulls

Sep 5, 2014

I thought it's easy but somehow got lost, what is the good way to insert that Comma intelligently, depending if any of var value is NULL.

-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'

SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )

View 3 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

Column Allows Nulls I Want To Change No Nulls Allowed

May 16, 2006

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This sux

View 4 Replies View Related

Testing Permutations Of Nulls And Not Nulls

Feb 17, 2008

is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?

View 4 Replies View Related

Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS

May 15, 2008

I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.

The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.

So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).

I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.

Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?

From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.

View 2 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


I have just started using SQL Server reporting services and am stuck with creating subreports.

I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.

For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.

When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.

Am I missing something here? Any help is appreciated.

Thanks,
Sirisha

View 3 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Public Overridable ReadOnly Default Property Fields() As ADODB.Fields

Jan 26, 2008

sir

I have got this error message to establish connction with recordset vb .net, Can you please rectify this

Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'

my code like this


rs = New ADODB.Recordset

rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)


If txtUserName.Text = rs.Fields.Append(userid) Then


MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")

End If


thanks

View 1 Replies View Related

Update Fields With Searched First Date Record Fields

Jul 23, 2005

Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/

View 3 Replies View Related

Transact SQL :: Get One Row From Multiple Based On Fields And Also Get Sum Of Decimal Fields?

Jul 2, 2015

I am using MS SQL 2012.  I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it.  Then I need a total of those added decimal fields. My data is like this:

I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:

ID, provider_name, uniq_id, total_spent, total_earned

Here is sample data:

1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00

I need the results to be just 3 lines:

Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00

But then I would need the totals for the Provider, so:

Harbor, 1800.00, 3800.00

View 3 Replies View Related

Search Multipe Fields, Compounding Fields, Like, Contains...?

Jul 20, 2005

I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M

View 1 Replies View Related

Update Fields With Data From Other Fields In Same Row

Jun 30, 2000

Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.

In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)

I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.

View 2 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

Drillthrough In Calculated Fields Enable When Drillthrough Option Is Disable In Original Fields, Is This A BUG?

Jan 21, 2008

Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.


Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.

But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link

Is this the standard and expected behavior? or its simply a BUG?

Have I done something wrong in my model? and in this case, How I can correct this?

regards.
Julio Diaz.

View 1 Replies View Related

NULLs

Sep 16, 2004

Do NULLs take up space on the Database? I mean is room allocated for them?

View 9 Replies View Related

Nulls

Apr 5, 2004

Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.

View 12 Replies View Related

Nulls

Apr 12, 2008

I have a page with 100 numeric fields. SQL insert crashes lest there is a number in every field. Any way around this?

View 1 Replies View Related

Two Nulls

Feb 9, 2007

Hi

i have a table t1 and wish to have to fk's now i want to be able to set it up so that only one of them can be null example:

t1
id___________fk1________fk2

1____________6__________NULL
2____________NULL_______3
3____________7__________NULL
4____________NULL_______NULL <<<<<<< This should not be allowed

thanks
james

View 2 Replies View Related

Nulls

May 21, 2007

Hi i am new to SQL, and i have a data base thats got NULLS in it
is there a query i can run, which can take the NULL value out ?
anything will be greatly appreciated.

View 14 Replies View Related

NULLS And ''

Feb 6, 2007

Hi,I don't have any specific details as I do not really understand whythis is happening but we have a customer database that we have beenusing and queries on text fields have begun returning empty stringvalues instead of NULL.So for example:SELECT TAB1.DESCRIPTIONFROM TABLE1 TAB1WHERE TAB1.DESCRIPTION IS NOT NULLORDER BY 1may have returned 50 records in the past (purely an example). It isnow returning an additional record first that appears just to be ''.Now I am not too sure where to begin looking into this. ODBCconnections, collation settings? I just am not sure where to begin.The column will not have had '' inserted into it; therefore it shouldbe NULL.I have posted this mainly so I can see if anybody else has seen thissort of behaviour - I cannot see why this has suddenly happened.The collation setting on our server is different to that of thecustomer DB, but this shouldn't make a difference should it?If anybody has any ideas then I can post some more information.Thanks,Paul

View 12 Replies View Related

Nulls

Mar 20, 2007

create table t1(c1 int, c2 varchar(10))insert t1 values(1,'Hello')insert t1 values(2,'')insert t1 values(3,NULL)select *from t1c1c21Hello23NULLselect *from t1where c2 = ' 'c1c22select *from t1where ltrim(rtrim(c2)) is nullc1c23NULLThe last query should have result as following. However sql server2000 does no list row c1 = 2.c1c223NULL

View 6 Replies View Related

Are Nulls Bad

Jul 20, 2005

HiI am probably going to regret asking this because I'm sure you are going totell me my design is bad 8-) ah well we all have to learn....anywayI often use Nulls as a marker to see if certain tasks have been completed.A typical example would be a column say invoice_valuewhen new work is entered and a new record is appended, I leave theinvoice_value column as NULL, so if I want a View for uninvoiced work Icheck for the invoice_value being equal to NULL, and alternatively if I wantinvoiced work, I check for not null. I did it this way to save putting inanother column that needed to be set to TRUE or FALSE. (I do similar thingselsewhere aswell)so far everything seems to work OK but reading some old stuff on Google Iget the feeling that NULLS should be left alone, have I done wrong?many thanksAndy

View 3 Replies View Related

NULLS

Nov 26, 2007

Hello all

I have a colunm - Datatype : nvarchar - Contains: Numeric, characters, strings, date and NULLS

I am getting a the value of each row of this colunm in a variable say @var1

Now I am trying to do some functionalities depending on this variable.

The requirement is if this variable is not null I need to do some stuff.

So I write simple query:

IF (@var1 IS NOT NULL)
BEGIN
......
END

when i am doing this I get an Error:


Error converting data type nvarchar to numeric.

realizing this I change the condition to
IF ( LEN (@Var1) > 1)

then
IF (LEN (@Var1) <> 0)

but no use. I get the same error.

I even use cast
I get the error

Conversion failed when converting the nvarchar value 'null' to data type int.


IF(CAST (@OldValue AS INT)IS NOT NULL)
OR

IF(LEN (CAST (@OldValue AS INT)) <> 0)


Can somebody help in guiding the best practice to do this?





View 8 Replies View Related

Where To Do The Calculation In VB.NET Or In SQL?

May 4, 2008



Hi,

My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?

My manager has handed me a task of converting an excel file she uses in to a web aplication.

While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.


The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).


So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?


Since these calculations are required a produce a result in an online environment, what will be faster?


I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.


Any views that you can offer will be very helpful.

Thanks in advance.

PMA

View 6 Replies View Related

Help W/ Calculation

Aug 3, 2007

I need to calculate the overall GPA for a student in a particular class.


YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000


The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.


Here's my sql:


select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

View 3 Replies View Related

MDX Calculation Help

May 28, 2008

Dimensions:

DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode


Period = 1
Product = 1
Status Code = InStock


Period = 1
Product = 2
Status Code = InStock



Period = 2
Product = 1
Status Code = OutOfStock

Period = 2
Product = 2
Status Code = InStock


In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)


The measure = 1 / 2 or 50%. TIA

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved