Case Statment Need Rounded

Feb 6, 2008

I have this case statement below it is for an access fee. My problem that I am having is that now I am getting a number that is huge. Example, 8.2350000000. I need this to be rounded up and numbers srinked to 8.24, how can I add that in my case statement below? Please help if you can.

Thanks



CASE clm_att1
WHEN 'NG' THEN CASE clm_h30 WHEN 0 THEN cast(clm_sppo as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
ELSE cast(clm_H30 as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
END
WHEN 'NA' THEN '0.00'

WHEN 'AF' THEN CASE clm_h30 WHEN 0 THEN cast(clm_sppo as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
ELSE cast(clm_H30 as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)*.65
END

ELSE Null
END AS AccessFeeFinal,

View 2 Replies


ADVERTISEMENT

Using Case Statment

Apr 7, 2008

Hi There i have a query that uses conditional Select statment my query is:
Select A,B,C case
when '1' then 'one'
when '2' then 'two'
end
from table

what if i want to include addional condition in the case condition like C case
when '1' and A is null then 'C is one and A is null'

Thank you :D

View 3 Replies View Related

Case Statment - Not Sure If I Should Use It Or Not

Oct 30, 2007

I am trying to have all these calulcation int one column under AccessFeeFinal, but it is not working. It was working when I just had the one case statement, but we needed to add another one in because we were getting some blank info in the column. Below are the two statement then I added a third on to show you how I think it should be, but it is not working for me. Please help me out if you can. I was thinking maybe I needed to use an IF Statement.

CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,

CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,


CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,


Thanks

Wendy

View 5 Replies View Related

CASE Statment

Oct 18, 2007

Any ideas why i am getting an error



set dateformat YMD

set datefirst 7

CREATE TABLE #ChildSessions (

siteid integer null

,childid integer null

,sessionid integer null

,sun integer default 0

,mon integer default 0

,tue integer default 0

,wed integer default 0

,thr integer default 0

,fri integer default 0

,sat integer default 0)

declare @firstofweek as datetime

declare @lastofweek as datetime

--select datepart(dw,getdate())

select @firstofweek=cast(floor(cast(dateadd(day,(-1*datepart(dw,getdate())+1),getdate()) as float)) as datetime)

select @lastofweek=dateadd(minute,-1,dateadd(day,7,@firstofweek))

declare @myday integer

set @myday=0

while @myday<7

BEGIN

INSERT INTO #childSessions

SELECT

c.siteid

,c.childid

,sg.sessionid

,case @myday WHEN 1 THEN 1 ELSE 0 end

,case @myday WHEN 2 THEN 1 ELSE 0 end

,case @myday WHEN 3 THEN 1 ELSE 0 end

,case @myday WHEN 4 THEN 1 ELSE 0 end

,case @myday WHEN 5 THEN 1 ELSE 0 end

,case @myday WHEN 6 THEN 1 ELSE 0 end

,case @myday WHEN 7 THEN 1 ELSE 0 end

FROM

child c

,sessionAttendance sa

,session s

,sessiongroup sg

WHERE

c.childID = sa.childid

AND c.siteid = sa.siteid

AND c.active = 1

AND c.potential = 0

AND s.identityid = sa.identityid

AND s.siteid = sa.siteid

AND sg.sessionid = s.sessionID

AND sg.siteid = s.siteid

AND s.dayofweek = @myday

AND @firstofweek <= sa.dateTo

AND @lastofweek >= sa.dateFrom

SET @myday=@myday+1

END

SELECT

c.forename,

c.surname,

sg.sessionname,

CASE (sum(sun)) WHEN 0 THEN ' ' ELSE 'X',

CASE (sum(mon)) WHEN 0 THEN ' ' ELSE 'X',

CASE (sum(tue)) WHEN 0 THEN ' ' ELSE 'X',

CASE (sum(wed)) WHEN 0 THEN ' ' ELSE 'X',

CASE (sum(thr)) WHEN 0 THEN ' ' ELSE 'X',

CASE (sum(fri)) WHEN 0 THEN ' ' ELSE 'X',

CASE (sum(sat)) WHEN 0 THEN ' ' ELSE 'X'

FROM child c,sessiongroup sg,#childsessions cs

WHERE c.childid=cs.childid

AND c.siteid=cs.siteid

AND sg.sessionid=cs.sessionid

AND sg.siteid=cs.siteid

GROUP BY c.forename,c.surname,sg.sessionname

ORDER BY sg.sessionname,c.forename,c.surname

DROP TABLE #childsessions





Msg 102, Level 15, State 1, Line 66

Incorrect syntax near ','.









View 3 Replies View Related

How To Use CASE Statment?

May 13, 2008

HI,

I have a problem with using CASE in my database,
Here is the code that I am using but not working

SELECT Computers.[Computer ID], Computers.Model, Computers.[Dell Service Tag], Users.[First Name] CASE WHEN Users.[first name] is NULL THEN Users.[user ID] ELSE Users.[First Name]+' '+Users.[Last Name] AS NAME ENDFROM Computers INNER JOIN Users ON Computers.[Computer ID]=Users.[Computer ID]ORDER BY Users.[First Name];

Could you please tell me where is wrong?Thanks in advance,Azi

View 1 Replies View Related

Use 'select Case' Statment In Sql Query.

Mar 18, 2005

hi,friend

is it possible to use 'select case' statment in sql query.

give any idea or solution.

thanks in advance.

View 2 Replies View Related

Local Variables In Case Statment

Nov 11, 2004

Hi

I am trying to use a global variable in a case when and am not getting the correct results. If I use static data, it works fine.

Here is a tableless example, which should return Shipper. Any ideas are appreciated.


Code:


declare @shipperGBS varchar(3000)
declare @sQuote char
set @sQuote = char(39)
set @shipperGBS = 'ACI,ADO,ALD,AMS,AWB'
set @shipperGBS = Replace(@shipperGBS, ',', @sQuote + ',' + @sQuote)
set @shipperGBS = @sQuote + @shipperGBS + @sQuote
select case when ('ACI' IN (@shipperGBS)) then 'Shipper' else 'Consignee' end as ClientCharge

View 1 Replies View Related

Can I Set A Variable Equal To A Case Statment?

Feb 25, 2008

I have a case statement I need to refer to several times in my select statement and it's quite long. I would like to just refer to if by name. I tried to create a variable for the Case statement (see below) but I get an error that says, "
Sub query returned more than 1 value. This is not permitted when the sub query follows =, !=, <, <= , >, >= or when the sub query is used as an expression."

USE GP05
GO

DECLARE @EmpID nvarchar (6)

SET @EmpID = (SELECT CASE WHEN NOT(dbo.BE010130.EMPID_I IS NULL) THEN dbo.BE010130.EMPID_I
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND dbo.UPR00600.EMPLOYID IS NULL THEN dbo.UPR00500.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND dbo.UPR00500.EMPLOYID IS NULL AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00600.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00500.EMPLOYID
END FROM dbo.BE010130 FULL OUTER JOIN dbo.UPR00500 ON dbo.BE010130.EMPID_I = dbo.UPR00500.EMPLOYID AND dbo.BE010130.BENEFIT = dbo.UPR00500.DEDUCTON
FULL OUTER JOIN dbo.UPR00600 ON dbo.BE010130.BENEFIT = dbo.UPR00600.BENEFIT AND dbo.BE010130.EMPID_I = dbo.UPR00600.EMPLOYID)

SELECT @EmpID

I can get around this by simply retyping the case statement when I refer to it in the where clause, but I would prefer not to do this. Can someone point a newbie in the right direction? I have 5 case statements in this query and it starts to look pretty ugly when you have to retype them multiple times.

Thanks!

View 9 Replies View Related

Insert And Delete Statment In One Statment

Jan 18, 2008



Hi all

I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?

Thank you in advance .....

Regards,
sms

View 15 Replies View Related

My Decimal Value Is Being Rounded Of When It Gets To The Db

Jan 28, 2004

Hi

I'm inserting a decimal value into my db but it keeps round it of to a single figure. Its the result of time between two times. ie between 1:00 and 2:30 equals 2.5. But it ends up as 2 in the db even though the db field is also decimal. I know by looking at the watch in the bugger that its actually a decimal value just prior to insertion which only leads me to suspect that sql server is doing the rounding of, maybe this is the default behaviour but i need it to store the values as decimal.

View 9 Replies View Related

Decimal Value Is Getting Rounded

Oct 14, 2005

I am writing a simple shopping cart app and the price field is being rounded when inserted into the database.There is a textbox for the user to enter the price.  A stored procedure is used to insert the line item info to the data base table.  The field in the table is formatted as datatype decimal.  Below is the stored procedure and the code that adds the value to the parameter.  Any suggestions on where I am going wrong?<Code>CREATE PROCEDURE [sp_insert_CartDetail] (  @ReferenceNum  [int],  @Item  [varchar](26),  @Desc1  [varchar](27),  @Desc2  [varchar](20),  @Desc3  [varchar](30),  @Desc4  [varchar](30),  @Note  [text],  @Quantity  [int],  @DateOrdered  [datetime],  @SalesPrice  [decimal],  @DistCost  [decimal],  @SalesTaxable  [smallint],  @RequiredDate  [datetime],  @User1  [varchar](12),  @User2  [varchar](12),  @User3  [varchar](12),  @User4  [varchar](12),  @User5  [varchar](12),  @User6  [varchar](12))
AS INSERT INTO [ToolCrib].[dbo].[CartDetail]   (   [ReferenceNum],  [Item],  [Desc1],  [Desc2],  [Desc3],  [Desc4],  [Note],  [Quantity],  [DateOrdered],  [SalesPrice],  [DistCost],  [SalesTaxable],  [RequiredDate],  [User1],  [User2],  [User3],  [User4],  [User5],  [User6])  VALUES  (   @ReferenceNum,  @Item,  @Desc1,  @Desc2,  @Desc3,  @Desc4,  @Note,  @Quantity,  @DateOrdered,  @SalesPrice,  @DistCost,  @SalesTaxable,  @RequiredDate,  @User1,  @User2,  @User3,  @User4,  @User5,  @User6)SqlCmdInsetCartDetail.Parameters("@SalesPrice").Value = CType(txtCost.Text, Decimal)
</code>

View 5 Replies View Related

Decimal Value Rounded To ,00 , Why?

Jun 19, 2007

Hello!



I“m having some trouble with a database field that I had to redefine. At first, it was defined as an int, but I had to change it to decimal (5,2).



I have an application which is accessing my database. Problem now is that when if I enter a decimal value, say 3,12 into my field and then press save on my BindingNavigator, this value is instantly rounded to 3,00. I tried entering the value directly into my database, and that“s no problem. The rounding seems to happen on the update statement. I tried stepping through the code, and after this particular line, the value is rounded... Any help?



Me.OrdersTableAdapter.Update(Me.OrdersDataSet.Orders)



Regards



Daniel

View 3 Replies View Related

Rounded Edge For The Tables

Apr 20, 2007

I am designing a report in which the edges of the tables should be curved. Is there any way to make the edge of the tables rounded? Could any one suggest for this. I am using MS Reporting service 2003

View 2 Replies View Related

SQL 2012 :: SSIS - Values Are Not Getting Rounded Off

Sep 17, 2015

I came across the below scenario in SSIS where the values are not getting rounded off in SSIS as expected.

I am passing the value 23007.945 and using a derived column expression to round off the value Round(number,2).

Placed a data viewer to view the outcome after the derived column expression.Value after derived column expression seems to be 23007.94 but the same when passed in SQL select round(23007.945,2) gives us the value as 23007.95.

What could be causing this?

View 0 Replies View Related

User Function Return Value Being Rounded

Jan 3, 2008



I have created the following user function :-


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER function [dbo].[GetBillPrice](@BillLineID int)

returns float

as

begin

DECLARE @BillPrice float

SET @BillPrice = (

SELECT ((CAST(T_BillValueAndTax.BillValueAndTaxBillValue as float))+(CAST(T_BillTax.BillTaxTax1Value as float)) +(CAST(T_BillTax.BillTaxTax2Value as float)))

FROM T_BillLine

INNER JOIN T_BillValueAndTax on T_BillValueAndTax.BillValueAndTaxID = T_BillLine.BillValueAndTaxID_Price

INNER JOIN T_BillTax on T_BillTax.BillTaxID = T_BillValueAndTax.BillTaxID_Bill

WHERE T_BillLine.BillLineID = @BillLineID)

return ABS(@BillPrice)



end



No matter how I try to return the @BillPrice value, the value is rounded to the nearest whole number. For instance if the value is calculated as 198.75 the value returned is 199.0


When I execute the function in Query Anlayser the correct value of 198.75 is returned.


Any help in solving this would be greatly appreciated.

View 10 Replies View Related

Integration Services :: SSIS - Values Are Not Getting Rounded Off

Sep 17, 2015

I came across the below scenario in SSIS where the values are not getting rounded off in SSIS as expected.

I am passing the value 23007.945 and using a derived column expression to round off the value Round(number,2).

Placed a dataviewer to view the outcome after the derived column expression.Value after derived column expression seems to beĀ 23007.94 but the same when passed in SQL select round(23007.945,2) gives us the value as 23007.95.

What could be causing this?

View 4 Replies View Related

SQL Server 2008 :: Float Value Converting Before Concatenate Get Rounded?

Mar 30, 2015

In My table formula(varchar) and value(Float) column.

Example

formula --> (A+B)/C

I am trying to create same expression with value But my below example showing value gets rounded if it is big.

I am trying to do,

Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456

any way to keep same value as It is?

View 5 Replies View Related

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
 
thanks in advance.

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Help With Sql Statment?

Jul 11, 2006

I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR  (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo

View 6 Replies View Related

SQL Not Statment Help

Jul 11, 2007

I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si! 

View 19 Replies View Related

Sql Statment

Nov 13, 2007

Select @ID  = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4  AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
 

View 5 Replies View Related

Need Help With Sql Statment

Apr 14, 2008

Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named  resolvidos with the field Resol (numeric)
How can i select all the records from table 1 who are not in Tabe2 ?
Thank you
mario

View 2 Replies View Related

IF Statment

May 16, 2002

Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!

View 1 Replies View Related

If Statment

Apr 2, 2007

need help with if statment in a stored procedure
here is what i have and it does not work

Code:


CREATE PROCEDURE Get_ckcompany
@cknum int,
@company varchar

AS

if (@cknum is not null) and (@company is null)
select *
from PMTK_tbl
where Company = @company

else if (@compnay is not null) and (cknum is null)
select *
from PMTK_tbl
where check_Num = @cknum
else
select *
from PMTK_tbl
where Check_Num = @cknum and Company = @company
end if

GO

View 2 Replies View Related

Need Help With An Sql Statment

May 1, 2008

I have a database used for a point of sale system
it has a main table with the total amount of a check - the tip
the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.

The statement i have now is like this

select jc.total + sum(jp.tip) as total from jc innerjoin jp on key
where total <= @max and total >= @min
group by jc.total

but this statement uses total before the tip is added. I am not sure how else to do this any help would be great

View 4 Replies View Related

SQL Statment

Jun 1, 2006

select zsong.song, zsong.trk
from zsong, zfmt
where zfmt.upc='13117' AND zfmt.muzenbr=zsong.muzenbr


That staement works, but if the UPC code is repeated twice it returns the results twice (i want to only read it once)...

how do i make it only return the first result or the last one or whatever (since they all reference the same thing)

View 4 Replies View Related

Use Statment

Jun 9, 2008

Posted - 06/09/2008 : 11:10:47
--------------------------------------------------------------------------------

trying to run the following script.

use 001
select *
from imitmidx_sql

Get incorrect syntax near 001

If I change the use 001 to Data_58 it works fine. Do I need special syntax when the database name is 001??

View 1 Replies View Related

SQL Statment

Feb 18, 2007

Hello All,

I imported a excel file from SSIS and created a table called Lockbox.
To avoid the user from having to change the excel file -it is being imported as is.
I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]

The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID.
The columns are in diffrent order.
And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF'
and convert to a diffrent data type [Site ID] to text.

Is their a sql statment that can do this?

SQL2005

Thanks for your time

View 2 Replies View Related







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