Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Formatting Decimal Places In A Query In MS SQL


Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Formatting A Float Variable To 2 Decimal Places
Hey,

I am filling a temp table with various float variables and I need to format one particular column to 2 decimal places.

Does anyone know the correct syntax to do this, and should it be done before filling the temp table or when I select what I needs from the temp table?

Thanks

View Replies !   View Related
Set Up Query To Return Only 2 Decimal Places?
Hi. I have a query that returns several averages and percentages. Is there some way to set up the query so the results only go to 2 decimal places? Here is a sample of the query I am using:
Code:

$query = "SELECT COUNT(deal_id), SUM(vs), SUM(vs)/COUNT(deal_id)*100, AVG(fin) FROM sales GROUP BY salesperson";



It works great,except the results are several decimal places long, and I need it to be only 2.

Any help appreciated.
Cori

Never mind, I found it muhself....

FORMAT(AVG(fin),2) works perfectly!!

View Replies !   View Related
2 Decimal Places On Sql AVG ()
Hi,
I am trying to get my string to 2 decimal places...
Here is the code:string sumSQL = "SELECT CONVERT (float,sum(callTime)/count(callTime)) as CAVG FROM leads where agent = '" + agent + "' and " + dates.Text;
 SqlCommand sumCMD = new SqlCommand(sumSQL, conn2);
object null1 = sumCMD.ExecuteScalar();if (null1 != null)
{string avgS = sumCMD.ExecuteScalar().ToString();Response.Write(avgS + "<br>" + "</td></tr><tr>");
}
 
Any Help appreciated...
Doug

View Replies !   View Related
Decimal Places
I have many columns of data. They are all using the money datatype. When Ienter a value into a column such as 32.00, it only shows up as 32. Whenoutputted to an ASP page using a query it also only displays 32. If thedata is 32.33, then the display is 32.33. How do I get it to display 32.00?Thanks!DarrenMCP

View Replies !   View Related
Decimal Places
How do i get these decimal working?


select
cast(2/5 as decimal(5,2))
,2/5
,2*5
,cast(5/2 as decimal (4,2))


thank you

View Replies !   View Related
Decimal Places
is there a way with a float datatype to choose the number of deicmal places to use

View Replies !   View Related
Decimal Places
I am running the following query and getting result 150.76000 but I am desired 150.76. How can i get it.
 
SELECT ROUND(150.75600,2)
Results:
--150.76000

But I need the following result
150.76

View Replies !   View Related
Moving Decimal 2 Places To The Right.
Hello, I have a datatable with a column of decimal numbers 0 - 1.  I want to move the decimal 2 places to the right to make the column a percent column.  I don't know how to do that.
Thanks
Steve

View Replies !   View Related
Want To Get Results Without Decimal Places
Hi,

The underlying table for my fact contains a Sale field whose data type is decimal.

In the cube editor, I have tried a few options (both for Data Type and Display format) but in my front-end I am still getting the results with 2 decimal places.

Can someone kindly help me how I can get the results in a rounded form so that the decimal places are not shown e.g. instead of seeing 12,345,678.98 I want the results rounded as 12,345,679.

