Sum Function Yielding Bad Results
Feb 20, 2004
I've inherited the following code:
SELECT TOP 100 PERCENT dbo._Options_Demographics.GradeLevel, dbo._Options_Demographics.Gender, dbo._Options_Demographics.Ethnicity,
dbo._Options_Demographics.Age, SUM(dbo._Options_Demographics.Enrollment) AS Enrollment, dbo._Options_Demographics.OptionsYear,
dbo._Options_Demographics.OptionsMonth
FROM dbo._Options_Confirmed INNER JOIN
dbo._Options_Demographics ON dbo._Options_Demographics.DistrictCode = dbo._Options_Confirmed.DistrictCode
GROUP BY dbo._Options_Demographics.GradeLevel, dbo._Options_Demographics.Gender, dbo._Options_Demographics.Ethnicity,
dbo._Options_Demographics.Age, dbo._Options_Demographics.OptionsYear, dbo._Options_Demographics.OptionsMonth
ORDER BY dbo._Options_Demographics.OptionsYear, dbo._Options_Demographics.OptionsMonth, dbo._Options_Demographics.GradeLevel,
dbo._Options_Demographics.Gender, dbo._Options_Demographics.Ethnicity, dbo._Options_Demographics.Age
which returns this data (the enrollment value is 4 times the correct value)
grade gender eth ageenrollyearmonth
8FemaleBlack1618112003December
8FemaleBlack1716212003December
8FemaleBlack187932003December
8FemaleBlack18P2872003December
If I remove the sum function and manually add, my totals are correct ...
SELECT TOP 100 PERCENT _Options_Demographics.GradeLevel, _Options_Demographics.Gender, _Options_Demographics.Ethnicity,
_Options_Demographics.Age, _Options_Demographics.Enrollment, _Options_Demographics.OptionsYear,
_Options_Demographics.OptionsMonth
FROM _Options_Confirmed RIGHT OUTER JOIN
_Options_Demographics ON _Options_Demographics.DistrictCode = _Options_Confirmed.DistrictCode
GROUP BY _Options_Demographics.GradeLevel, _Options_Demographics.Gender, _Options_Demographics.Ethnicity, _Options_Demographics.Age,
_Options_Demographics.Enrollment, _Options_Demographics.OptionsYear, _Options_Demographics.OptionsMonth
ORDER BY _Options_Demographics.OptionsYear, _Options_Demographics.OptionsMonth, _Options_Demographics.GradeLevel,
_Options_Demographics.Gender, _Options_Demographics.Ethnicity, _Options_Demographics.Age
I've been looking at this for too long and I know that is something very trivial ...
William
View 2 Replies
ADVERTISEMENT
Feb 8, 2006
All,
I'm getting the following error in my SQL logs:
Process 51:16 (2bc) UMS Context 0x1249F118 appears to be non-yielding on Scheduler 0.
I killed the process, but it's been rolling back for 3 hours. It's done zero IO and consumed a TON of CPU time.
Any suggestions as to what I can do?
Thanks,
Isaac
View 1 Replies
View Related
Jul 11, 2007
our sql 05 server running on 64 bit slowed down and became non-responsive after getting this initial message Non-yielding Scheduler and then flooding the system with this message and become non-responsive.
Process 62:0:0 (0x11a0) Worker 0x00000001371DE1C0 appears to be non-yielding on Scheduler 0. Thread creation time: 12828422717058. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 96%. Interval: 70050 ms.
i restarted the server as , i could not stop the services running , as the view processess window in sql server had become non responsive and i could not kill or even view the processes ...
no idea what caused it or how this can be stopped from happening again,
please advise if you have encountered this problem
thanks
View 1 Replies
View Related
May 10, 2006
Could anyone tell me why the following code produces a result of 2.5 instead of 2 ?
CREATE TABLE MyValues (MyValue Float)
INSERT INTO MyValues (MyValue) SELECT 2
INSERT INTO MyValues (MyValue) SELECT 3
INSERT INTO MyValues (MyValue) SELECT 4
INSERT INTO MyValues (MyValue) SELECT 5
INSERT INTO MyValues (MyValue) SELECT 6
SELECT VAR(MyValue) FROM MyValues AS MyVariation
View 4 Replies
View Related
Jul 25, 2013
When I test the CAST function on AdventureWorks2012, the CAST function will round down or up, i.e. 2024.994 to 2025 or 5.1865 to 5.
However, when I try the same the CAST function as:
SELECTCAST(2024.994 AS INT) the result I get is now 2024 (and not 2025).
Why is CAST rounding up when I use it on a table in the AdventureWorks2012 database, but when I use CAST on its own, it rounds down?
View 5 Replies
View Related
Feb 14, 2005
Hi,
We are running SQL Server 2000 SP3 + security patches on Windows 2000 Advance Server.
After we did the security updates released before Jan 8th,
We are getting the below error 2 times within a month and the sql server is not responding after this error. After rebooting the server, it responds and works normally.
please give me a solution for this
2005-02-13 07:16:22.54 server Error: 17883, Severity: 1, State: 0
2005-02-13 07:16:22.54 server Process 89:0 (12a68) UMS Context 0x129E1EE8 appears to be non-yielding on Scheduler 0.
2005-02-13 07:16:26.27 server Stack Signature for the dump is 0x00000000
2005-02-13 07:16:26.27 server Error: 17883, Severity: 1, State: 0
2005-02-13 07:16:26.27 server Process 86:0 (1418c) UMS Context 0x12A36D98 appears to be non-yielding on Scheduler 5.
View 2 Replies
View Related
Jan 13, 2012
I'm working on an income deferral problem and we are using IRR to end up calculating periodic income.
As an example, I have the following inputs:
Number of periods = 9
Initial Loan = 21.46
Instalment amount =15.30
Future value=0
Guess=0.1 (10%)
Using a compiled function from Visual Studio in SQL Server 2008 we get a result of 70.71656373
Using a Microsoft Access function we get NaN
Using Microsoft Excel we get NaN
Using a web calculator we get 70 (ish, the calculator rounds the numbers)
I know that the numbers are odd, some of our data is dirty so we do expect that. I was expecting the SQL process to kick out an error (therefore converting the result into 0), but it doesn't.
View 1 Replies
View Related
Nov 19, 2014
We installed the Sql 2014 in Test server.
We observed frequently
"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."
Is it better to run the profiler or performan counter?
What are the filters we have to select in the profiler to monitor the Sql server
View 0 Replies
View Related
Sep 15, 2015
I've applied SP3 to a 2008 R2 instance in order to solve non yielding scheduler errors. Â However, I just had another one after applying the SP. Â
Select @@Version:
10.50.6000.34.
The minidump generated shows this stack trace, which unfortunately, did not point me to a specific bug.
0:039> ~~[0xe30]s
sqlservr!CSqlHandleList::FResize+0x11a:
00000000`022c2b26 498b4108     mov   rax,qword ptr [r9+8] ds:00000000`a4c26088=????????????????
0:039> k
[code]....
View 5 Replies
View Related
Nov 9, 2007
Hello! My group uses scalar-valued functions to denote the FTP drives on our SQL Server 2005 database servers. This allows many (over 100) import processes to refer to that function to find the flat files they need, and if the FTP drive is changed, then only the function needs to be updated, not 100 SP's.
The function is defined this way. Currently, F: is our FTP drive:
CREATE FUNCTION [dbo].[fnFTPPath] ()
RETURNS varchar(200) AS
BEGIN
return 'f:'
END
Now the question: Can a Flat File Connection Manager be used in an SSIS ETL package that refers to the output of this function, plus the FTP folder name, so we can continue updating the FTP drive name in just one place? If so, how is this done?
Thank you!
View 1 Replies
View Related
Oct 23, 2006
hi guys,
i am encountering a pretty weird problem on our SQL database stored procs, particularly those concerning datetime functions. we have two setups, one is on london, and another one is here in singapore. both servers have the same regional settings, the same database tables, stored procs and functions, almost the same in every aspect. the only difference we know is that one is a direct database cut (sql 7.0), while the other used database migration in win2k.
here goes: all stored proc functions work just fine on the singapore setup. the london setup however, is not working at all! it does all the functions like change flags and status, but it doesn't update dates nor search for data by date. i have also checked on the collision names, and they are the same. where else could i start searching for discrepancies between the two?
hope to hear something from you guys, i'm getting desperate. thanks in advance!
View 5 Replies
View Related
May 27, 2015
I am having a problem with the GETDATE().
WHERE TableName.ColumnNamne = Getdate()
The above SQL function does not return any results whereas the below SQL code returns results. Am I doing anything wrong?
WHERE SalesOrder.New_ActualShipmentDate >= GETDATE()
View 35 Replies
View Related
Aug 3, 2006
We are using SQL Server 2005 (not on SP1), and keep getting the following message in the SQL Server log:
IO Completion Listener (0x15e8) Worker 0x00B7E0E8 appears to be non-yielding on Node 0. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 35092.
After receiving this message, the server slows down considerably and we receive numerous messages saying:
SQL Server has encountered 61 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [xxxxx.MDF] in database [xxxx] (22). The OS file handle is 0x00000CDC. The offset of the latest long I/O is: 0x000000fa21a000
I have not been able to find any documentation or explanation of what the IO Completion Listener message is about or how to correct whatever problem is causing it.
Any help would be greatly appreciated!
View 8 Replies
View Related
Nov 1, 2014
I have rather simple CLR function:
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString GetUserName()
{
return (SqlString)WindowsIdentity.GetCurrent().Name;
}
When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?
View 1 Replies
View Related
Jun 10, 2006
Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir
View 4 Replies
View Related
May 15, 2007
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
View 3 Replies
View Related
Jul 8, 2015
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])Â returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])
View 4 Replies
View Related
Feb 25, 2008
Hi
I have a UDF
---------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UDF_AlphaNumeric]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UDF_AlphaNumeric]
GO
CREATE function UDF_AlphaNumeric (@string nvarchar(max)) returns nvarchar(max)
AS
-- select dbo.UDF_AlphaNumeric('a[]#b`c,;"1$%^2"£!3')
begin
while @@rowcount > 0
select @string = replace(@string, substring(@string, patindex('%[^0-9a-zA-Z]%', @string), 1), '')
where patindex('%[^0-9a-zA-Z]%', @string) <> 0
if @string = ''
select @string = null
return @string
end
---------------------
After creating the function I open a new query window, put in the below two calls to the function, and run them
select dbo.ADE_AlphaNumeric('a432[]#b`c,;gfd23$%^789')
select dbo.ADE_AlphaNumeric('a432[]#b`c,;gfd23$%^789')
The results then look like this:
a432[]#b`c,;gfd23$%^789
a432bcgfd23789
The first time it runs, it's not having the desired effect. Any subsequent calls to the function perform as expected.
I am using SQL Server 2005 SP2. Is this a known issue? Or is there some setting I am missing?
Cheers
Neil
View 7 Replies
View Related
May 19, 2008
Hi all,
On our production server once in a while i found the following dump:
---> SQLDump0005.log
Using 'dbghelp.dll' version '4.0.5'
2008-05-14 06:33:28.65 Server **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000
2008-05-14 06:33:28.67 Server ***Stack Dump being sent to E:Microsoft SQL ServerMSSQL.1MSSQLLOGSQLDump0005.txt
2008-05-14 06:33:28.67 Server * *******************************************************************************
2008-05-14 06:33:28.67 Server *
2008-05-14 06:33:28.67 Server * BEGIN STACK DUMP:
2008-05-14 06:33:28.67 Server * 05/14/08 06:33:28 spid 0
2008-05-14 06:33:28.67 Server *
2008-05-14 06:33:28.67 Server * Non-yielding Resource Monitor
2008-05-14 06:33:28.67 Server *
2008-05-14 06:33:28.67 Server * *******************************************************************************
2008-05-14 06:33:28.67 Server * -------------------------------------------------------------------------------
2008-05-14 06:33:28.67 Server * Short Stack Dump
2008-05-14 06:33:28.88 Server Stack Signature for the dump is 0x00000000000003F3
---> sqldumper_errorlog.log
05/14/08 06:33:30, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 4784
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x00000000031421E8
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x0000000000000000
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x0000000000000000
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Dump is associated with file E:Microsoft SQL ServerMSSQL.1MSSQLLOGSQLDump0005.txt
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
05/14/08 06:33:30, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
05/14/08 06:33:32, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
05/14/08 06:33:32, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: E:Microsoft SQL ServerMSSQL.1MSSQLLOGSQLDump0005.mdmp
05/14/08 06:33:32, ACTION, sqlservr.exe, Watson Invoke: No
Looks like there is some missing user privilege in group policy for sql server service account (not local admin) ??
Does any1 can shine some light.
Help is highly appreciated.
TIA
acki4711
View 5 Replies
View Related
Feb 12, 2007
I created a HelloWorld like example using InforPath 2007 and endpoint with SQL Server 2005. Very simple example.
I created a stored procedure that took in one parameter, a datetime field. This parameter is then inserted into a db table that has two columns, an id column and a datetime column.
I took the store procedure just discribed to create an endpoint. Cool. The WSDL is showing, I'm feeling good.
I go to InfoPath and make it so that submit a datetime to the webmethod associated with the endpoint. The data table shows one more row has been added and it is the datetime I sent from InfoPath. YEA!! I go to the Event View and see that I have an error message. The message is HTTP authenticaiton failed. [CLIENT: 10.8.2.26]. Bummer!!
This does not make sense to me. I am able to update the targeted database table but I get an exception. I do not like exceptions nor warnings without an explanation. Does anyone have an idea why this is happening?
The endpoint code looks like: <code snippet>
CREATE ENDPOINT [GeraldTesting_endpoint]
AUTHORIZATION
STATE=STARTED
AS HTTP (PATH=N'/testEndPoint',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'*',
CLEAR_PORT = 8080,
COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'testEndPoint'
( NAME=N'[geraldstest].[dbo].[Gerald_Test_Proc]'
, SCHEMA=STANDARD,
FORMAT=ALL_RESULTS),
BATCHES=ENABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60,
DATABASE=N'geraldstest', NAMESPACE=N'http://tempuri.org', SCHEMA=STANDARD, CHARACTER_SET=XML)
</code snippet>
I had to give up for the project I am currently working because of time constraints. But in the future, for out-of-the-box functionality for proof-of-concept/prototyping, I would like to use endpoints.
View 3 Replies
View Related
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Jan 28, 2015
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
[Code] ....
What it does is convert numbers to times
E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, e.g. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
There are 5 codes: 0 1 2 3 and 4
View 9 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Jun 18, 2008
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
View 2 Replies
View Related
Oct 19, 2007
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
View 3 Replies
View Related
Jan 30, 2008
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies
View Related
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
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
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
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