MS SQL Server :: Ceiling Function Problem

Using Ceiling Function In SQL

Jan 10, 2008

Hello, I'm trying to use the ceiling function in SQL to round up numbers to the nearest 0.5 just like MS Excel does it:
ceiling(numeric, significance). but in SQL it only takes one argument. is there a SQL function similar to the Ceiling function in Excel?

thanks for the help.

View 7 Replies View Related

Ceiling Function Problem

Apr 22, 2008

Hello,
I am running the following query. When (sum_of_prov-@Ded) < 0 and > -1, the �ceiling� function will make the amount = 0.
Could you please advice should I just remove the �ceiling� function from the �amount� field or what changes
needs to be done to take the amount because right now Amount is showing zero amount.

DECLARE @Tax int
DECLARE @Ded int

SELECT @Tax =151.8600
SELECT @Ded =0

SELECT round(((ceiling(sign(@Tax-@Ded)/2)*((1+.09)*(@Tax-@Ded)))*1)/24,2) Amount
FROM payhist WHERE empid = '30054' AND payperiod IN (29,30) AND clientid=32 AND payind='U1' AND type IN ('HLT1')

View 5 Replies View Related

Ceiling Function Problem

Apr 23, 2008

Hello,
I am running the following query. When (sum_of_prov-@Ded) < 0 and > -1, the ��ceiling�? function will make the amount = 0.
Could you please advice should I just remove the ��ceiling�? function from the ��amount�? field or what changes
needs to be done to take the amount because right now Amount is showing zero amount from the following query.

DECLARE @Tax int
DECLARE @Ded int

SELECT @Tax =151.8600
SELECT @Ded =0

SELECT round(((ceiling(sign(@Tax-@Ded)/2)*((1+.09)*(@Tax-@Ded)))*1)/24,2) Amount
FROM payhist WHERE empid = '30054' AND payperiod IN (29,30) AND clientid=32 AND payind='U1' AND type IN ('HLT1')

View 1 Replies View Related

Use Of Ceiling Function In SSRS

Apr 3, 2008

The Ceiling Function should ideally return an integer value, but in SSRS it return the value upto 3 decimal places.. any idea how that can be resolved?

Suppose i have a value 90.6 and i use the ceiling function, it should return me 91... but it returns 90.998....

Thanks

View 5 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...

ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )

i hope to have the answer because i need it so much.

Maylo

View 7 Replies View Related

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?

View 4 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

View 3 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Calling A Function From A Function?

Apr 11, 2008

Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)

and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View 2 Replies View Related

Use Getdate Function In A Own Function

Jul 20, 2005

Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View 5 Replies View Related

SQL AVG Function

Apr 30, 2007

I have a table:tblRateUserUserIDJudge intUserIDJudged intscore int
this table contains the userid of the person giving a score (judge) and the person receiving the score (judged) and the score itself.
Each user can only score another user once.
tblRateUser content:judged judge score6 5 87 5 107 6 515 7 415 5 9
When a new score is inserted I want to get the average score for the rated user by counting all scores for that user and divide by the number of records.
declare @avgscore decimal(4,1) set @avgscore=(select avg(score) from tblRateUser WHERE UserCodeJudged=@ID) print 'avg score: '+ cast(@avgscore as nvarchar(15)) in the above example this results for usercode judged 7 in an average score of 7.0 whereas I would expect 7.5 (10+5)/2
what am I doing wrong?

View 1 Replies View Related

Use Function In Asp.net

Aug 19, 2007

I have this scalar -valued function in sql called dbo.GetGoodCustomer ALTER function [dbo].[GetGoodCustomer](@client_id int)returns numeric(10, 2)asbegin declare @getgoodcustomer as numeric(10, 2) declare @review_type as int select @review_type = item_num from customers where client_id = @client_id if @review_type = 0 begin select @getgoodcustomer = getgoodcustomer from customers where client_id = @client_id end if @review_type > 0 begin select @getgoodcustomer = l.g from customers c inner join detail l on c.client_id = l.client_id where c.client_id = @client_Id and c.item_num = l.item_num endReturn @getgoodcustomerendNow, how can I use this function in asp.net (vb.net) ?Because whatever is getgoodcustomer would give me I need to display that in datagrid's text box.Loop thru datagrid If CType(dgItem.FindControl("txtID"), TextBox).Text = 13 ThenDim txtgoodcustomer As TextBox = CType(dgItem.FindControl("txtcust"), TextBox)basically amount returns from function would be showing in txtcust textbox.I don't know I have never done this before..

View 19 Replies View Related

Using SQL MOD Function

May 8, 2008

DECLARE @TotalAmount AS INTEGER DECLARE @TotalPoints As INTEGER SELECT @TotalAmount = SUM(Amount) From Rewards WHERE MemberID = @MemberID AND Redeemed = 1 @TotalPoints = @TotalAmount MOD 20

I have the code above but I keep getting an error message around the last line that I am not sure what it means. Can anyone please help?? Thanks in Advanc3e

View 9 Replies View Related

Last Function

Jan 5, 2004

SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server????

tblClients - ClientID (PK)
tblPayments - PaymentID (PK)

Thanks for any help,

View 2 Replies View Related

Function

Jul 14, 2005

Can a UDF return a table query as result? Please kindly provide sample T-SQL. Thanks.