If (and i really hope not) it boils down to using MDX in the front end then can u kindly guide me since I can only spell MDX at the moment :(

Many thanks in advance for your help.

View Replies !   View Related
Help! SMALLMONEY:Too Many Decimal Places
Hello all...

I am new to SQL 7, and am having trouble with the money and smallmoney data-types. When I pull data from these columns, it has four decimal places, and I need only two (four is screwing the perl code up). What do I do now?

All help much appreciated!

View Replies !   View Related
How Can I Always Display 2 Decimal Places?
In MS SQL Server 2000 T-SQL, how can I always display 2 decimal places?

e.g.

I wish to display

25 as 25.00
or

43.6789 as 43.68.

Any help would be greatly appeciated.

View Replies !   View Related
Format To 2 Decimal Places?
Hi, i need to display my data in 2 decimal places but now i'm getting results after some calculation (for eg. 2.336224). How can i round it off to 2.34?

View Replies !   View Related
Decimal Places In Node_Description??
There's been several good posts on using the node description of a model as the end user description for a specific cluster. My model uses a number of continuous input columns defined as currency from a fact table in the source cube. After processing, the node description has elements that look like this:







-0.5799759795 <=Interest Expense <=0.8397462488 ,

Since the source data is currency, this makes the node description look a little strange. The data type in the model is set as double. The precision implied by the description is not what I want the model to consider. In the case above, the difference between the numbers listed is not significant.

It would be great to have a better node desciption that doesn't imply so much precision, but the bigger question is why does the cluster model turn currency types into doubles. Should I set the data type to long in the model so that cents are ignored? I know I should probably use discrete inputs, but I don't want to have to discretize the currency values in the cube since this would require me to set up fact dimensions for each currency column in the fact table.

View Replies !   View Related
Convert Value To 2 Decimal Places?
 
Hello, is there a way to convert the value to just 2 decimal places, I created the report in Reporting Services and it has quite a few digits to each value.  I looked at the table and found that the data type is {Float}.  Is there a way to convert the values to just 2 decimal places?..Thank You.

View Replies !   View Related
Decimal Places Displayed
Hi,

I'm running the following query against a SQL Server 2003 database to receive the results below:

SELECT     PayPeriod AS [Pay Period], SUM(PayHours) AS [Pay Hours]
FROM         EmployeePayHours
GROUP BY PayPeriod

Pay Period                  Pay Hours713                    80

714                    120

717                    59.5

718                    80

719                    80


 A colleague of mine, however, is running the same query against the same database (using a different machine) and gets the following results.

Pay Period                  Pay Hours

713                    80

714                    120

717                    59.500000000000021

718                    79.999999999999972

719                    79.999999999999972

Is there a setting somewhere that needs to be changed?  Thanks.

 

 

 

View Replies !   View Related
Convert Variable To Only Two Decimal Places
Dim subtot As Double
Dim tax As Double
Dim tot As Double
subtot = "0.00"
Dim sql As String
sql = "SELECT items.qty, products.descrip, products.price FROM items INNER JOIN products ON items.productid = products.id WHERE (items.orderid = " & Request.QueryString("oid") & ")"Dim objConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|AllStar.mdf;Integrated Security=True;User Instance=True")
Dim cmdCustomers As New SqlCommand(sql, objConn)Dim dataReader As SqlDataReader
objConn.Open()
dataReader = cmdCustomers.ExecuteReader(CommandBehavior.CloseConnection)While dataReader.Read
subtot = subtot + (dataReader.GetValue(0) * dataReader.GetValue(2))
End While
tax = (subtot * 0.07)
tot = (subtot + tax)
Label1.Text = subtot
Label2.Text = tax
Label3.Text = tot
----------------------------------------
How to a convert the variable tax to just two decimals?
I tried label2.text = CType(tax, Double)
but that didn't work either
Thanks in advance

View Replies !   View Related
Help Withrounding Money To 2 Decimal Places?
I have a table with a money field that had previously been running calculation and storing the data into the database's money field. Since this field supports 4 decimal places, it was storing 4 decimal places worth of data. I have since cleaned up my insert routine to round everything up to two decimal places and it only inserts the rounded values. I now have to go back and update the old data with the two decimal place rule. How would I go about doing this?

OLD---------------------------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16

View Replies !   View Related
Decimal Places For Integer Divisions
SELECT 1/2 returns 0 in transact sql instead of 0.500000

How do I get it to return 0.5000 ?

View Replies !   View Related
Fixed Decimal Places Without Rounding
What is the best way to force a 2 digit decimal place without rounding?
For example select price*UOM returns
47.1294
3.255
.5
8
.49

What i want to be returned is
47.12
3.25
8.00
.50
.49

Thanks,

Jim

View Replies !   View Related
Money Field Decimal Places
I have a field in a database which is a datatype Money. When I run a select query the data is coming back with 4 decimal places like 100.0000 but I only want 2 decimal places like 100.00.

Anyone know how to get this?
macca

View Replies !   View Related
Decimal Places On Money Values
Hi

I have an SQL search that is converting two values to type money. I want it to show two digits after the decimal point but am getting inconsistent results. The first value is as follows:

tblInventoryItem.itemcost as originalcost (the column is datatype money)

This displays correctly i.e. 2000.00 or 150.70 etc

The second value is this:

tblInventoryItem.itemcost + tblUpgrades.ItemCost as totalcostincupgr (both columns are datatype money)

But this displays as 2000 or 150.7

How can I get the second value to show two decimal places even when the digits are zeros?

Thanks!

View Replies !   View Related
Round And Show To 2 Decimal Places?
Hi All,

I have a small question to ask. How to round a numeric field upto 2 decimal places, and also show it with 2 decimal places only

For example the following would return 255.88000000000
select round(255.87908765444,2)

How to get 255.88 only?

Pleas help.

Thanks,

Zee

View Replies !   View Related
Format Field With Decimal Places
Hi all,

 

I need to format a field to a set number of decimal places which is held as a field within the DB, and also format it as a standard number.

 

I have been able to set the format of the field to the decimal places field and it all works (the quantity in the row has the right number of decimal places), but i cant now format it as a number...

 

Heres an example:

 

The quantity is 1500 with 3 decimal places so it becomes 1500.000

 

I need to format it as a number with comma's etc such as 1,500.000

 

How would i go about doing this? Ive already used the format option for the decimal places and dont know how to add another format type (which in this case will be N)

 

Cheers

 

Rob.

View Replies !   View Related
Missing Decimal Places After Cast()-ing
Hello, The issue is to convert a number like 114270 to 114 + (270/320) = 114.84375. The decimal version is the desired result. In other words the last 3 digits of 114270 are in base 320.

Question:  I cast my number as char() then use substring() to get the digits. Then I cast back to float and divide by 320.   I get 0 when I do this and I'm not sure why.

Code follows:
declare @p float
set @p = (select top 1 P from [tablename] where Product = 'Z')

declare @pchar as char(6)
set @pchar = cast(@price as char(6))
declare @first3 as char(3)
declare @second3 as char(3)
set @first3 = cast(substring(@pchar, 1,3) as float(25))
set @second3 = cast(substring(@pchar, 4,6) as float(25))
select @pchar      -- 114270
select @first3       -- 114
select @second3/320    -- 0

I have tried changing cast(substring(@pchar, 4,6) as float(25)) to
cast(substring(@pchar, 4,6) as decimal)
and I get the same result.  Any suggestions or is there a better way altogether?

View Replies !   View Related
How Do I Round And Truncate A Float To 2 Decimal Places?
I have a float of 70.83333333343

If I do this

SET @Output=ROUND(@Output, 2, 1) -- @Output is DECLARED as FLOAT


I get this:
70.82999999999999

I want:
70.83

How do I do that?
Thanks in advance...

View Replies !   View Related
Not Getting An Exact Match Upto Decimal Places...
Hi,

This is strange....

I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).

