Know Of Any SQL Function That Would Chop-off The Hyphen From The Zipcode??

Dec 14, 2006

Anyone know what's the MS-SQL function that look at the zipcode like "30296-6912" and chop off the "-6912" suffix by just looking at the hyphen part?

Thanks...

View 6 Replies


ADVERTISEMENT

Trying To Chop Up My Data -- Not Working The Way I Want It Too.

Mar 21, 2005

Hello everyone again.

I'm trying to manipulate one of my collumns. I have one collumn called fundID (where a 7 diget number is stored). These numbers are sequestial (not identifers w/n the SQL database) but they have meaning in the order they are in....for instance:

1100000 is a higher level then
1110000 and this is a higher level then
1111000 (which is the lowest level)

however since they are all part of the 11 group they all belong w/n the same group.

What I've done is created two more collumns and I want to deceminate the field where these id's are kept.

So if my key is 1110000 I want to list in the centerKey collumn that the Center Key is 1100000 (becasue it is the one higher then the 1110000 key)....This is all for reporting purposes that i'm doing in .net and has been the easist way to relate all of these records together.

If someone could lead me in the right direction on how I could write a stored proc that would break these down properly that would be great...Im trying to base it on the Substring right now but that does not seem to be working....if there are any more ideas I appricate it.

Thanks in advance everyone as always.

RB

View 1 Replies View Related

Missing 00's In My Zipcode Table

Mar 7, 2008



I have a zipcode table that is missing the leading 0's.

Currently Need
501 00501
1001 01001

How would I go about adding the missing 0's?

View 3 Replies View Related

Need Help With GROUP BY And COUNT On 9 Digit Zipcode

Sep 3, 2007

I have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.

This is what I have

Code:

SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;


which does what I want it to do except it uses the whole 9 digits.

Any help would be appreciated

View 4 Replies View Related

Recommendations For Placement Of ZIPCode Table

Apr 30, 2007

We are creating a company-wide table of ZipCodes, States, GPS info, etc. This table can be used by our development and production servers (many of them.) We could place the table on a given server and use linked servers to grant access to that table to the other servers. But is there a better way to handle this globally-useful table?



Barkingdog



P.S. Clearly, we don't want to have multiple copies of this table scattered around on various servers. That introduces synchronization issues.

View 1 Replies View Related

IDENTITY Problems Updating ZipCode Table

Aug 16, 2004

I am having problems updating my zip code table that contains zip, city, state, long, lat, ect..

I have the latest CSV file, I tried to use the import wizard in SQL Server 2000 Enterprise Manager.
I set the ID field as <ignore> and specified the appropriate columns for the rest of the data matching from CSV to already designed and working zip code table. Also I checked the box that said "Delete Rows in Destination Table" as well as "Enable Identity Insert" was checked

I ran the wizard, and now I have empty table and it will not insert any records because the error said that the identity column can not accept NULL.

What do I do? I am not updating the identify column so Is it telling me it can't insert NULL into ID?

Any suggestions...

Thanks,
Lito

View 3 Replies View Related

Selecting Nearby Distinct City/zipcode Issue

Mar 12, 2004

I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:

Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco

---------------- etc----------------

I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:

ZIP | City name
111 belmont
114 San Francisco


How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.

The select statement is below:




CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int


AS


SELECT distinct o.City AS City, o.zip_code, o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist

FROM zipcodes z,
zipcodes o,
zipcodes a




WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
GO





Thanks,

Christian

View 7 Replies View Related

Stored Procedure To Retrieve Zipcodes Within A Specified Zipcode And Distance

Apr 28, 2008

Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.

Thanks in advance

Ade

View 5 Replies View Related

SQL &#39;Hyphen&#39; Problem...

Aug 24, 2001

I am new to SQL.

But here is my question. I have a column 'name' and contents are like
A-B, C-D, E-F

When I query DB using, SQL

select * from tbl_Names
where name='C-D'

it does not display anything...
What am I doing wrong?

View 1 Replies View Related

SQLServer Name With Hyphen

Dec 30, 1999

Does anybody knows a workaround to register SQLServer with the name which contains hyphen('-') character in it? I work for multi-national company & they have naming standard for NT servers & this server has hyphen in it.
Also site server guru here thinks that server name change option is not feasible. Please help.

View 1 Replies View Related

Using A Hyphen In SQL Server Name

Oct 14, 2003

We are about to name a new SQL Server and was wondering if anybody has any documentation/knowledge that states one should avaoid using hyphens when naming Servers, objects, etc in SQL.

I have always heard its bad practise, but have not found any solid documentation. For example, I know that the 'connect to' statement does not like hyphens, but what about a SQL Server name on the network ?

Thanks

View 2 Replies View Related

Creating A Job With A Database That Has Hyphen

Jun 22, 2004

Hi,

1- I have given a dbo permission on a database named F12345-1, now I want to create a job on that server using this database.

2- I tried to create the first step in the new job but I don’t see my database name on the databases drop down menu (I only see the system databases)