View 6 Replies View Related

DTS Function

Sep 12, 2002

Hi

I'm creating DTS packages to load data from text files. I'm finding that a lot of the data needs to be transformed in the same way (e.g trailing "." removed). Rather than writing individual activex scripts to do this, is it possible to write my own DTS function which I call from my DTS packages?

Thanks

Andy

View 5 Replies View Related

VBA Function In SQL ??

Jun 9, 2004

Hi,

I am upgrading an AccessXP database to SQL 2000 and have an issue with week numbers.

There is currently a piece of VBA that calculates the week number for time recording purposes using the DatePart function as
DefaultVal_WeekNo = DatePart("ww", TaxDate, vbMonday, vbFirstFourDays)

I have tried to get this replicated in SQL by using

declare @TaxDate as datetime
declare @TheWeek float

set @TaxDate = @TheDate -90 -7

set @TheWeek = cast(datepart("ww",@TheDate) as numeric (30))
-
cast(datediff("wk", @TaxDate, @TheDate) as numeric (30))

but this is not reurning the correct values, i belive this is because the VB function has the vbMonday argument.
Can anyone tell me how i can get wround this please?

TIA

View 2 Replies View Related

MTD Function

Mar 11, 2002

Is there a SQL MTD function ! Let's I want to delete MTD rows. Need something
like:

delete from abc where date_entered = mtd

Suggestions are appreciated.

View 1 Replies View Related

Len() Function

May 28, 1999

We have len() function in some of our stored procedures. Why one of our SQL servers (6.5) will not recognize this function while others do? While we transfer the stored procedures, the stored procedure can not be transfered to the database of that server.
Your immediate help will be appreciated.
Su

View 5 Replies View Related

Max Function

Jul 18, 2002

Hi,

I am having some difficulty with the Max function. I would expect it to return one row when you use it in a select statement (as long as there is truly one unique row)

Executing the query below give me the following result

select * from lead_with
where id= 80430
ORDER BY 1 DESC

id firm covacc avgFinal
----------- ----------- ----------- ----------------------------------------
80430 122 0 8.000000
80430 122 29353 9.000000
80430 505 NULL 8.500000
80430 2045 NULL 7.500000
80430 122 18815 9.000000
80430 223 NULL 8.500000
80430 625 NULL 8.000000

Executing the next query give me the following results

select MAX(avgFinal), IBFirm
from lead_with
where [ID] = 80430
group by avgFinal,IBFirm
order by avgFinal desc

IBFirm
---------------------------------------- -----------
9.000000 122
8.500000 223
8.500000 505
8.000000 122
8.000000 625
7.500000 2045

Not only does it return more than one row, it actually misses a row from
above

80430 122 18815 9.000000

I guess I am missing something. What I am trying to do is get the row with
the maximum value in the avgFinal column.

Any help is appreciated.

Thanks,

James

View 2 Replies View Related

Function

Mar 22, 2006

I have a function dbo.Validlan
which is takes 4 paramentes:
par1,par2,par3,par4
I would like to select all 4 at run time:
SELECT par1,par2,par3,par4
FROM table1 t1
INNER JOIN table2 t2
ON t1.enrl=t2.enrl

View 1 Replies View Related

Help With MAX Function ?

Dec 3, 2004

I have the following problem statement: Quote: What was the most expensive merchandise item sold in July? Display sale date, sale price and merchandise description. Sort output by descending sales price. (hard-coding date is OK). Here's my query statement:
Code:

SELECT saleDate AS "Sale Date", salePrice AS "Sale Price",description AS "Merchandise Description"FROM Sale, SaleItem, MerchandiseWHERE salePriceAND DATENAME(MONTH, dateBorn) = 'July'ORDER BY salePrice DESC

I want to know how I would use the MAX function on my query since I know this would give me the most expensive item. Am I writing my query statement correctly? Do I have to declare the MAX function in the SELECT statement and on the WHERE statement of just on the WHERE statement? Any clarifications/help would be appreciated

View 1 Replies View Related

How To Use IN Function..

Aug 26, 2005

Hi..

I need to use IN function in a sql prodecure..

my procedure is below

Code:

CREATE PROCEDURE [dbo].[Cat]
@gets nvarchar(200)
AS
begin
select * tbl where id in (@gets) order by id desc
end
GO

the type of id field is identity number
also I tried
@gets int

gets is called from my asp page.

in asp page, I use prodecure like below

Code:

ids="11,12,13"
Rs.Open "Cat '"&ids&"'", db,1,3

it doesn't work!

I get the following error

Code:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '11,12,13' to a column of data type int.

View 3 Replies View Related

Like Function

Apr 18, 2008

Hello

Is there a way to do something like this?

select column1 from tablename where
column1 not like '%(select col1 from table2)%'

i am trying to save my time with not writing too many not like condition

Help Appreciate

View 14 Replies View Related

Max Function

Feb 8, 2007

Hi Guys

How can i change data_type of a table from varchar to timestamp.
I have also lot of data in that table. When i create a the table i just put vchar for that column and now i have to perform max function on that date column.
for example
table_1
ID serial
employee_id
date vchar
name vchar

query
select max(date) from table_1 group by employee_id

I don't get the max date.

Any help
Thanks