For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:

Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?

Many TIA.

View Replies !   View Related
Dividing Two Numbers Truncates After 4 Decimal Places
I have two fields - both defined as money.
When I divide them, SQL Server truncates the result after the 4th decimal point.
So SQL Server says: 370.45 / 3,391,517.85 = 0.0001
I want to achieve: 370.45 / 3,391,517.85 = 0.00010922837... etc.
The field the result is going into is defined as decimal(20,18)

I've tried using "cast(1stmoneyfield as decimal(20,18)) / cast(2ndmoneyfield as decimal(20,18)) as dividednumber", but SQL Server reports back errors about null values and Arithmetic overflow and terminates.

I'm at a loss as to how to solve the problem. Any suggestions please?

View Replies !   View Related
Reduce Scale Of Floats To 4 Decimal Places From 16
I am working with an off the shelf app that controls decimal places through the application. All columns underlying the apps 'numeric' fields are floats. We are writing some scripts that need to compare numbers and some of them look like the second row

KELLYJ 2.1233
DONOVM 1.6000000000000001
OLSSON 15.3750

I have tried using round and cast. My examples work like the following:

SELECT 27.719999999

SELECT CAST(ROUND(27.719999999,4) AS DECIMAL(50,4))

When I try the same against a column to update the entire table it leaves the rows like above as longer to the right of the decimal point.

Any pointers on how to get these numbers back to 4 decimals places would be much appreciated

View Replies !   View Related
Stored Procs - Limiting The Decimal Places
I have a stored proc that is inserting an order from a shopping cart into a table - but the problem is that the tax portion of it is causing a problem with CyberCash due to having more than 2 decimal places after decimal point. The part of the stored proc that is causing the problem is:

SELECT @TaxAmt = @TaxTotal * .0775

I know with VBScript I can limit the number of decimal places:

<%=FormatCurrency(sTaxTotal,2)%>

Is the same thing possible in a stored proc?

Thanks for any suggestions.

View Replies !   View Related
Only 2 Decimal Places In Field Of Type Money
I have a table in SQL 2005 with a field that has a value of type 'money'. When values are added, the field has 4 decimal places. Is there a way that I can make it only have 2 decimal places right away? Thanks!!!

