Search Results

Search found 63598 results on 2544 pages for 'sql add on'.

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 331/2544 | < Previous Page | 327 328 329 330 331 332 333 334 335 336 337 338  | Next Page >

  • Summing the results of Case queries in SQL

    - by David Stelfox
    I think this is a relatively straightforward question but I have spent the afternoon looking for an answer and cannot yet find it. So... I have a view with a country column and a number column. I want to make any number less than 10 'other' and then sum the 'other's into one value. For example, AR 10 AT 7 AU 11 BB 2 BE 23 BY 1 CL 2 I used CASE as follows: select country = case when number < 10 then 'Other' else country end, number from ... This replaces the countries values with less than 10 in the number column to other but I can't work out how to sum them. I want to end up with a table/view which looks like this: AR 10 AU 11 BE 23 Other 12 Any help is greatly appreciated. Cheers, David

    Read the article

  • Clustered index dilemma - ID or sort?

    - by richardtallent
    I have a table with two very important fields: id INT identity(1,1) PRIMARY KEY identifiersortcode VARCHAR(900) My app always sorts and pages search results in the UI based on identifiersortcode, but all table joins (and they are legion) are on the id field. (Aside: yes, the sort code really is that long. There's a strong BL reason.) Also, due to O/RM use, most SELECT statements are going to pull almost every column. Currently, the clustered index is on id, but I'm wondering if the TOP / ORDER BY portion of most queries would make identifiersortcode a more attractive option as the clustered key, even considering all of the table joins going on. Inserts on the table and changes to the identifiersortcode are limited enough that changing my clustered index would be a problem for insert/update operations. Trying to make the sort code's non-clustered index a covering index (using INCLUDE) is not a good option. There are a number of large columns, and some of them have a lot of update activity.

    Read the article

  • Comparing textbox.text value to value in SQL Server

    - by Anicho
    Okay so I am trying to compare a login textbox password and username with a custom validator using linq to get information from the database it always returns false though on the validator could someone please tell me where my code below is going wrong. This will be very much appreciated... thank you in advanced... protected void LoginValidate(object source, ServerValidateEventArgs args) { TiamoDataContext context = new TiamoDataContext(); var UsernameCheck = from User in context.Users where User.Username == TextBoxLoginUsername.Text && User.Password == TextBoxLogInPassword.Text select User.Username; var PasswordCheck = from User in context.Users where User.Username == TextBoxLoginUsername.Text && User.Password == TextBoxLogInPassword.Text select User.Password; String test1 = PasswordCheck.ToString(); String test2 = UsernameCheck.ToString(); if (test1 == TextBoxLogInPassword.Text && test2 == TextBoxLoginUsername.Text) { args.IsValid = true; Session["Username"] = TextBoxLoginUsername; Response.Redirect("UserProfile.aspx"); } else { args.IsValid = false; } } I dont know where I am going wrong I know its most probably some sort of silly mistake and me being inexperienced at this...

    Read the article

  • Restore Partioned database into multiple filegroups

    - by Renju
    does anyone have any query to restore partioned db that having multiple file groups,In the restore option in the SSME i need to edit manually all the path of the filegroups restore as option it little bit tedious as it having more than 150 filegroups eg:USE master GO -- First determine the number and names of the files in the backup. RESTORE FILELISTONLY FROM MyNwind_1 -- Restore the files for MyNwind. RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERY, MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf' GO -- Apply the first transaction log backup. RESTORE LOG MyNwind FROM MyNwind_log1 WITH NORECOVERY GO -- Apply the last transaction log backup. RESTORE LOG MyNwind FROM MyNwind_log2 WITH RECOVERY GO Here i need to specify multiple MOVE command for all my filegroups,this is a tedious task when having more than 100s of filegroups MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf' I need to move the files into the path i provided as a parameter.Please help. Regards Renju http://blog.renjucool.com

    Read the article

  • ASP.NET MSSQL Select top N values but skip M results

    - by Mikey
    Im working on an ASP.Net project to display information on a website from a database. I want to select the top 10 items from a news table but skip the first Item and I'm having some problem with it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" SelectCommand="SELECT top 5 [id], [itemdate], [title], [description], [photo] FROM [Announcements] order by itemdate desc"> </asp:SqlDataSource> This is what I have so far but i can't find any info online about how to skip a record

    Read the article

  • Two different definitions of database schema

    - by AspOnMyNet
    a) I found two definitions of schema: FIRST - A set of information that describes a table is known as a schema, and schemas are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any). SECOND - A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. I assume the two descriptions describe entirely different concepts, which just happen to use the same name? b) A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. If I understand the above definition correctly, then database schema is similar to a namespace, only difference being that we can assign access permissions to database schema, while same can’t be done with namespaces? thanx

    Read the article

  • SQL Query to return maximums over decades

    - by Abraham Lincoln
    My question is the following. I have a baseball database, and in that baseball database there is a master table which lists every player that has ever played. There is also a batting table, which tracks every players' batting statistics. I created a view to join those two together; hence the masterplusbatting table. CREATE TABLE `Master` ( `lahmanID` int(9) NOT NULL auto_increment, `playerID` varchar(10) NOT NULL default '', `nameFirst` varchar(50) default NULL, `nameLast` varchar(50) NOT NULL default '', PRIMARY KEY (`lahmanID`), KEY `playerID` (`playerID`), ) ENGINE=MyISAM AUTO_INCREMENT=18968 DEFAULT CHARSET=latin1; CREATE TABLE `Batting` ( `playerID` varchar(9) NOT NULL default '', `yearID` smallint(4) unsigned NOT NULL default '0', `teamID` char(3) NOT NULL default '', `lgID` char(2) NOT NULL default '', `HR` smallint(3) unsigned default NULL, PRIMARY KEY (`playerID`,`yearID`,`stint`), KEY `playerID` (`playerID`), KEY `team` (`teamID`,`yearID`,`lgID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Anyway, my first query involved finding the most home runs hit every year since baseball began, including ties. The query to do that is the following.... select f.yearID, f.nameFirst, f.nameLast, f.HR from ( select yearID, max(HR) as HOMERS from masterplusbatting group by yearID )as x inner join masterplusbatting as f on f.yearID = x.yearId and f.HR = x.HOMERS This worked great. However, I now want to find the highest HR hitter in each decade since baseball began. Here is what I tried. select f.yearID, truncate(f.yearid/10,0) as decade,f.nameFirst, f.nameLast, f.HR from ( select yearID, max(HR) as HOMERS from masterplusbatting group by yearID )as x inner join masterplusbatting as f on f.yearID = x.yearId and f.HR = x.HOMERS group by decade You can see that I truncated the yearID in order to get 187, 188, 189 etc instead of 1897, 1885,. I then grouped by the decade, thinking that it would give me the highest per decade, but it is not returning the correct values. For example, it's giving me Adrian Beltre with 48 HR's in 2004 but everyone knows that Barry Bonds hit 73 HR in 2001. Can anyone give me some pointers?

    Read the article

  • SCD2 + Merge Statement + SQL Server

    - by Nev_Rahd
    I am trying work out with MERGE statment to Insert / Update Dimension Table of Type SCD2 My source is a Table var to Merge with Dimension table. My MERGE statement is throwing an error as: The target table 'DM.DATA_ERROR.ERROR_DIMENSION' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK_ERROR_DIMENSION_to_AUDIT_CreatedBy'. My MERGE Statement: DECLARE @DATAERROROBJECT AS [ERROR_DIMENSION] INSERT INTO DM.DATA_ERROR.ERROR_DIMENSION SELECT ERROR_CODE, DATA_STREAM_ID, [ERROR_SEVERITY], DATA_QUALITY_RATING, ERROR_LONG_DESCRIPTION, ERROR_DESCRIPTION, VALIDATION_RULE, ERROR_TYPE, ERROR_CLASS, VALID_FROM, VALID_TO, CURR_FLAG, CREATED_BY_AUDIT_SK, UPDATED_BY_AUDIT_SK FROM (MERGE DM.DATA_ERROR.ERROR_DIMENSION ED USING @DATAERROROBJECT OBJ ON(ED.ERROR_CODE = OBJ.ERROR_CODE AND ED.DATA_STREAM_ID = OBJ.DATA_STREAM_ID) WHEN NOT MATCHED THEN INSERT VALUES( OBJ.ERROR_CODE ,OBJ.DATA_STREAM_ID ,OBJ.[ERROR_SEVERITY] ,OBJ.DATA_QUALITY_RATING ,OBJ.ERROR_LONG_DESCRIPTION ,OBJ.ERROR_DESCRIPTION ,OBJ.VALIDATION_RULE ,OBJ.ERROR_TYPE ,OBJ.ERROR_CLASS ,GETDATE() ,'9999-12-13' ,'Y' ,1 ,1 ) WHEN MATCHED AND ED.CURR_FLAG = 'Y' AND ( ED.[ERROR_SEVERITY] <> OBJ.[ERROR_SEVERITY] OR ED.[DATA_QUALITY_RATING] <> OBJ.[DATA_QUALITY_RATING] OR ED.[ERROR_LONG_DESCRIPTION] <> OBJ.[ERROR_LONG_DESCRIPTION] OR ED.[ERROR_DESCRIPTION] <> OBJ.[ERROR_DESCRIPTION] OR ED.[VALIDATION_RULE] <> OBJ.[VALIDATION_RULE] OR ED.[ERROR_TYPE] <> OBJ.[ERROR_TYPE] OR ED.[ERROR_CLASS] <> OBJ.[ERROR_CLASS] ) THEN UPDATE SET ED.CURR_FLAG = 'N', ED.VALID_TO = GETDATE() OUTPUT $ACTION ACTION_OUT, OBJ.ERROR_CODE ERROR_CODE, OBJ.DATA_STREAM_ID DATA_STREAM_ID, OBJ.[ERROR_SEVERITY] [ERROR_SEVERITY], OBJ.DATA_QUALITY_RATING DATA_QUALITY_RATING, OBJ.ERROR_LONG_DESCRIPTION ERROR_LONG_DESCRIPTION, OBJ.ERROR_DESCRIPTION ERROR_DESCRIPTION, OBJ.VALIDATION_RULE VALIDATION_RULE, OBJ.ERROR_TYPE ERROR_TYPE, OBJ.ERROR_CLASS ERROR_CLASS, GETDATE() VALID_FROM, '9999-12-31' VALID_TO, 'Y' CURR_FLAG, 555 CREATED_BY_AUDIT_SK, 555 UPDATED_BY_AUDIT_SK ) AS MERGE_OUT WHERE MERGE_OUT.ACTION_OUT = 'UPDATE'; What am I doing wrong ?

    Read the article

  • Duplicate all rows in sql database table

    - by Andrew Welch
    I have a table which contains house details called property. I am creating a localised application, and I have a db table called propertylocalised. In this table is held duplicates of the data and culture column e.g. key culture propertyname 1 en helloproperty 1 fr bonjourproperty At the moment I have all my en culture inserted but I want to duplicate all of those rows and then for every other row insert fr into culture. I obviously only want to do this once, for the purpose of setting up the localisation. Thanks Andy

    Read the article

  • SQL - How to select a row having a column with max value

    - by Abhi
    date value 18/5/2010, 1 pm 40 18/5/2010, 2 pm 20 18/5/2010, 3 pm 60 18/5/2010, 4 pm 30 18/5/2010, 5 pm 60 18/5/2010, 6 pm 25 i need to query for the row having max(value)(i.e. 60). So, here we get two rows. From that, I need the row with the lowest time stamp for that day(i.e 18/5/2010, 3 pm - 60)

    Read the article

  • Counting consecutive items within MS SQL

    - by Greg
    Got a problem with a query I'm trying to write. I have a table that lists people that have been sent an email. There is a bit column named Active which is set to true if they have responded. But I need to count the number of consecutive emails the person has been inactive since either their first email or last active email. For example, this basic table shows one person has been sent 9 emails. They have been active within two of the emails (3 & 5). So their inactive count would be 4 as we are counting from email number 6 onwards. PersonID(int) EmailID(int) Active(bit) 1 1 0 1 2 0 1 3 1 1 4 0 1 5 1 1 6 0 1 7 0 1 8 0 1 9 0 Any pointers or help would be great. Regards Greg

    Read the article

  • Sql query to get the GrandFather name by doing a self join

    - by mahesh
    Hello all , Can any one please give me the query to get the child name and his grand fathers name For eg - if i have a table Relationships in the i have childid and fatherid columns so how will i get the grandfather, i can easily get the father name by using a join but for grandfather i need to do joins 2 times so can any one help me with this D.Mahesh

    Read the article

  • SQL Selects on subsets

    - by Adam
    I need to check if a row exists in a database; however, I am trying to find the way to do this that offers the best performance. This is best summarised with an example. Let's assume I have the following table: dbo.Person( FirstName varchar(50), LastName varchar(50), Company varchar(50) ) Assume this table has millions of rows, however ONLY the column Company has an index. I want to find out if a particular combination of FirstName, LastName and Company exists. I know I can do this: IF EXISTS(select 1 from dbo.Person where FirstName = @FirstName and LastName = @LastName and Company = @Company) Begin .... End However, unless I'm mistaken, that will do a full table scan. What I'd really like it to do is a query where it utilises the index. With the table above, I know that the following query will have great performance, since it uses the index: Select * from dbo.Person where Company = @Company Is there anyway to make the search only on that subset of data? e.g. something like this: select * from ( Select * from dbo.Person where Company = @Company ) where FirstName = @FirstName and LastName = @LastName That way, it would only be doing a table scan on a much narrower collection of data. I know the query above won't work, but is there a query that would? Oh, and I am unable to create temporary tables, as the user will only have read access.

    Read the article

  • Using DateDiff in Entity Framwork on a SQL CE database

    - by deverop
    I have a method which should return a list of anonymous objects with a calculated column like this: var tomorrow = DateTime.Today.AddDays(1); return from t in this.Events where (t.StartTime >= DateTime.Today && t.StartTime < tomorrow && t.EndTime.HasValue) select new { Client = t.Activity.Project.Customer.Name, Project = t.Activity.Project.Name, Task = t.Activity.Task.Name, Rate = t.Activity.Rate.Name, StartTime = t.StartTime, EndTime = t.EndTime.Value, Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("m", t.StartTime, t.EndTime.Value) / 60), Description = t.Activity.Description }; Unfortunately I get the following error from the DateDiff function: The specified method 'System.Nullable1[System.Int32] DateDiff(System.String, System.Nullable1[System.DateTime], System.Nullable`1[System.DateTime])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression. Any ideas what I could have done wrong here? EDIT: I also tried the EntityFunctions class mentioned here, but that did not work as well. Minutes = EntityFunctions.DiffMinutes(t.EndTime, t.StartTime),

    Read the article

  • Why better isolation level means better performance in SQL Server

    - by Oleg Zhylin
    When measuring performance on my query I came up with a dependency between isolation level and elapsed time that was surprising to me READUNCOMMITTED - 409024 READCOMMITTED - 368021 REPEATABLEREAD - 358019 SERIALIZABLE - 348019 Left column is table hint, and the right column is elapsed time in microseconds (sys.dm_exec_query_stats.total_elapsed_time). Why better isolation level gives better performance? This is a development machine and no concurrency whatsoever happens. I would expect READUNCOMMITTED to be the fasted due to less locking overhead. Update: I did measure this with DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE issued and Profiler confirms there're no cache hits happening. Update2: The query in question is an OLAP one and we need to run it as fast as possible. Closing the production server from outside world to get the computation done is not out of question if this gives performance benefits.

    Read the article

  • Merge two rows in SQL

    - by Jason
    Assuming I have a table containing the following information: FK | Field1 | Field2 ===================== 3 | ABC | *NULL* 3 | *NULL* | DEF is there a way I can perform a select on the table to get the following FK | Field1 | Field2 ===================== 3 | ABC | DEF Thanks Edit: Fix field2 name for clarity

    Read the article

  • SQL Server 2008 - Difference between time(0)

    - by lugeno
    I've a table with working_hours time(0), lunch_hours time(0) What I have to do is the following: If lunch_hours is greater that one hour, I have to calculate the offset Example: lounch_hour = 01:30:00 = offset = 00:30:00 Once done I've to subtract the offset from the working_hours value Example: offset = 00:30:00, working_hours = 07:30:00 = working_hours = 07:00:00 The result must be in time(0) format (hh:mm:ss) I've tried several solutions but still not working. Used DATEDIFF probably didn't used in correct way. Thanks for any help Bye!

    Read the article

  • Multiple IN statements for WHERE. Would this return good data?

    - by TheDudeAbides
    SELECT ['VISA CK - 021810$'].[ACCT NBR #1], ['VISA CK - 021810$'].[ALT CUST NM #1], ['VISA CK - 021810$'].[LAST USED] FROM ['VISA CK - 021810$'] WHERE ['VISA CK - 021810$'].[ALT CUST NM #1] IN ( SELECT ['VISA CK - 021810$'].[ALT CUST NM #1] FROM ['VISA CK - 021810$'] GROUP BY ['VISA CK - 021810$'].[ALT CUST NM #1] HAVING COUNT(['VISA CK - 021810$'].[ALT CUST NM #1]) > 1 ) AND ['VISA CK - 021810$'].[ACCT NBR #1] IN ( SELECT ['VISA CK - 021810$'].[ACCT NBR #1] FROM ['VISA CK - 021810$'] GROUP BY ['VISA CK - 021810$'].[ACCT NBR #1] HAVING COUNT(['VISA CK - 021810$'].[ACCT NBR #1]) > 1 )

    Read the article

  • Checking inherited attributes in an 'ancestry' based SQL table

    - by Brendon Muir
    I'm using the ancestry gem to help organise my app's tree structure in the database. It basically writes a childs ancestor information to a special column called 'ancestry'. The ancestry column for a particular child might look like '1/34/87' where the parent of this child is 87, and then 87's parent is 34 and 34's is 1. It seems possible that we could select rows from this table each with a subquery that checks all the ancestors to see if a certain attribute it set. E.g. in my app you can hide an item and its children just by setting the parent element's visibility column to 0. I want to be able to find all the items where none of their ancestors are hidden. I tried converting the slashes to comma's with the REPLACE command but IN required a set of comma separated integers rather than one string with comma separated string numbers. It's funny, because I can do this query in two steps, e.g. retrieve the row, then take its ancestry column, split out the id's and make another query that checks that the id is IN that set of id's and that visibility isn't ever 0 and whala! But joining these into one query seems to be quite a task. Much searching has shown a few answers but none really do what I want. SELECT * FROM t1 WHERE id = 99; 99's ancestry column reads '1/34/87' SELECT * FROM t1 WHERE visibility = 0 AND id IN (1,34,87); kind of backwards, but if this returns no rows then the item is visible. Has anyone come across this before and come up with a solution. I don't really want to go the stored procedure route. It's for a rails app.

    Read the article

  • Help me finding dependency list.

    - by Pearl
    I have two table employee table and employee dependency table. Employee tooks like below. insert into E values(1,'Adam') insert into E values(2,'Bob') insert into E values(3,'Candy') insert into E values(4,'Doug') insert into E values(5,'Earl') insert into E values(6,'Fran') Employee dependency table looks like below insert into Ed values(3,'2') insert into Ed values(3,'5') insert into Ed values(2,'1') insert into Ed values(2,'4') insert into Ed values(5,'6') I need to find the dependency list like below Eid Ename Dname 3 Candy Bob,Fran Please help me finding the above.

    Read the article

  • SQL: many-to-many relationship, IN condition

    - by Maarten
    I have a table called transactions with a many-to-many relationship to items through the items_transactions table. I want to do something like this: SELECT "transactions".* FROM "transactions" INNER JOIN "items_transactions" ON "items_transactions".transaction_id = "transactions".id INNER JOIN "items" ON "items".id = "items_transactions".item_id WHERE (items.id IN (<list of items>)) But this gives me all transactions that have one or more of the items in the list associated with it and I only want it to give me the transactions that are associated with all of those items. Any help would be appreciated.

    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 | 327 328 329 330 331 332 333 334 335 336 337 338  | Next Page >