select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
Anyone seen wrong values returned from a CLR update trigger when using the columncount property?
I have a 6-column table that I was experimenting on, and the isupdatedcolumn property was not returning true on the one column I was updating (I verfied the trigger was firing). So, I returned the columncount property in a pipe.send, and was surprised to see the value of 11. When I looped through each column's isupdatedcolumn property, the only column that returned true was column 9.
Then I remembered this table used to have more columns, so I conducted the following experiment:
Added a new column to the table (bringing the total number of columns to 7). Executed an update the caused the trigger to fire; columncount sent to the pipe returned 12. Deleted the column. Executed another update that caused the trigger to fire; columncount returned 12. Added another column to the table. Executed an update the caused the trigger to fire; columncount sent to the pipe returned 13. Deleted the column. Executed another update that caused the trigger to fire; columncount returned 13.
See the pattern? I'm fairly perplexed. Anyone seen this or something similar? This table is in a database that was migrated from SQL 2000 to 2005 and is 9.0 compatibility mode.
Hi All! Could You comment the next situation:I'm configuring my TableAdapter just like Scott Mitchell does in his tutorialhttp://www.asp.net/learn/data-access/tutorial-70-vb.aspxThe only principal difference is that I need Insert/update and deletemethods to be generated (His aim is only SELECT).I'm also using analytic function (ROW_NUMBER) and I'm also gettinwarning “The OVER SQL construct or statement is not supported.� Yousay then that it could be ignored. But, in this case statements tomodify data (insert/update and delete) aren't being generated, thoughafter warning SQL command is executed without errors. So, the question is obvious - why does this warning occur and how mustI perform configuration of TableAdapter based on SQL query withanalytic function?
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int ); insert into @t ( ID ) values (-7077); select checksum_agg( ID ) from @t; ----------- -7077 declare @t table ( ID int ); insert into @t ( ID ) values (-7077), (-8112); select checksum_agg( ID ) from @t; ----------- 1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int ); insert into @t ( ID ) values (-7077), (-7077); select checksum_agg( ID ) from @t; ----------- 0
It's not specific to -7077, for example:
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- 0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- -997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- 0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (8112), (-997777), (8112); select checksum_agg( ID ) from @t; ----------- 0
Create report with SharePoint list. Columns are displaying on the report I used out of the group SUM function and it is group by report. one group total is showing correct total and other groups are showing wrong total and showing too much big value even there is whole column is empty.where value is not in column I am using "-" with IIF function. these columns are calculating by using date difference function between two date columns. these values coming with minus value . so ABS function is also using.
Columns calculation expression:
=IIF(ISNOTHING(Fields!DateCAPackage.Value) OR (ISNOTHING(Fields!Date_CA_Application.Value)) , "-" , CINT(Abs(DateDiff("d",Fields!Date_CA_Application.Value,Fields!DateCAPackage.Value))))
Hi, How to write a SQL function that returns a string that contains xml string from "SELECT ....FOR XML"In other word, I want to put result of select .. for xml into a variable.Thanks
Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???
I have a situation where after creating roles and addding users to the roles then when i call Is_Member('MyRole') on my development box it tells me the user is a member of the Role, But when i detach the database and deploy on a production server and call Is_Member('MyRole') it tells me that the user is not a member of the Role....
I went on to call the system stored procedures, sp_helprolemember to actualy determine if the user is in the 'MyRole' role and the procedure returned a list which i confirmed that the user actually exists in that role..
So am pretty confused when i call Is_Member('MyRole') and it gives me 0 meaning the user is not in the role....
I would like to create a function that will return to me the width of the column of the table without giving as a parameter the table name. I need it, as I want to exceed my data in this column to the column length. I want to fill the column data with trailing blanks until I reach the column width. The function len returns the length of the data in the column.
I know that If I have the table name I can find the column width through the system tables. I don€™t want this.
I am hoping someone could help me understand why this is happening and perhaps a solution. I am using ASP.NET 2.0 with a SQL 2005 database. In code behind, I am performing a query using a parameter as below: sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)" objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text)) The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query. The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way: sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text) I also get NO records retuned using the same criteria. What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning? Thanks!
Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.
CREATE FUNCTION [dbo].[getNextProjectID] () RETURNS varchar(10) AS BEGIN '''''''''''''''''''........................... DECLARE @vNextProjectID varchar(10) RETURN @vNextProjectID END
Sub LoadNextProjectNumber() Dim vProjectID As String Dim cmd As New SqlClient.SqlCommand() cmd.Connection = sqlConn cmd.CommandText = "getNextProjectID"
I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task
create a function called CleanString (ByVal CustomerName as String) As String
CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd
ie parse 'Mr John Tools' and the function returns 'Tools'
ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'
ie parse 'Microsoft Corp' return 'Microsoft Corp'
ie parse 'Digidesign Ltd' return 'Digidesign Ltd'
Any idea of a regular expression or existing piece of existing code I can have
Hi, Assume there is no sale with over 2000 units, the first query returns no row while the latter returns 1 row with a null value. I am not questioning the result, I would like to understand conceptually what semantic difference between the Sql99 analytic function and regular aggregate function makes the result different in this case.
select sum(Quantity) over() from orderdetails where Quantity > 2000
select sum(Quantity) from orderdetails where Quantity > 2000
STDEV() gives incorrect values with reasonable input.
I have a table filled with GPS readings. I've got a column LATITUDE (FLOAT) with about 20,000 records between 35.6369018 and 35.639890. (Same value to the first 5 digits of precision---what can i say, it's a good gps.)
Here's what happens when I ask SQL Server ("9.00.1399.06 (IntelX86)") to compute the standard deviation of the latitude:
// Transact-SQL StdDev function:
SELECT STDEV(LATITUDE) FROM GPSHISTORY WHERE STATTIME BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
0
// Zero. ZERO??!?!! //Let's re-implement Std Dev from the definition using other aggregate functions:
DECLARE @AVERAGE FLOAT; SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000; SELECT SQRT(SUM(SQUARE((LATITUDE - @AVERAGE)))/COUNT(LATITUDE)) FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005392E-06
// That's better. Maybe STDEV is using fixed point arithmetic?!?
SELECT STDEV(10 * LATITUDE)/10 FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
4.77267753808509E-06
SELECT STDEV(100 * LATITUDE)/100 FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
1.66904329068838E-05
SELECT STDEV(1000 * LATITUDE)/1000 FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
8.11904280806654E-06
// The standard deviation should, of course, be linear, e.g.
DECLARE @AVERAGE FLOAT; SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000; SELECT SQRT(SUM(SQUARE(100*(LATITUDE - @AVERAGE)))/COUNT(LATITUDE))/100 FROM GPSHISTORY WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005389E-06
// Std Dev is a numerically stable computation, although it does require traversing the dataset twice. // // This calculation is not being done correctly. // // Incidently, SQRT(VAR(Latitude....)) gives 4.80354E-4, which is also way off.
I will redefine STDEV to use a stored procedure similar to the above, but the algorithm used to compute VAR, STDEV etc should be reviewed and fixed.
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select F.LicenseeID, IsSpecialLicensee from FactTable F left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee] as Select LicenseeID, LicenseeName, IsSpecialLicensee = 1 from DimensionLicensee where LicenseeName like '%ibm%' or LicenseeName like '%cisco%' or LicenseeName like '%hp%'
In my test environment, I get the query result I expected: LicenseeID, IsSpecialLicensee 1 , 1 - (where LicenseeName = 'IBM') 2, null - (where LicenseeName = 'Juniper') 3, 1 - (where LicenseeName = 'Cisco') 4, null - (where LicenseeName = 'Microsoft') 5, null - (where LicenseeName = 'Oracle') 6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result: 1 , 1 - (where LicenseeName = 'IBM') 2, 1 - (where LicenseeName = 'Juniper') 3, 1 - (where LicenseeName = 'Cisco') 4, 1 - (where LicenseeName = 'Microsoft') 5, 1 - (where LicenseeName = 'Oracle') 6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
We run std 2008 r2. I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested.Â
But when he selects * he gets a single row.
I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it. Â
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date) Â Â Â Â Â Â RETURNS date AS BEGIN; Â DECLARE @Result date; Â SET @Result =Â convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101) Â Â Â RETURN @Result; END;
I want to write a statement something like this SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/) But i want the search to ignore the first 2 digits so that it will return e.g 10/11/1234, 09/11/1234 so that it's only matching the last part Any Help Would be greatly appreciated Thanks
In SQL Server for a field of datatype float(8), if i insert a value 2.62 , it saves it as 2.6200000000000001 like so for other values also. But in frontend i can see the right values.
I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)
Code: SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY FROM CUSTOMER C INNER JOIN USERS U ON C.ID = U.ID INNER JOIN ORDERS O ON C.ID = O.ID INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID
hi, i'm using the following code to generate the value of a column in a database, where the colums value is dependent upon the multiplication of two other cells in the same row. When I execute the statement, it will work fine for whole numbers, but it does not work correctly for decimal values. All columns are of type varchar(50). An exmple.................It just multiplied 200 by 2.50 and returned 600SqlCommand objCmd2 = new SqlCommand("UPDATE Portfolio SET Current_Worth=Current_Price*Number_Of_Shares WHERE Name_Of_Asset LIKE '%'+@Name_Of_Asset+'%'", objConn);objCmd2.Parameters.AddWithValue("@Name_Of_Asset", DropDownList1.SelectedValue);objConn.Open();objCmd2.ExecuteNonQuery();objConn.Close(); Any ideas?
(From an exchange originally posted on SQLServer.com, which wasn't resolved...)
To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor: Call to table returning UDF, using local variables:
declare @orgauth varchar(50) set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth') declare @since datetime set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP) select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT * from deptAwardsFn ( dbo.MyParam('DeptAwards', 'OrgAuth') ,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP) ) [20625 CPU, 1709010 Reads, 20632 Duration] (My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?
----- (and later, from me)--- (I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)
