Transact SQL :: Computing Column Formatted To Decimal (12, 2)

Oct 19, 2015

I have the following a computing column

(isnull(TotalProductSaleCost,0) * 7) / 100

I would like the output to be formatted to decimal (12, 2) not sure how to achieve this?

View 4 Replies


ADVERTISEMENT

Select Qry: 1 Real Value To Formatted Decimal, 2. Replace NULL With String, How Do I?

Jun 14, 2005

Hi.

1.
Have a query that fetches a real value (e.g. 4.3345643)

Let say the field is called TheReal.

How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.

2. In the same query I have a left join as well.
Table 2 retur (sometimes) <NULL>.
Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.

View 6 Replies View Related

Problems With Computing A Primary Key Column

Sep 13, 2006

hi !

Just started playing around with SQL 2000 and I createda sample table called 'actor' which has 4 columns

1. actorID
(formula= LEFT(NEWID(), 3)+ LEFT([actorFirst], 2) +
LEFT([actorLast], 2) + RIGHT(NEWID(), 3))
2. actorFirst
varchar(20) NOT NULL
3. actorLast
varchar(20) NOT NULL
4. actorName
(formula = [actorFirst] + ' ' + [actorLast]

Now my problem is that I want to set a primary key constraint on actorID but it doesn't let me because it the NULL check mark is automatically checked and I cannot check it off ... and I can't set a primary key on something which is allowed to be NULL....

I don't understand why 'actorName' column which is also calculated doesn't have that default NULL checked and locked ....

What am I doing wrong ? Please help ....

View 5 Replies View Related

Formatted Text In A Column;

Jan 9, 2006

Hi All ,

Is it possible to insert the below text into a column and retrieve it in the same format ?

Thanks,

Hari Haran Arulmozhi

TEXT :

CLIENT NAME : ABC Corporation
CLIENT CITY : MUMBAI

================================================
INV_NO INV_DATE INV_AMT
================================================
I100 01-01-2006 Rs.600
I200 01-02-2006 Rs.800
I300 01-03-2006 Rs.1600
I400 01-04-2006 Rs.2600
I500 01-05-2006 Rs.9600



RECEIVED ADVANCE :Rs.10000

View 3 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

Transact SQL :: Converting A Negative Varchar Decimal?

Jul 6, 2015

how to convert -2.0120 to -2 or 3,6789 as 3 ignoring the decimal places.

The input -2.0120 is a varchar.

View 8 Replies View Related

DECIMAL With Scale 2 Truncation Of SSIS Differs From Transact SQL

Mar 14, 2007

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer SELECT 6800 /
464OR




SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

View 7 Replies View Related

Transact SQL :: Values In Table - Convert Varchar To Decimal

Jul 22, 2015

I have one of the sample values in my table. I need to convert below value to Decimal(18,5)

DECLARE @i
VARCHAR
SET @i
= '0.9'

Output i m looking for is 0.90000

View 16 Replies View Related

Transact SQL :: Convert Time In Hours And Minutes To Decimal

Jul 30, 2008

I am trying to convert hours and minutes to decimal and arrive at a sum of time taken. The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.

SELECT DATENAME(weekday, Started) AS Day,        C.Category,     ClientCode,Description,    dbo.FormatDateTime(Started, 'HH:MMS 12') as Started,    dbo.FormatDateTime(Ended, 'HH:MMS 12') as Ended, CONVERT(varchar,TimeTaken,108) AS TotalTimeSpentFROM dbo.Journal JLEFT OUTER JOIN dbo.Categories C ON J.CategoryID = C.CategoryIDWHERE--DATENAME(weekday, Started) =@Weekday  AND Started >= @StartDate ORDER BY Day, Category, Started

View 6 Replies View Related

Transact SQL :: How To Convert Numeric Data Type To A Varchar With No Decimal Place

Aug 31, 2015

So my data is delivered as numeric(9,2)...like 100.00. I have to extract that data and store it as a varchar 0 filled without the decimal place...like 0000010000///I tried the following and it did not work...

RIGHT('000000000'+CONVERT(VARCHAR,[EODPosting].[Amount]),10),

View 8 Replies View Related

Transact SQL :: Convert Today Date To MMDDCCYY To A Decimal And Retain 0 In Front Of Month

Aug 24, 2015

This seems to get the job done...

SELECTRIGHT('00000000'+CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8)

However, when I try to create it and CONVERT it to a DECIMAL, it then loses the "0"

SELECTCONVERT(DECIMAL(8,0),RIGHT('00000000'+CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8))

Is it impossible to convert it to a decimal and retain the leading "0" on the month?

My vendor's spec states..."Business Date Numeric For 8 positions MMDDYYYY"

View 5 Replies View Related

Transact SQL :: Pass Parameter To Convert Function To Format Decimal Precision Dynamically?

Nov 4, 2015

I want to change decimal precision dynamically without rounding value

For example

10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount

[Code] ....

I am getting error as follows......

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'

This decimal precision format value will vary  company to company ...

View 7 Replies View Related

Column With A Number And 2 Decimal Like 10.10 Or 150.30

Nov 15, 2006

Hello,How should I create a column to save data with the folowing format   10.10 or 10.20 or 10.30 or 150.30  or 10 or 150.It is basically  process step in a diagram flow.I tried with decimal but with 10.10 , it removes automatically the 0.Thanks

View 2 Replies View Related

SQL AVG Function Of A Decimal Column

Jan 13, 2005

Hi,

Trying to get a dataset. My select statement is "SELECT Com_year,Avg(GPA) FROM Pilot_Stats WHERE com_source = 0 GROUP BY Pilot_Stats.com_year ORDER BY Pilot_Stats.com_year"

Where Com_Year is an integer (ie 1984, 1986, etc)
Where GPA is a decimal (ie 3.4, 3.0, etc)
Where com_source is an integer

This returns this error when I try to fill the dataset:

Decimal byte array constructor requires an array of length four containing valid decimal bytes.

For some reason it's hanging up on Averaging this Decimal column for some reason...

Anyone have an idea why? Thanks for any help!

View 6 Replies View Related

How To Know If A Column In A Table Has Decimal Values

Apr 4, 2008



Hi Guys,

I want to find out if a ceratin column has values with decimal figures populated? for example say column A has values

10.00
20.00
30.00
50.01

in this case the query should let me know that there is one figure (50.01) with a actual decimal value. So the results of the query should be 1 (since one record has decimal figures <>.00)

Cananyone help

Sonny

View 10 Replies View Related

SQL Problem Updating A Column Of Type DECIMAL(11,4)

Apr 29, 2005

This problem is strictly an SQL problem, and I am about to lose my mind over it.  I have a table named Inventory that has a column named PartialQty which is of type DECIMAL(11,4).  I have shortened my query for simplicity, but here it is.
DECLARE @pqty1  DECIMAL(11,4)
SELECT @pqty1 = PartialQty FROM Inventory WHERE InventoryID = @invIDPRINT @pqty1 UPDATE Inventory   SET PartialQty = 6.5513   WHERE InventoryID = @invID
SELECT @pqty1 = PartialQty FROM Inventory WHERE InventoryID = @invIDPRINT @pqty1
Before this query is ran, the value in PartialQty is 1.2345.  If I run this 2 times in a row, this is the output I get:
RUN 1:
1.2345
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
6.0000
 
RUN 2:
6.0000
(1 row(s) affected)
6.5513
 
I don't understand 2 things.  One is why the first run displays (1 row(s) affected) 4 times even though there is only one record with that InventoryID.
And the other thing I don't understand is why the first time I run it, it loses all of the values after the decimal point. 
Please help,
Scott

View 2 Replies View Related

How Do I Limit Decimal Points In Computed Column?

Aug 31, 2007

How do I limit the decimal points in a computed column. SSE 2005 seems to override the precision and scale I set in either GUI or by creating the table by query.

Thanks in advance for helping.

Scott

View 2 Replies View Related

Truncate Decimal In Derived Column Task

Apr 10, 2008

Hi,

Is there a way to truncate a number to a certain number of decimal places in the SSIS Derived Column component (similar to TRUNC in excel)?

Thanks!

View 4 Replies View Related

Overflow Problem On Decimal(38,14) Column With SQL2005 SP2 With Min Value Of 0.1 And Maximum Of 3.0

Apr 18, 2007

On table TABLENAME, I have increased the size of column C to decimal(38, 14); maximum size and an excessive amount of precision.
If I do a 
SELECT MAX(CONVERT(DECIMAL(7,2), A) / B) FROM TABLENAME
SELECT MIN(CONVERT(DECIMAL(7,2), A) / B) FROM TABLENAME
I get 3.0000000000000 for the MAX and 0.1000000000000 for the MIN value, yet if I update
UPDATE TABLENAME SET C = CONVERT(DECIMAL(7,2), A) / B WHERE B <> 0
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Obviously I have obscured the column names and table name, but I am puzzled as to why values in the range 0.1 to 3.0 should give an overflow error.
I am using SQL2005 SP2

View 7 Replies View Related

Rounding To 2 Decimal Places In Derived Column Transform Editor

May 15, 2008

I want to replace the contents of a value column with itself but rounded to 2 decimal places.

The current column is a double and I have tried to perform this using the following expression but it fails to work.






Code Snippet

Round(cc_vl,2)
How should I achieve this?

View 7 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Adding Implied Decimal Without A Derived Column From Flat File Datasource

Apr 18, 2007

We are importing Flat file data from our Mainframe system. We have a lot of money amounts coming in, but the mainframe does not store the decimals in the flat file. So for example a row in the file might look like this:

+0000007894-0000000563

Where the first value is $78.94 and the second value is -$5.63

Is there anyway to have the Flat file connection manager put in the decimal place for me, or do i have to create derived columns for each column and divide it by 100? There like 50-100 columns per file, so i'm looking for a better, quicker way.

Thanks in advance.

John

View 12 Replies View Related

Storing A Value, Vs Computing A Value

Sep 5, 2007

Hi,

I'm having a DB designed for me, and I'm inspecting it and wondering what in general is the better way to do this.

We have a product, which we are counting "product views". The DB designer has created columns called "view_today" and "views_alltime".

I specified I wanted a normalized database, I'm thinking this is technically not normalized ? Am I correct ?

Wouldn't it be better to have a query that counted the views off the logging table ? I can't see any advantage to doing it the way its been designed except to save time.

Thanks for any input !

Mike123

View 3 Replies View Related

Grid Computing

Jul 20, 2005

Has anyone here heard or come across an article or write up about GridComputing in SQL Server 2000?Bharathihttp://www.vkinfotek.com

View 2 Replies View Related

T-SQL (SS2K8) :: Get Script To Output Only 2 Decimal Places For Amount Column From Select Statement?

Jul 22, 2015

I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?

Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',

[code]....

View 2 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Computing The Queried Output

Mar 11, 2008

hi please help me,i have a table queried using this sql,  select name,(select count(*) from myTable a where a.name = r.name ) as Total, (select count(*) from myTable b where b.name = r.name and dnum > '1') as Used, (select count(*) from myTable c where c.name = r.name and dnum < '1') as remainingfrom myTable r group by namebut i need one more thing in this table that should look like this,nameTotalUsedRemainingPercentageA126650%B2021810%C150150%  this is to add the BOLD field from the above table, but my problem is that the computation is "Used / Total = Percentage%"so how can i do this, please help methanks 

View 4 Replies View Related

Computing 'SUM' On 'DATETIME' Datatype

Jan 5, 2006

hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'

Can somebody pls help me with this?

Thnx in advance.

View 5 Replies View Related

Computing Business Hours

Mar 16, 2004

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

Essentially I have a number of different alternatives, and have found some potential solutions, including:

www.dbforums.com/arch/7/2003/9/914261

However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

Additionally, I would like to remove Holiday's from the calculation for service level as well.

I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

The DTD I have for the Holiday table is as follows:

CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')


Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

Thank you in advance

View 13 Replies View Related

Computing Difference Between Two Rows

Jul 6, 2013

I encountered a tricky problem. The original data, say, table_o, is shown below:

IdsStatusDate
ID1402-May-13
ID1310-May-13
ID1216-May-13
ID1120-May-13
ID2308-May-13
ID2210-May-13
ID2119-May-13

The final resulting table, e.g., table_f, is:

Ids4->3 3->2 2->1
ID18 6 4
ID2NULL 2 9

The values in the final table are the days used by each ID transferring from status i to status i-1. E.g., ID uses 8 days (10-May-13 - 2-May-13) to go to status 3 from status 4.

It is hard for me to come up with a table like the final table, although I know that the difference between two adjacent rows can be computed by using self-join and timediff().

View 3 Replies View Related

Computing New Variable From Other Variables

Sep 6, 2007

Dear All.
I'm a fairly new SQL programmer so apologies if this is a silly question.

I'm trying to create a new column/variable from 3 other variables where the new column = column 1 unless column 1 is blank, then = column 2, unless column 2 is blank, then = column 3.

But I don't know where in my query to begin building this. Should I build it in a subquery? Thanks in advance for any replies.

View 2 Replies View Related

Computing The Sum Of Multiple Columns

May 17, 2008

Hi,

I want to create a view where I can calculate the sum of a couple of bit value columns,
aswell as keeping track of the total number of bits set to true.

Here is an example:





dbo.Band


BandID int
Name nvarchar(50)
Country nvarchar(50)




dbo.Record


ID int
Name nvarchar(50)
BandID int
Label nvarchar(50)
InProduction bit
InSkodne bit
From these tables I created this view:





dbo.TestView


SELECT dbo.Band.Name, dbo.Band.Country, dbo.Record.Name AS Recordname, dbo.Record.Label, CONVERT(int, dbo.Record.InProduction) AS InProduction,
CONVERT(int, dbo.Record.InSkodne) AS InSkodne, CONVERT(int, dbo.Record.InProduction) + CONVERT(int, dbo.Record.InSkodne) AS Total
FROM dbo.Band INNER JOIN
dbo.Record ON dbo.Band.BandID = dbo.Record.BandID
I use the convert function to be able to use SUM() across my bit columns, which works fine. Problems is I´m not sure that the way I´m creating the TOTAL column is the best way to go. Any other ideas?
I´m having some problems using this view and the TOTAL column in particular when referencing this view from applications outside SQL Server...

View 9 Replies View Related







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