SQL AVG Function
I have a table:
tblRateUser
UserIDJudge int
UserIDJudged int
score 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 score
6 5 8
7 5 10
7 6 5
15 7 4
15 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 Complete Forum Thread with Replies
Related Forum Messages:
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
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 Replies !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
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 Replies !
Function To Call Function By Name Given As Parameter
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 Replies !
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
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 Replies !
Use Getdate Function In A Own Function
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 Replies !
Calling A Function From A Function?
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 Replies !
Using SQL MOD Function
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 Replies !
Use Function In Asp.net
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 Replies !
Last Function
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 Replies !
SQL Function
hello everyone,I created a function in MSSQL that builds an html coupon based upon thecoupon ident (more complex than that, but that's not where i'm havingproblems!)When the function builds the coupon, it returns the stream asVarChar(8000)I am calling this function in a sproc that passes in the coupon ident.But when I execute this sproc, I get the following error:Syntax error converting the varchar value '<html>......"What am I doing wrong? Are there problems with the datatypes I"musing?Thanks in advance for any help on this?
View Replies !
Need A Function
I'm looking for a string function that is similar to the INSTRfunction in VB. I haven't seen anything in the help files that I canuse. Does anyone have any suggestions?Here's what I'm trying to do:There is a field in a table that will look something like this -"XXXXXX - YY".I want to separate it on the dash and get two strings out of it -"XXXXXX" and "YY". I'm trying to keep it all in a stored procedureand avoid a vb script or exe.I'm envisioning something like this:declare @CDT datetimeselect @CDT = createdatetime from imOrderHdrwhere VendorCode = 'SYG' and createdatetime is not nulland status in (1,2,3)select d.VendorStockNumber, substring(i.ItemDescription, 1,instr(iItemDescription, '-') - 1),substring(i.ItemDescription, instr(iItemDescription, '-') + 1),d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) asExtensionfrom imOrderDetail djoin imItem i on i.ItemCode = d.ItemCodewhere d.CreateDateTime = @CDTI'd write my own function, but the computers this will be run on haveSQL 7.Any suggestions will be appreciated.Thanks!Jennifer
View Replies !
Function
Good Morning,I have a question.Is there a function like ISNULL but for the space value(example ISSPACE)?THANKS
View Replies !
SP Vs FUNCTION
Hi, When we talk about the difference between SP and Function, it is said that Function returns a value where as SP doesn't. My Question is, Then what does the "OUTPUT" parameter Stands for in SP? Kindly make my doubts clear on this. Thanks In Adv. Rahul Jha
View Replies !
Last Function
Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature? Example: Item_Code Time 123456 11:40 123456 11:41 123456 11:42 By grouping on item code and using the last function on the Time column, the third record would be returned. Item_Code Time 123456 11:42 All, help is appreciated.
View Replies !
Using A Function
I have found this very practicle function CREATE FUNCTION FN_TRANSLATE (@VALIN VARCHAR (8000), @FROM VARCHAR(256), @TO VARCHAR(256)) RETURNS VARCHAR (8000) AS BEGIN -- effets de bord IF @VALIN IS NULL RETURN NULL IF @FROM IS NULL OR @TO IS NULL RETURN NULL IF LEN(@VALIN) = 0 RETURN @VALIN -- initialisation DECLARE @I INTEGER DECLARE @OUT VARCHAR(8000) SET @OUT = '' -- lecture caractère par caractère SET @I =1 WHILE @I <= LEN(@VALIN) BEGIN IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0 BEGIN IF LEN(@TO) >= PATINDEX('%' + SUBSTRING(@VALIN, @I, 1) + '%', @FROM) SET @OUT = @OUT + SUBSTRING(@TO, PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1) END ELSE SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1) SET @I = @I + 1 END RETURN @OUT END but how can I use it in MS SQL 2000-25000 ? FN_TRANSLATE(dbo.Users.Name, 'àâäçéèêëîïôöùûüÿ', 'aaaceeeeiioouuuy') doesnt work I get an error on the colum name dbo.Users.Name thank you
View Replies !
SUM Function
I have this query: SELECT SUM(st_Count) FROM Queue WHERE st_ID = 1483 It works, but how can I change it, to see SUM for many ID's, which I have in list? I try this: SELECT SUM(st_Count) FROM Queue WHERE st_ID = List.tw_ID But there is error: The multi-part identifier "List.tw_ID" could not be bound. How to display SUM for meny ID's?
View Replies !
Len() Function
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 Replies !
MTD Function
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 Replies !
Max Function
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 Replies !
DTS Function
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 Replies !
VBA Function In SQL ??
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 Replies !
Function
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 Replies !
Help With MAX Function ?
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 Replies !
How To Use IN Function..
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 Replies !
Like Function
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 Replies !
Max Function
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
View Replies !
Use MAX Function ???
I have to use Select MAX(COL) FROM TABLE I have to increment ID column value each time by 1 so that each row has a unique identity. Please let me know how do we do that. using above statement Thanks
View Replies !
Sum Function
I have a cube which as a time dimension by year (2001 through 2007). I have set up a report that allows user to select which years to see...However, I also have some additional calculations that require that I sum the number of accounts over the time period that they selected. Sum([Time Dimension].[Close Year].[All],[Measures].[Number of Accounts])...If the user selected years 2003 through 2006, I was expecting that this formula would give me only the sum of those accounts for those specific years, however it gives me the sum of accounts for all years in the cube (2001-2007) even though 2001,2002 and 2007 were not selected. I assume it is because of the [All], but is there a function that I could use to only look at what was selected in the parameter.
View Replies !
SUM Function
I'm trying to sum a string column that looks like this hhhh:mm 152:20 103:15 - 101:45 Trouble is there are "-" in the result set being returned Anyone have ideas how i could do this? The result has to be in the same format Thanks Dave
View Replies !
Top N Function
Hi, I am using Reporting Services to display the Top N with N set by the user through a Parameter. It works fine to the extent when the values are not the same so will take top 10 but when there are several values the same say for instance Row Id Value 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 9 11 9 12 9 At present If i wanted the Top 10 it would also include all the 9's rather than just the 2 Nine's that would give me top 10 values. Is there any way in Reporting Services in which this can be done? Many Thanks
View Replies !
Function?
Hey all.... It is possible to get the ID on this way? or how can i do that? Code SnippetSELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0') The reason is: "c34f5321_95d9_41fc_b98a_192eda1ea0d3" but now my problem....how do i write the ID behind a command? like this: dtEvent_c34f5321_95d9_41fc_b98a_192eda1ea0d3 my query: Code Snippet SELECT dtEvent.EventNo as "EventID:", dtPrincipal.struser as "User:", FROM dtEvent_HereShouldStayTheId AS dtEvent, dtMachine, dtPrincipal_HereShouldStayTheIdToo AS dtPrincipal WHERE dtMachine.id = dtEvent.agentmachineid AND dtPrincipal.id = dtEvent.primaryuserid AND dtEvent.eventno IN ('529') I think it is possible with a function but i don't know how can i realize it... Mhh thats also a half solution...: Code Snippetdeclare @table1 nvarchar(max); declare @table2 nvarchar(max); Set @table1 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0')) Set @table2 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0')) SELECT dtEvent.EventNo as "Event ID:", dtEvent.creationtime as "Erstellt am:", dtPrincipal.struser AS "User Account:", dtMachine.description AS "Computer:", FROM (dtEvent_+@table1) AS dtEvent, dtMachine, (dtprincipal_+@table2) AS dtPrincipal, WHERE dtMachine.id = dtEvent.eventmachineid AND dtPrincipal.id = dtEvent.primaryuserid AND dtEvent.eventno IN ('528') Thanks!
View Replies !
Function Help
I have the following Function The Location Table contains a Zipcode, Latitude, Longitude CREATE FUNCTION ZipcodeProximitySQR(@zipcode varchar(5), @miles int) RETURNS @ZipcodesInRange TABLE (Zipcode varchar(5) primary key) AS BEGIN DECLARE @latitude as float DECLARE @longitude as float DECLARE @latitudeMile as float DECLARE @latitudeDegree as float SELECT @latitudeMile = 69.1; SELECT @latitudeDegree = 57.2504; /*@earthRadius / @latitudeMile*/ SELECT @latitude = (SELECT latitude FROM Location WHERE Zipcode = @zipcode) SELECT @longitude = (SELECT longitude FROM Location WHERE Zipcode = @zipcode) INSERT INTO @ZipcodesInRange (Zipcode) (SELECT Zipcode INTO @ZipcodesInRange FROM Location WHERE (SQRT((@latitudeMile*(Latitude-@latitude))*(@latitudeMile*([Latitude]-@latitude))+(@latitudeMile*([Longitude]-@longitude)*COS([Latitude]/@latitudeDegree))*(@latitudeMile*([Longitude]-@longitude)*COS(@latitude/@latitudeDegree)))) <= @miles) RETURN END GO When I try to create it, I get an error with my insert into statement. I tried changing it to a SELECT INTO but still no luck. Any help will be greatly appreciated.
View Replies !
BCP And Function
Hi, I have 2 questions: 1. Is it possible that I can filter out the records and only load those that I wanted during BCP loading? I am using SQL Server 2005 and doing the bulk copying of the data from a .dat file into a SQL table. The BCP process is working fine, but one of the column in this .dat file indicate the type of data I am getting. One is 'T' for text, the other is 'N' for numeric. So, is it possible that I can import the data of these 2 types separately, meaning I want to load the 'N' type first, then 'T' after, but it does not have to go in that order. Whichever type goes first is fine, but can I filter out like we could in SQL with the where clause? 2. Is it possible to have the value returns from a function as a list of items, instead of an individual item. for example, in query, i can say I wanted a list of cities ....where city in(select city from sometable), but i know function only allows me to return one value (New York) for example, not the whole list of cities within the US. so, is this possible with a function or I can only do this with stored procedure? i have both scalar and in-line/variable table function, but none of them give me what I need. the scalar only returns 1 value, and the table function I would still only can put in specific parameter for it to return a list. For instance, from using my table function, I have "select * from ::fn_test(ID, city)', this mean I still can only put in one id and one city at a time with all the columns that I specified, not a list of ID and cities. do I make any sense? please help/advise if you can, appreciated much.
View Replies !
Need To Use MID Function In SQL
When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following? CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END. I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash. Thanks. Ernie
View Replies !
Function ?
Hi how to take previous month endate Declare @cEnd_Date datetime Declare @pEnd_Date datetime set @cEnd_Date='30 jun 2008' set @pEnd_Date=dateadd(m,-1,@cEnd_Date) output should be '31 may 2008'
View Replies !
LEN Function
Sorry people but I am really new to SQL server, but here goes. I have the folowing database using the table MasterCalendar. I would like to drop the last 3 caracters off the cohort field and insert into a new field. When I use the following code I can make the LEN() work and also make the SUBSTRING() work but when I try to combine into 1 function I get an error message. This works as expected USE FacultyServices_1 Select Cohort, LEN(Cohort)-3 AS L_Cohort, //this returns a number depending on length SUBSTRING(Cohort,1,5)AS SUB_cohort //this returns the 1st 5 characters of the cohort From MasterCalendar Expected results <edit by tkizer>removed image since it's asking us for login information</edit> This doesn't work! USE FacultyServices_1 Select Cohort, LEN(Cohort)-3 AS L_Cohort, SUBSTRING(Cohort,1,LEN(Cohort)-3)AS SUB_cohort From MasterCalendar Notice I substituted the LEN(cohort)- 3 function into the SUBSTRING(Cohort,1,5) AS SUB_cohort changing the the number 5 to LEN(Cohort)-3 This kind of logic works well in Excel or Access but I get this error in SQL 2005 Msg 536, Level 16, State 5, Line 2 Invalid length parameter passed to the SUBSTRING function. All I really want to do is drop the last 3 characters off a variable length field returning the remaing characters. Thanks
View Replies !
Function
i have to Create function to return comma separated value. on a author_info table which having a column name (AuthorName) and return the comma separated author name. please help me spatle
View Replies !
Function
what is the error near as ----> create function listname() AS BEGIN Declare @authorname varchar(1000) SET @authorname= '' SELECT @authorname = @authorname + AuthorName + ',' FROM Author END can we cant create a function which don't return any value. spatle
View Replies !
Need Help With Max Function
I am trying to create a report that is using 2 tables. The first table is called equipdaily. The 2 fields I am using are Equipdaily.kequipnumber, the second one is equipdaily.equipstatus. I then need to join into another table called Equipdet. The field I link with the Pk is (kequipnumber). The Field I grabbed from that table was equipdet.kordernumber. The problem I am having is we have rented out these equip to other people and its pulling all data. I want the most recent one. So I am assuming I need to use the max code. Here is what I have so far and it works I just dont know how to add the Max to either equipnum or if it should be added to ordernum. please help me out. SELECT DISTINCT equipdet.kequipnum, equipdet.kordnum, equipdaily.eqpstatus FROM equipdaily INNER JOIN equipdet ON equipdaily.kequipnum = equipdet.kequipnum WHERE (equipdaily.eqpstatus = 'ON')
View Replies !
SQL Function Help Please
Hiya chaps I have a table called movements which tracks the issuing of safety wear etc. movement_id int (identity yes) emp_no char(8) clothing_id int (fk) create_dte smalldatetime create_by_emp_no char(8) clothing_price decimal I need to create a function that will check first that a user has or has not received an item (param clothing_id) if they have not then insert a new entry. else If they have had an item that matches the clothing_id param - check if it is within one year (if it is then do not insert) if the user has had this item and it was longer than a year insert the item. Ideally I would like the function to return a 1 if a new record was inserted (for the first time) a 2 if a record was inserted(user has had this item longer than a year ago) a 3 if user has this item within one year (no record inserted) Dont know if this is feasable as I am new to transact sql. Thanks
View Replies !
Using Str Function
Hi, I have an item number as column on my table, this column is a char(10) size. Its look like this. Item_no ------- 010010001 010010002 010010003 010020001 010020002 020020001 The description of the field as follows:- xx is the catcode xxx is a subcode xxxxx is the serial number How to search the table look for item number with subcode of '002', normally we are using before str() function. Best Regards
View Replies !
What Function Should I Use?
I need a query that returns a string for the following: If field voucher has less than 3 characters then account field + date field else voucher. I was thinking I should use a Case When statement, however, I'm not sure which function to use to determine the voucher field having less than 3 characters.
View Replies !
|
TRACKER