View Replies !   View Related
Losing Decimal Places On Sql Svr To Sql Svr Data Flow
Hi,

I have a staging table that has a float [DT_R8] column that shows 4 decimal places.  When I use an OLE DB Source referencing that table to go to an OLE DB Destination referencing a table with an identical column the data gets rounded to a single decimal place. Needless to say this is really messing with the values. 

I can see the Scale property in the Advanced Editor for OLE DB Destination but I cannot change it.  Same for the OLE DB Source.

Oh, and if I do an insert using SQL in Management Studio I have no problem getting the 4 decimal places in. For example:

Insert into table2
(Col1, Col2)
select Col1, Col2 from table1

Moves all the data and keeps the 4 decimal places.

How do I do this without losing those decimals?


Thanks
John

View Replies !   View Related
Format Money Or Float 2 Decimal Places
How do I format the money or float field types to 2 decimal places during a SELECT statement?

View Replies !   View Related
Adding Number Of Decimal Places During Table Design
Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
Neal

View Replies !   View Related
Problem With CAST And CONVERT In SQL Server2000 Converting Decimal Places From 4 To 2
All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted. 
For example: 
Database Price            SELECT statement Price
100.56                           100.5600
I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56.  Has anyone had a similar problem?
Thanks

View Replies !   View Related
Rounding To 2 Decimal Places In Derived Column Transform Editor
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 Replies !   View Related
Round Milliseconds To 2 Decimal Places In 00:02:09:6570000 Format In Sql Reporting Services?
 
     Start                                     End                                      Duration




11/20/2007 12:05:57 PM

   11/20/2007 12:08:06 PM

        00:02:09.6570000

 
Duration =(Fields!end.Value-Fields!startdate.value) i get the output as shown above.
 
I need to round that output to 2 decimal places and display it as    00:02:09.65
 
Any ideas?

View Replies !   View Related
Decimal Places Error On Opening SQLServer Table From Access Project
One single table when openen from a MSAccess project file gives error:

The setting for DecimalPlaces property must be from 0 through 15, 255 for Auto (default).

After which the table is openend normally. I don't recall setting Decimal Places to the table. The only numeric fields are two INTs that are also ID's (PK and FK). This error occurs also when recreating the db project. These fields can not be edited eitther from MSAccess nor Enterprise Manager.

What can be done on the table, resp. MSAccess to change this?

View Replies !   View Related
Decimal Formatting From OLEDB Source To Flat File
I have a
table with column (call it Rate) defined as decimal(18,4). This column
can have any positive decimal value. When the value is less than 1
(e.g. 0.1234) strange things are happening in SSIS.

To set the scenario ...

If I do a select in Management Studio, the value is correctly displayed as 0.1234

If I do the same select from the command line via OSQL, I get .1234

If I do SELECT CONVERT(char(6), Rate) from OSQL, I get 0.1234 (which is what I want)

In SSIS I have an OLE DB Source which defines the SELECT statement to use, and that maps straight to a Flat File Destination.

The
default column type in the flat file connection manager is DT_NUMERIC,
Precision 18, Scale 4. I Set InputColumnWidth to 6 as that's how I need
it in the flat file. The value appears in the file as .1234

I
change the SELECT in the OLEDB Source to apply the CONVERT(char(6),
Rate), and change the column type in the connection manager to DT_STR
length 6. Output is *still* .1234

How can I get the output column in my file to show 0.1234 ??

Greg.

View Replies !   View Related
The Query Just Stops - At Different Places
I have created a stored procedure in SQL Server. I found it very slow, so i putted "select getDate(), 'testposition 1'" at different places, so I could see what part of the code that takes time.

The problem is: Depending on where I put the select statements, the execution of the stored procedure seems to just stop. And depending on where i put the select statements, it stops at different places.

This is how I do (example):
1. I re-create the stored procedure with some "select getDate()"-statements
2. I run the stored procedure 15:00:00
3. I cancel the stored procedure after 20 seconds and look at the resultsets. All getDate-functions show a time between 15:00:00 and 15:00:02
4. I run the stored procedure 15:01:00
5. I cancel the stored procedure after 5 seconds and look at the resultsets. The same amount of resultsets are showed, so I can make the conclusion that the execution stopped at the same place as last time. All getDate-functions show a time between 15:01:00 and 15:01:02 this time too.
6. I re-create the stored procedure with some new "select getDate()"-statements
7. Now the execution stops at an other position. Somtimes even between two "select getDate()"-statements!