3- I tried being under Master and using the USE sql command (Ex: Use F12345-1) at the beginning of my step but I get a message saying that the database F12345 doesn’t exist (The message doesn’t say the whole database name only the part before the hyphen)

4- I tried putting quotes around the database name with no luck, although that worked in SQL Query Analyzer but the Job didn’t like it.

Can you please help with this, knowing that I have no control on the database name (In order to remove the hyphen from the name which I am assuming is the cause of this problem)

Thanks

View 3 Replies View Related

Order By Ignoring The Hyphen Character

Sep 24, 2007

Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.

Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part


Here is the beginning of the Transactions table create script


CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,


The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).

Here is an example of what I get.

130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683


I was expecting this ( and I get this in and older database ).

068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072

View 3 Replies View Related

Inserting A Hyphen (-) Into The 3rd Position Of A Nvarchar Field

Sep 13, 2004

I have a colum of numbers, which datatype is nvarchar;

157853
4389
1999999999556
4568987

I need to insert a hyphen, 3 spaces from the right (to left) My numbers would look like this:

157-853
4-389
1999999999-556
4568-987

...do I use the rtrim function ?...what would the syntax look like ?

Thank you

View 3 Replies View Related

Replication Fails When Table Name Has Single Hyphen

Dec 7, 2006

Hey,

I have SQL Server 2000 DB that was converted via DTS from Acccess 97. Replication fails for any table with a single hypen in the table name. If I copy one of these tables to a name using an underscore rather than a hyphen, then it replicates successfully.

Below are errror messags from agent with -Outputverboselevel set to 2. I have also seen 'Agent error code 20082', in which case the replication command is incomplete.

Any ideas?

Thanks,

Bill Coulter



[12/7/2006 1:22:40 PM]172.23.150.51.engdata: {CALL sp_MSupd_br1-16 (NULL,?,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,?,0x0200020000)}
Parameterized values for above command(s): {{N'00000000001010000100', N'000000000000000000', N'730535L1205C'}}
Agent message code 20046. Syntax error or access violation
[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_distribution_history(57, 6, ?, ?, 27, 27, 2.32, 0x01, 1, ?, 1, 0x00, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 18,
Transaction Seqno = 0000108b000028f2000400000000, Command ID = 1
Message: Replication-Replication Distribution Subsystem: agent OUSBID02-engdata-172.23.150.51-57 failed. Syntax error or access violation[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_repl_alert(3, 57, 18, 14151, ?, 1, N'OUSBID02', N'engdata', N'172.23.150.51', N'engdata', ?)}
ErrorId = 18, SourceTypeId = 1
ErrorCode = ''
ErrorText = '{CALL sp_MSupd_br1-16 (NULL,N'00000000001010000100',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'000000000000000000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'730535L1205C',0x0200020000)} '
[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_repl_error(18, 0, 1, ?, N'', ?)}

Category:COMMAND
Source: Failed Command
Number:
Message: {CALL sp_MSupd_br1-16 (NULL,N'00000000001010000100',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'000000000000000000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N'730535L1205C',0x0200020000)}
ErrorId = 18, SourceTypeId = 4
ErrorCode = '37000'
ErrorText = 'Syntax error or access violation'
[12/7/2006 1:22:40 PM]OUSBID02.distribution: {call sp_MSadd_repl_error(18, 0, 4, ?, N'37000', ?)}

Category:ODBC
Source: xxx.xx.xx.xx
Number: 37000
Message: Syntax error or access violation

View 8 Replies View Related

SQL Server 2012 :: Hyphen Delimited String Manipulation

Oct 19, 2015

I am having a field 'Flight Route that holds hyphen delimited character sequences.

E.g. : ABC-BCD-DEF-EFG.

My requirement is like this:

If the flight route is:

ABC-BCD-BCD-DEF make it ABC-BCD-DEF
ABC-ABC-BCD-DEF make it ABC-BCD-DEF

i.e. 'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.

View 6 Replies View Related

Problem With Hyphen In Full Text Catalog Search

Jul 23, 2005

Hi All,we have a problem with the Full Text Catalog Search.We use the following SQL Statement for matching companies from a table:select company, lastname, firstname, pkcustomers, fkcustomers,location, title, fkFunktionen, TypeOfPositionfrom customerswhere (fkcustomers = 0 or fkcustomers is null)and active = 1and pkCustomers in (select [KEY] from CONTAINSTABLE(Customers, Company,'"*SEARCHTERMS*"'))order by company ascThe search so far is working perfect.Now the problem: There are two companies in the table called "i-fabrik"and "b-wise". There´s no way to find these two companies. I find outthat the search is successful if there are more than three letters infront of the hyphen (for exampe iii-fabrik or bbb-wise). How can thatbe? Why exactly 3 letters?I hope somebody can help me.Best regardsMarkus Weber

View 1 Replies View Related

Data Warehousing :: Query To Extract All Text After Hyphen From String Containing Comma Separated Text

Aug 27, 2015

I have a parameter value as shown below and this is dynamic and can grow 

Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA   --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL   --- each state to be followed with comma and the next state

correct query that will fetch  only state names and not the numbers.

View 8 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







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