DECLARE @table Customer (Date DATE, CustomerID VARCHAR(5))
INSERT INTO @Customer (Date, CustomerID) VALUES
('2/6/2015','ADXPA'),
('2/6/2015','AKXLV'),
('2/7/2015','CPDYA'),
('2/7/2015','ZXDQW'),
('2/8/2015','LK1MP'),
etc, till 3836 Records for '2/8/2015' and 4869 Records for '2/8/2015' etc...
Now, I want to take the records which are meeting the standard deviation value of count of CustomerIDs in the group.
Date Count of Customers Avg. Count STD DEV
2/6/2015 2 3944.07 1849.61
2/7/2015 2 3944.07 1849.61
2/8/2015 3836 3944.07 1849.61
2/9/2015 4869 3944.07 1849.61
[Code] ....
Here I want to filter out +/- STDDEV of Average - So I need to filter out rows with dates 2/6/2015, 2/7/2015 and 2/16/2015 since these values not in the range of +/- STDDEV of Average.
Here CustomerID is Varchar type, So we need to convert them to Count first partitioning the Date Column and take the average and STDDEV of all without any partition...
I have a report that calculates mean, min, max, stddev of somer exercises for a class (pushups, situps, trunk lifts, etc). I also have to calculate those for the 1-mile run time (ex: data -- 7:56, 6:35, 9:45 ( in minuteseconds). Obviously the standard Avg(), Min(), Max(), StdDev() functions won't work for time. So I put in some custom code to convert the time to seconds so I can use the standard functions on the seconds and also code to convert that answer back to minuteseconds. Max() works, but for example when I try to calculate the min(), it includes nulls from the dataset as zeros (not every student has to do the mile run). So the min is always 0. How can I exclude nulls from being calculated. The min() function excludes nulls so it returns the correct min() on integer data. What else can I do?
My question here is whether or not MS SQL is processing joins according to the ANSI SQL standard. The situation is this, a friend of mine has the following query (The definition for the tables and the data in them are at the end of the message):
SELECT t.*, v.* FROM Table3 T LEFT OUTER JOIN TableView V ON T.tx = V.tx WHERE V.tx IS NULL and V.part IN (1,2)
My friend is implying that the OUTER join takes precedence over the WHERE clause and should return ALL rows from table3 that don't exist in the view. The view, as you will see below, is a partitioned view. My contention is that the proper join processing is to join the tables first and then apply the WHERE clause to that result set (regardless of the join type). In my friend's case, he expects this query to return four rows. My expectation is that it will return zero rows, because in the result set after the join is performed there are no rows that have a NULL in the v.tx column AND 1 or 2 in the v.part column. The actual ouput agrees with my premise and my friend is sure that this is not proper ANSI SQL join processing. Is this proper ANSI join processing or is MS SQL deviating from the standard?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO
CREATE TABLE [dbo].[Table1] ( [Part] [tinyint] NOT NULL , [id] [int] NOT NULL , [tx] [INT] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Part], [id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [CK_Table1] CHECK ([Part] = 1) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table2] GO
CREATE TABLE [dbo].[Table2] ( [Part] [tinyint] NOT NULL , [id] [int] NOT NULL , [tx] [INT] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( [Part], [id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [CK_Table2] CHECK ([Part] = 2) GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table3] GO
CREATE TABLE [dbo].[Table3] ( [tx] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table3] WITH CHECK ADD CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED ( [tx] ) ON [PRIMARY] GO --DROP VIEW TableView CREATE VIEW TableView AS SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 GO
Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.
How would I convert this query to ANSI 92 standard?
select p.InternalID, n.LastName from tblPatient p, tblName n where p.PatientID = n.EntID and n.EffDt = ( select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate() )
We are using SQL Server 2005 Standard Edition. I have SSIS package which uses 'Data Mining Query Task'. I am using this task to run the predictions using the mining model I have deployed. When I run this SSIS package from IDE , everything is fine. But I deployed this SSIS to msdb, and when I right click on the package and run it from MSDB I get error message saying ,
'Cannot run this task in this edition of Integration Services. It requires higher level edition'.
Altimately we want to execute this SSIS from an API which is written in C#. Can you please suggest any workaround for doing so?
How do SQL 2000 service packs play a role in upgrading? That is, can SQL 2000 Standard with no Service Packs(SP) be upgraded to SQL 2005 Standard, or does SQL 2000 Standard have to have a certain service pack??
I am basically a sybase guy but unfortunately I need to patch a SQL server with SP3. This is the first time I will be doing that. So I am not sure that after applying SP3, if I would need to rebuild all databases again.
Could anybody tell me what and where is the impact when we install SP3..??
In my package i have column called "optinout" coming from source file which has the value "start" and "stop", now in my destination table i have a column called "contact permission code" to which i need to apply a business rule based on the value in column "optinout" the rule is as stated "Contact Permission Code" is OptOut if "OptInOut" value is "STOP" else OptIn.
Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages. Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String) Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType) Dim Connection As New SqlConnection(ConnectionString) Dim Command As New SqlCommand(QueryString, Connection) Command.Connection.Open() Return Command.ExecuteReader()End Function It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader). Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!
When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
hi i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting.. chakri
I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Incorrect syntax near '.'.
Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476
SELECT dv.product , dv.itemname , dv.u_vlgx_plc, dv.shorted , dv.onhand , dv.po_num , t10.docduedate FROM (SELECT t3.product , t7.itemname , t2.u_vlgx_plc, t3.shorted , t4.onhand , t6.cardname AS t6_cardname, MIN( CASE WHEN t8.linestatus = 'O' THEN t9.docnum ELSE NULL END) po_num FROM (SELECT t0.product product , SUM( CASE WHEN t0.qty_topick <> t0.qty_picked THEN t0.qty_topick - t0.qty_picked ELSE 0 END) shorted FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date GROUP BY t0.product ) t3 INNER JOIN comparison.dbo.vlgxplc t2 ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS LEFT JOIN (SELECT t0.product AS product, SUM(t0.quantity) AS onhand FROM rbeacon.dbo.binlocat t0 GROUP BY t0.product ) t4 ON t3.product = t4.product INNER JOIN wbau.dbo.oitm t5 ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.ocrd t6 ON t5.cardcode = t6.cardcode INNER JOIN wbau.dbo.oitm t7 ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.por1 t8 ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS LEFT JOIN wbau.dbo.opor t9 ON t8.docentry = t9.docentry WHERE t3.shorted <> 0 GROUP BY t3.product , t7.itemname , t2.u_vlgx_plc, t3.shorted , t4.onhand , t6.cardname ) dv
OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10
GROUP BY dv.product , dv.itemname , dv.u_vlgx_plc , dv.shorted , dv.onhand , t10.docduedate, dv.po_num , dv.t6_cardname ORDER BY dv.u_vlgx_plc, dv.t6_cardname, dv.product
I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.
Hi guys, ive search high and low for info regarding the "-UseInprocLoader" (in-process BULK INSERT command ) though to no avail. Their are only brief overviews on this property.
does anyone know or have detailed info on the "-UseInprocLoader" Property
I am trying to install the eval version of Reporting Services. One of the requirements for installing this is to have SQL Server 2000 with service pack 3a. I've downloaded the service pack and followed the instructions documented by Microsoft (applying SQL2KSP3.exe, then SQL2KASP3.exe).
It appears that the application of the service packs did nothing. No errors where returned after the executable was run. But, after having applied these service packs, the version has not changed. I have restarted the service, and the server with no success. The version that is returned is 'Microsoft SQL Server 2000 - 8.00.194'.
I am running MSSQL server Developer Edition, running on Windows 2000 Professional (service pack 4).
Any suggestions. I have even uninstalled the SQL server and re-applied the service packs, so I really am having trouble trying to figure out what to do next.
When I try to run SQL Server 6.5 Service Pack 5a, I consistently receive the error message
"Setup initialization could not be successfully completed. cfgchar.exe could not be executed. Please check the relevant out file."
I am currently at Service Pack 3.
1) I have insured that the PATH environment variable does not contain quotes. 2) I have no other applications running at the same time as the service pack is running.
The contents of the cfgchar.out file appear as follows:
Are there any guidelines or best practices for deploying or testing Service Packs? What types of tests should be performed prior to upgrading or do I just trust Microsoft?
I need to modify an already existing stored procedure, which takes three parameters. Currently it uses exclusively "or" logic to apply the parameters to a select statement. e.g.
and (adid = @adID or (editorID = @editorID and aa.editorTypeID = 1))
What I need it to do is to apply a different "and/or" logic depending on what is supplied. If two or three parameters are supplied then it needs to use "and" logic to join them. Howver, if just one parameter is supplied then it should search on that parameter alone - which it won't do if I just join them all together using "and" by default.
The work has to be done inside the T-SQL, not in the calling code. I only know two ways of doing this. Firstly to create different stored procedures and use the calling code to pick one or the other depending on supplied parameters. The other is to do if/or login inside the SP itself on the values of the parameters. Either way presents something of a maintenance nightmare. Is there any other way to do this?
Hello, I have setup web sync from wm 5.0 and it seems to crash when applying the snapshot. Looking at the column definitions it seems fine. Am I missing something? Thanks in advance.
John
The identity column must be either an integer or big integer data type and cannot be NULL. HRESULT 0x80004005 (25551)
I have never applied a service pack to sql server 2000. Someone tells me that you have an option of supplying the SA password but do not have to and the service pack is applied just as if you did supply the SA password. This sounds odd to me. So, is it true? I am asking because our server instance shows "SP4" but a fix that was supposed to be included in SP4 was apparently not as the problem persists (link from sql server 2005 to 2000 fails when referenced in sql2005). I was thinking that whoever ran the service pack may not have provided the SA password so some of the SP4 was not applied???
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "s-1". The provider supports the interface, but returns a failure code when it is used.
Ok I already have a database layed out with several tables and relationships set up, and i went ahead and built a bunch of pages that I was planning on using. While i was doing this I ran into 2 problems, the first was i wrote some SQL statements to insert data from input boxes into some tables i have in my database, i used a catch exception statement as well as a if/else statement to handle the transfer to diffirent pages like a signup succeded or failed, the problem im having is that the data isnt saving into the database. (I was modeling my code after the Video 8 in the Learning SQL server 2005 Express Edition for Beginners Video Series.... I actually got them to work......sorta....) When i test it no exceptions are thrown and it continues on like nothing went wrong. The other Problem i ran into was i realized i wanted to use the memberships and roles provided by ASP.NET 2.0, and since i already created the pages for the site, i was wondering if there was a way to add them in. This site isnt anything fancy at all, i wanted to try and create a app where users sign up and create an account on the site, but i also wanted to have the flexability to insert and retrieve data from the database with out having to be stuck in the templates. Sorry for the long story im trying to give all the info possible. Any help on this would be great! Thanks!
Our update of KB 934458 to SQL Server 2005 SP2 has failed on a Windows 2003 SP2 server. This is a standard version of SQL Server.
The error reported is:
Error Number : 29506 Error Description : MSP Error: 29506 SQL Server Setup failed to modify security permissions on file e:MSQLMSSQLData for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
I have verified that Administrator has full control permissions for the folder indicated in the error message. The SQL Server Service account also has full control permissions on the folder.
I have run the update as administrator without any access restriction.
I have some C# code written a little while that imports data nightly into a database. Most of the data is typically deleted from the tables and imported again. There are some tables where data is never deleted and references other tables. Because there are constraints between these tables, the code processes the import in the following sequence.
get constraints from database->drop constraints->delete data->apply constaints->import data
I was curious how I can accomplish similiar results in SSIS. I know I can execute a process task to manage the constraints, but I was wondering if there is any other way.
I'm retrieving data from a cube using a datareader, all the measures have format string, but when retrieve from Integration services it's lost.
I know there is a way to get the format string as columns using extended properties of the conection. I haven't tried this yet, but is there any way of reapplying this format to the columns using this format strings??
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