I pasted the whole stored procedure here:


drop PROCEDURE spUpdateASW
go

create PROCEDURE spUpdateASW
AS

DECLARE @DataBatchID int
DECLARE @DataHeaderID int
DECLARE @ASWTableID int
DECLARE @ASWTableName varchar(25)
DECLARE @ASWFieldName varchar(25)
DECLARE @AllowASWUpdate tinyint
DECLARE @IsPrimaryKey tinyint
DECLARE @DataTypeIsNumeric tinyint
DECLARE @Data varchar(100)

DECLARE @SQL_Where as varchar(400)
DECLARE @SQL_Insert as varchar(1000)
DECLARE @SQL_InsertValues as varchar(400)
DECLARE @SQL_Update as varchar(1000)
DECLARE @updateCounter int
DECLARE @whereCounter int
DECLARE @SQL_CheckIfAlreadyExist as varchar(1000)

DECLARE @ErrorMessage varchar(500)

DECLARE @RuleWhen as varchar(50)
DECLARE @RuleWhenToExec as varchar(500)
DECLARE @tempStr as varchar(700)

DECLARE @server varchar(50)
DECLARE @shortServer varchar(50)
SET @server = 'GIBSON_A3MFGF_T1.S44E5797.A3MFGFT1'
SET @shortServer = 'GIBSON_A3MFGF_T1'
DECLARE @SQL varchar(5000)

select getdate(), 'testposition 1'


CREATE Table #tmptblUpdateASW(
ASWRowAlreadyExists int,
RuleWhenIsValid int
)
INSERT INTO #tmptblUpdateASW(ASWRowAlreadyExists, RuleWhenIsValid) Values(-1, -1)

DECLARE Batch_Cursor CURSOR LOCAL FOR
SELECT DataBatchID from tblDataBatch
where DateConverted is not null and ASWUpdateStarted = 0
and DataBatchID not IN(
select fkDataBatchID from tblDataHeader where DataHeaderID IN(
select fkDataHeaderID from tblASWData where ConversionErrorMessage is not null
)
)
OPEN Batch_Cursor

FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
WHILE @@FETCH_STATUS = 0
BEGIN
Update tblDataBatch set ASWUpdateStarted = 1 where DataBatchID = @DataBatchID

DECLARE Header_Cursor CURSOR LOCAL FOR
SELECT DataHeaderID
from tblDataHeader
inner join tblAgileFieldType on tblDataHeader.fkAgileFieldTypeID = tblAgileFieldType.AgileFieldTypeID
where fkDataBatchID = @DataBatchID and isSentToASW = 0 order by tblAgileFieldType.InsertOrder
OPEN Header_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ASWTable_Cursor CURSOR LOCAL FOR
SELECT ASWTableID, ASWTableName, RuleWhen
from tblASWTable
inner join tblASWField on tblASWTable.ASWTableID = tblASWField.fkASWTableID
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkDataHeaderID = @DataHeaderID
group by ASWTableID, ASWTableName, RuleWhen, InsertOrder
order by InsertOrder
OPEN ASWTable_Cursor
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
WHILE @@FETCH_STATUS = 0
BEGIN
exec spBuildRuleString @DataHeaderID, @RuleWhen, @RuleWhenToExec output, 0

