Search Results

Search found 45340 results on 1814 pages for 'sql date functions'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 389/1814 | < Previous Page | 385 386 387 388 389 390 391 392 393 394 395 396  | Next Page >

  • Help with optimising SQL query

    - by user566013
    Hi i need some help with this problem. I am working web application and for database i am using sqlite. Can someone help me with one query from databse which must be optimized == fast =) I have table x: ID | ID_DISH | ID_INGREDIENT 1 | 1 | 2 2 | 1 | 3 3 | 1 | 8 4 | 1 | 12 5 | 2 | 13 6 | 2 | 5 7 | 2 | 3 8 | 3 | 5 9 | 3 | 8 10| 3 | 2 .... ID_DISH is id of different dishes, ID_INGREDIENT is ingredient which dish is made of: so in my case dish with id 1 is made with ingredients with ids 2,3 In this table a have more then 15000 rows and my question is: i need query which will fetch rows where i can find ids of dishes ordered by count of ingreedients ASC which i haven added to my algoritem. examle: foo(2,4) will rows in this order: ID_DISH | count(stillMissing) 10 | 2 1 | 3 Dish with id 10 has ingredients with id 2 and 4 and hasn't got 2 more, then is

    Read the article

  • Performance of inter-database query (between linked servers)

    - by Swoosh
    I have an import between 2 linked servers. I basically got to get the data from a multiple join into a table on my side. The current query is something like this: select a.* from db1.dbo.tbl1 a inner join db1.dbo.tbl2 on ... inner join db1.dbo.tbl3 on ... inner join db1.dbo.tbl4 on ... inner join db2.dbo.myside on ... db1 = linked server db2 = my own database After this one, I am using an insert into + select to add this data in my table which is located in db2. (usually few hundred records - this import running once a minute) My question is related to performance. The tables on the linked server (tbl1, tbl2, tbl3, tbl4) are huge tables, with millions of records, and it is slowing down the import process. I was told that, if I do the join on the "other" side (db1 - linked server) for example in a stored procedure, than, even if the query looks the same, it would run faster. Is that right? This is kinda hard to test. Note that the join contains a table from my database too. Also. are there other "tricks" I could use in order to make this run faster? Thanks

    Read the article

  • SQL database self interaction entity

    - by Ricardo Costa
    I've been working on a database, wich is referent to an Aeroport management. I'm having a problem that it's freaking me out.. What i'm trying to do is, assuming that a client wants to know the distance between 2 locations, in miles or kms. As an example, if the user wants to know the distance between London and Amsterdam, should that distance be calculated by a formule or should it be already stored on the database? (1,N) ____________ ____________|__ | | | | | City/Airport |<---------| |______________| How can i show to user the distance between his 2 choices? RicardoCosta

    Read the article

  • Query Execution Plan - When is the Where clause executed?

    - by Alex
    I have a query like this (created by LINQ): SELECT [t0].[Id], [t0].[CreationDate], [t0].[CreatorId] FROM [dbo].[DataFTS]('test', 100) AS [t0] WHERE [t0].[CreatorId] = 1 ORDER BY [t0].[RANK] DataFTS is a full-text search table valued function. The query execution plan looks like this: SELECT (0%) - Sort (23%) - Nested Loops (Inner Join) (1%) - Sort (Top N Sort) (25%) - Stream Aggregate (0%) - Stream Aggregate (0%) - Compute Scalar (0%) - Table Valued Function (FullTextMatch) (13%) | | - Clustered Index Seek (38%) Does this mean that the WHERE clause ([CreatorId] = 1) is executed prior to the TVF ( full text search) or after the full text search? Thank you.

    Read the article

  • Pivot to obtain EAV data

    - by Snowy
    I have an EAV table (simple key/value in every row) and I need to take the 'value' from two of the rows and concat them into a single row with a single column. I can't seem to get through the part where I just have the pivot straight. Can anyone help me figure this out? Declare @eavHelp Table ( [Key] VARCHAR (8) NOT NULL, [Value] VARCHAR (8) NULL ) Insert Into @eavHelp Values ( 'key1' , 'aaa' ) Insert Into @eavHelp Values ( 'key2' , 'bbb' ) Select * From @eavHelp Pivot ( Min( [Value] ) For [Value] in ( hmm1 , hmm2 ) ) as Piv Where [Key] = 'key1' or [Key] = 'key2' That makes: Key hmm1 hmm2 -------- -------- -------- key1 NULL NULL key2 NULL NULL But what I want to make is: hmmmX ----- aaa;bbb

    Read the article

  • need an sql query

    - by CKeven
    I currently have two tables: 1. car(plate_number, brand, cid) 2. borrow(StartDate, endDate, brand, id) I want to write a query to get all available brand and count of available cars for each brand

    Read the article

  • using NEWSEQUENTIALID() with UPDATE Trigger

    - by Ram
    I am adding a new GUID/Uniqueidentifier column to my table. ALTER TABLE table_name ADD VersionNumber UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWSEQUENTIALID() GO And when ever a record is updated in the table, I would want to update this column "VersionNumber". So I create a new trigger CREATE TRIGGER [DBO].[TR_TABLE_NAMWE] ON [DBO].[TABLE_NAME] AFTER UPDATE AS BEGIN UPDATE TABLE_NAME SET VERSIONNUMBER=NEWSEQUENTIALID() FROM TABLE_NAME D JOIN INSERTED I ON D.ID=I.ID/* some ID which is used to join*/ END GO But just realized that NEWSEQUENTIALID() can only be used with CREATE TABLE or ALTER TABLE. I got this error The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression. Is there a workaround for this ? Edit1: Changing NEWSEQUENTIALID() to NEWID() in the trigger solves this, but I am indexing this column and using NEWID() would be sub-optimal

    Read the article

  • T-SQL: if exists always return true ?

    - by msfanboy
    Hello, What do you think , does the Stored Procedure always return 1 ? I am concerned about the if exists(..) BEGIN DECLARE @IsUserExisting bit SET NOCOUNT ON IF Exists ( Select null FROM G_User WHERE SamAccountName = @SamAccountName AND NetBIOSDomainName = @NetBIOSDomainName ) BEGIN SET @IsUserExisting = 1 END ELSE BEGIN SET @IsUserExisting = 0 END Select @IsUserExisting END

    Read the article

  • Stored procedure and trigger

    - by noober
    Hello all, I had a task -- to create update trigger, that works on real table data change (not just update with the same values). For that purpose I had created copy table then began to compare updated rows with the old copied ones. When trigger completes, it's neccessary to actualize the copy: UPDATE CopyTable SET id = s.id, -- many, many fields FROM MainTable s WHERE s.id IN (SELECT [id] FROM INSERTED) AND CopyTable.id = s.id; I don't like to have this ugly code in trigger anymore, so I has extracted it to a stored procedure: CREATE PROCEDURE UpdateCopy AS BEGIN UPDATE CopyTable SET id = s.id, -- many, many fields FROM MainTable s WHERE s.id IN (SELECT [id] FROM INSERTED) AND CopyTable.id = s.id; END The result is -- Invalid object name 'INSERTED'. How can I workaround this? Regards,

    Read the article

  • Chaining Many-To-Many Dimensional Relationships in SSAS

    - by Ray Saltrelli
    I'm developing a cube in SSAS and attempting to model the following relationships: Many Facts to 1 Customer Many Customers to Many Sales Reps Many Sales Reps (Subordinates) to Sales Reps (Managers) Each M2M relationship is facilitated by a bridge table which also acts as a fact table in the cube I have most of this working. I can slice Facts by Customer and by Sales Rep (Subordinate), but when I add Sales Rep (Manager) to the query it appears to return every subordinate/manager combination regardless of whether or not that relationship exists in the bridge table. Any ideas as to what I might be doing wrong?

    Read the article

  • Help with Parameterizing SQL Query for C# with possible null values

    - by Jhonny D. Cano -Leftware-
    Hello, people. I need help with parameterizing this query. SELECT * FROM greatTable WHERE field1 = @field1 AND field2 = @field2 The user should be able to search for any of the 2 fields, and the user also should be able to search if the field2 has null values. var query = "theQuery"; var cm = new SqlCommand(cn, query); cm.AddParameter("@field1", "352515"); cm.AddParameter("@field2", DBNull.Value); // my DataTable here is having 0 records var dt = GetTable(cm);

    Read the article

  • SQL query multi table selection

    - by nemiss
    I have 3 tables, - Section table that defines some general item sections. - Category table - has a "section" column (foreign key). - Product table - has a "category" column (foreign key). I want to get all products that belong to X section. How can I do it? select from select?

    Read the article

  • SQL use comma-separated values with IN clause

    - by user342944
    I am developing an ASP.NET application and passing a string value like "1,2,3,4" into a procedure to select those values which are IN (1,2,3,4) but its saying "Conversion failed when converting the varchar value '1,2,3,4' to data type int." Here is the aspx code: private void fillRoles() { /*Read in User Profile Data from database */ Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetStoredProcCommand("sp_getUserRoles"); db.AddInParameter(cmd, "@pGroupIDs", System.Data.DbType.String); db.SetParameterValue(cmd, "@pGroupIDs", "1,2,3,4"); IDataReader reader = db.ExecuteReader(cmd); DropDownListRole.DataTextField = "Group"; DropDownListRole.DataValueField = "ID"; while (reader.Read()) { DropDownListRole.Items.Add((new ListItem(reader[1].ToString(), reader[0].ToString()))); } reader.Close(); } Here is my procedure: CREATE Procedure [dbo].[sp_getUserRoles](@pGroupIDs varchar(50)) AS BEGIN SELECT * FROM CheckList_Groups Where id in (@pGroupIDs) END

    Read the article

  • Trigger on database using a web application and a winform application

    - by Michael
    Hello all, Situation: I have a web application which shows errors and where you can accept those error messages. I also have a service, which checks errors from a system and sets the error messages in the database. When I accept an error in the web application, i would like the service to know which error message has been accepted, so that it can do some other actions. My guess is that this could be done through some sort of trigger, but i can't figure out how. Can anyone help me with this?

    Read the article

  • LINQ to SQL left outer joins

    - by César
    Is this query equivalent to a LEFT OUTER join? var rows = from a in query join s in context.ViewSiteinAdvise on a.Id equals s.SiteInAdviseId where a.Order == s.Order select new {....}; I tried this but it did not result from s in ViewSiteinAdvise join q in query on s.SiteInAdviseId equals q.Id into sa from a in sa.DefaultIfEmpty() where s.Order == a.Order select new {s,a} I need all columns from View

    Read the article

  • How can I improve my select query for storing large versioned data sets?

    - by Jason Francis
    At work, we build large multi-page web applications, consisting mostly of radio and check boxes. The primary purpose of each application is to gather data, but as users return to a page they have previously visited, we report back to them their previous responses. Worst-case scenario, we might have up to 900 distinct variables and around 1.5 million users. For several reasons, it makes sense to use an insert-only approach to storing the data (as opposed to update-in-place) so that we can capture historical data about repeated interactions with variables. The net result is that we might have several responses per user per variable. Our table to collect the responses looks something like this: CREATE TABLE [dbo].[results]( [id] [bigint] IDENTITY(1,1) NOT NULL, [userid] [int] NULL, [variable] [varchar](8) NULL, [value] [tinyint] NULL, [submitted] [smalldatetime] NULL) Where id serves as the primary key. Virtually every request results in a series of insert statements (one per variable submitted), and then we run a select to produce previous responses for the next page (something like this): SELECT t.id, t.variable, t.value FROM results t WITH (NOLOCK) WHERE t.userid = '2111846' AND (t.variable='internat' OR t.variable='veteran' OR t.variable='athlete') AND t.id IN (SELECT MAX(id) AS id FROM results WITH (NOLOCK) WHERE userid = '2111846' AND (t.variable='internat' OR t.variable='veteran' OR t.variable='athlete') GROUP BY variable) Which, in this case, would return the most recent responses for the variables "internat", "veteran", and "athlete" for user 2111846. We have followed the advice of the database tuning tools in indexing the tables, and against our data, this is the best-performing version of the select query that we have been able to come up with. Even so, there seems to be significant performance degradation as the table approaches 1 million records (and we might have about 150x that). We have a fairly-elegant solution in place for sharding the data across multiple tables which has been working quite well, but I am open for any advice about how I might construct a better version of the select query. We use this structure frequently for storing lots of independent data points, and we like the benefits it provides. So the question is, how can I improve the performance of the select query? I assume the nested select statement is a bad idea, but I have yet to find an alternative that performs as well. Thanks in advance. NB: Since we emphasize creating over reading in this case, and since we never update in place, there doesn't seem to be any penalty (and some advantage) for using the NOLOCK directive in this case.

    Read the article

  • SQL Analysis Services - Dimension attributes with a "many" cardinality

    - by MonkeyBrother
    I am creating a cube with the following tables: Customer CustomerID, Name Customer Rep CustomerID, RepID Rep RepID, Name The important thing here is that there is a many to many relationship between Reps and Customers. I want to be able to ask the question "How much sales for customers working with rep 'A'?" In the data source view i set up the relationships between both customerid columns and both repid columns. I set up the rep attribute in the dimension builder and when I try to build the cube I get this error: Errors in the high-level relationship engine. the 'Rep' table that is required for a join cannot be reached based on the relationships in the data source view.

    Read the article

  • SSIS (missing) Pre-Build and Post-Build

    - by Raj More
    For the warehouse work under progress, we have a single solution with multiple projects in it OLTP Database Project Warehouse Database Project SSIS ETL project After the SSIS project is built, I want to move the binaries (XML, really) from the Bin folder to "C:\AutomatedTasks\ETL.Warehouse\" and "C:\AutomatedTasks\ETL" I cannot find the Post-Build events to do that for the SSIS project. Where are they? If they aren't available, how do I achieve this?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 385 386 387 388 389 390 391 392 393 394 395 396  | Next Page >