SET @tempStr = 'IF ' + @RuleWhenToExec + ' UPDATE #tmptblUpdateASW SET RuleWhenIsValid=1 ELSE UPDATE #tmptblUpdateASW SET RuleWhenIsValid=0'
EXEC (@tempStr)
IF (SELECT RuleWhenIsValid FROM #tmptblUpdateASW) = 1
BEGIN

set @ErrorMessage = null
exec spASWDataCheck_hardCoded @DataHeaderID, @ErrorMessage output

SET @SQL_Insert = 'INSERT INTO ' + @server + '.' + @ASWTableName + '('
SET @SQL_InsertValues = 'VALUES('
SET @SQL_Update = 'UPDATE ' + @server + '.' + @ASWTableName + ' set '
SET @updateCounter = 0
SET @SQL_Where = ' WHERE '
SET @whereCounter = 0

DECLARE ASWField_Cursor CURSOR LOCAL FOR
SELECT ASWFieldName, AllowASWUpdate, IsPrimaryKey, DataTypeIsNumeric, Data
from tblASWField
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkASWTableID = @ASWTableID and fkDataHeaderID = @DataHeaderID
OPEN ASWField_Cursor
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
select getdate(), 'testposition 2'
WHILE @@FETCH_STATUS = 0
BEGIN
select getdate(), @ASWFieldName, 'testposition 3'
set @Data = replace(@Data, char(39), char(39) + char(39))
if @DataTypeIsNumeric = 0
set @Data = char(39) + @Data + char(39)

set @SQL_Insert = @SQL_Insert + @ASWFieldName + ', '
set @SQL_InsertValues = @SQL_InsertValues + @Data + ', '
IF @AllowASWUpdate = 1
BEGIN
set @SQL_Update = @SQL_Update + @ASWFieldName + ' = ' + @Data + ', '
set @updateCounter = @updateCounter + 1
END
IF @IsPrimaryKey = 1
BEGIN
set @SQL_Where = @SQL_Where + @ASWFieldName + ' = ' + @Data + ' and '
SET @whereCounter = @whereCounter + 1
END

FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
END
select getdate(), 'testposition 4'
CLOSE ASWField_Cursor
DEALLOCATE ASWField_Cursor

SET @SQL_Where = LEFT(@SQL_Where, LEN(@SQL_Where) - 4)
SET @SQL_Insert = LEFT(@SQL_Insert, LEN(@SQL_Insert) - 1) + ') ' + LEFT(@SQL_InsertValues, LEN(@SQL_InsertValues) - 1) + ')'
SET @SQL_Update = LEFT(@SQL_Update, LEN(@SQL_Update) - 1) + @SQL_Where
SET @SQL_CheckIfAlreadyExist = 'Update #tmptblUpdateASW set ASWRowAlreadyExists = ' +
'(SELECT * from OPENQUERY(' + @shortServer + ','' SELECT count(*) FROM ' + @ASWTableName + ' ' + replace(@SQL_Where,char(39), char(39) + char(39)) + ' ''))'
Exec(@SQL_CheckIfAlreadyExist)
select getdate(), 'testposition 4'
select getdate(), 'testposition 5'
select getdate(), 'testposition 6'

IF @whereCounter = 0
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Inga primary keys var valda för denna tabellen!')
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) > 1
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Kombinationen av primary keys genererade följande where-sats: ' + @SQL_Where)
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 1 and @updateCounter > 0
begin
EXEC(@SQL_Update)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Update, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 0
begin
EXEC(@SQL_Insert)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Insert, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end

END

FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
END
CLOSE ASWTable_Cursor
DEALLOCATE ASWTable_Cursor

FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
END
CLOSE Header_Cursor
DEALLOCATE Header_Cursor

UPDATE tblDataBatch set DateToASW = getDate() where DataBatchID = @DataBatchID

FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
END
CLOSE Batch_Cursor
DEALLOCATE Batch_Cursor


DROP Table #tmptblUpdateASW

GO

View Replies !   View Related
Formatting In Sql Query
Hello All,
Is it possible to format an integer to a float value with 2 decimals in an sql query.
If yes, Please help?

View Replies !   View Related
SQL 6.5 Query Formatting Help
I am trying to run a query for the company that I work for and I am getting the right data but more than needed. the query is like this

select distinct(companyname),max(calldate) from tablename

what I want to see is the latest call made to a customer for each company
but what I get back is the company listed several times with the dates of each call. I only want the latest. Any help?

View Replies !   View Related
SQL Query Formatting
Hi

Im new to sql server what i wanted to do was as follows:

i got a table with application_name and app_version

it looks like as follows

Application_name App_ver
-----------------------------
Oracle 8.1.0
Oracle 8.5
Oracle 9.1
Oracle 10.1
Sql Server 6.5
Sql Server 7
Sql Server 2000
Sql Server 2005

i want to get query the table and produce an out put as follows:

Application_name App_vers
-------------------------------------------
Oracle 8.1.0, 8.5, 9.1, 10.1
Sql Server 6.5, 7, 2000, 2005

please note i need the commas in place
i have written several queries to try and do it but no luck

im using sql server 2000

i will be greatful if any one can help...

View Replies !   View Related
Formatting Results Of The Query
my current query returns data as follows:

stopID reason count

h1 e 5
h1 v 2
h1 a 1
h2 v 2
h2 m 8
h3 t 6
h3 v 1

Is it even possible to sort/format this query in SQL to
return the same data but in this type of format? Using
the values of the 'reason' column as column names?

stopID e v a m t

h1 5 2 1 0 0
h2 0 2 0 8 0
h3 0 1 0 0 6


thanks

-r

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved